About

MySQL stores TIMESTAMP columns in UTC internally, converting them on read based on the session time_zone variable. When you extract raw data via dumps, APIs, or direct queries with @@session.time_zone set to +00:00, every value arrives stripped of local context. Displaying these values to end users without conversion produces errors ranging from incorrect appointment times to broken audit trails. This tool parses MySQL DATETIME and TIMESTAMP string formats, interprets them as UTC, and converts to the client's detected local timezone using the browser's Intl.DateTimeFormat API, which correctly handles Daylight Saving Time transitions. It does not approximate offsets with a fixed number.

Batch mode accepts multiple timestamps separated by newlines, processing each independently. The output format is configurable: ISO 8601, locale-aware human-readable, Unix epoch (seconds and milliseconds), and relative time. Note: this tool assumes the input is UTC. If your MySQL server uses a non-UTC time_zone setting, the input is not truly UTC and the conversion will be offset by the difference. Pro Tip: always verify your source timezone with SELECT @@global.time_zone, @@session.time_zone; before trusting raw timestamp exports.

Formulas

The conversion from a UTC timestamp string to local time follows this process. The MySQL UTC string is parsed into a JavaScript Date object representing the absolute moment in time:

T_local = T_UTC + Δ_offset

Where T_UTC is the Unix epoch milliseconds of the input interpreted as UTC, and Δ_offset is the local timezone offset in milliseconds, which varies with DST. The browser computes this internally via the IANA timezone database.

Δ_offset = −1 × Date().getTimezoneOffset() × 60000 ms

For Unix epoch output:

epoch_s = T_UTC1000

Where T_UTC is the Date.getTime() value in milliseconds since 1970-01-01T00:00:00Z. The relative time calculation computes Δt = now − T_UTC and selects the appropriate human-readable unit (seconds, minutes, hours, days, months, years) based on magnitude thresholds.

Reference Data

MySQL Type	Storage	Range	UTC Behavior	Format
DATETIME	8 bytes	1000-01-01 to 9999-12-31	Stores as-is (no conversion)	YYYY-MM-DD HH:MM:SS
TIMESTAMP	4 bytes	1970-01-01 to 2038-01-19	Stored as UTC, converted on read	YYYY-MM-DD HH:MM:SS
DATE	3 bytes	1000-01-01 to 9999-12-31	No time component	YYYY-MM-DD
TIME	3 bytes	−838:59:59 to 838:59:59	Duration, not timestamp	HH:MM:SS
YEAR	1 byte	1901 to 2155	No conversion	YYYY
Common Timezone Offsets from UTC
UTC / GMT	+00:00		London (winter), Reykjavik
CET	+01:00		Berlin, Paris, Rome
EET	+02:00		Helsinki, Athens, Cairo
MSK	+03:00		Moscow, Istanbul
IST	+05:30		India (Mumbai, Delhi)
CST (China)	+08:00		Beijing, Singapore
JST	+09:00		Tokyo, Seoul
AEST	+10:00		Sydney (winter)
EST	−05:00		New York (winter)
CST (US)	−06:00		Chicago (winter)
MST	−07:00		Denver (winter)
PST	−08:00		Los Angeles (winter)
AKST	−09:00		Anchorage (winter)
HST	−10:00		Honolulu (no DST)
MySQL Timezone Functions
NOW()	Returns current date/time in session timezone
UTC_TIMESTAMP()	Returns current UTC date/time
CONVERT_TZ()	Converts between named timezones: CONVERT_TZ(dt, from_tz, to_tz)
UNIX_TIMESTAMP()	Returns seconds since 1970-01-01 00:00:00 UTC
FROM_UNIXTIME()	Converts Unix epoch to DATETIME in session timezone

Frequently Asked Questions

TIMESTAMP columns are stored as UTC internally (4-byte Unix epoch). On INSERT, MySQL converts from the session time_zone to UTC. On SELECT, it converts back. DATETIME columns store the literal value with no conversion - they are timezone-unaware. If your session time_zone is "+00:00" when reading a TIMESTAMP, you get the raw UTC value. If reading a DATETIME that was inserted in local time, you get that local time back regardless of your current session timezone.

This tool uses the browser's native Date object, which references the IANA timezone database (tzdata). It correctly handles DST transitions, including the "spring forward" gap (where 2:00 AM jumps to 3:00 AM in US timezones) and the "fall back" overlap. For ambiguous times during the fall-back hour, the browser typically selects the standard-time interpretation. The offset is computed per-timestamp, not globally, so a batch of timestamps spanning a DST boundary will each get the correct offset.

MySQL TIMESTAMP uses a 32-bit signed integer storing seconds since 1970-01-01 00:00:00 UTC. The maximum value of a 32-bit signed integer is 2,147,483,647, which corresponds to 2038-01-19 03:14:07 UTC. This is the Y2K38 problem. For dates beyond this, use DATETIME (which supports up to 9999-12-31) or upgrade to MySQL 8.0.28+ which supports 64-bit TIMESTAMP on some platforms.

Run SELECT @@global.time_zone, @@session.time_zone; on the MySQL connection used for the export. If both return "+00:00" or "UTC", your data is in UTC. If it returns "SYSTEM", check the server OS timezone with SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP); - a result of "00:00:00" confirms UTC. If the offset is non-zero (e.g., '-05:00:00'), your export is in that local timezone, not UTC, and this converter would produce incorrect results.

Yes. MySQL 5.6.4+ supports fractional seconds in DATETIME(fsp) and TIMESTAMP(fsp) with up to 6 decimal places (microseconds). This tool parses the fractional part after the decimal point in "YYYY-MM-DD HH:MM:SS.ffffff". JavaScript Date objects have millisecond precision (3 decimal places), so microsecond digits 4-6 are preserved in display but truncated in the internal Date representation. The displayed output retains the original fractional precision.

Unix epoch in seconds counts whole seconds since 1970-01-01 00:00:00 UTC. MySQL's UNIX_TIMESTAMP() returns this format. JavaScript's Date.getTime() returns milliseconds since the same epoch (multiply by 1000). This tool outputs both: epoch seconds (matching MySQL convention) and epoch milliseconds (matching JavaScript convention). When interfacing between MySQL and JavaScript, this distinction prevents off-by-factor-of-1000 errors.