Convert Unix Timestamp in MySQL

MySQL provides UNIX_TIMESTAMP() (date/time → epoch seconds) and FROM_UNIXTIME() (epoch seconds → date/time). The main gotcha is timezone: conversions are affected by your session settings and by whether you use DATETIME or TIMESTAMP.

Unix Timestamp to DATETIME / DATE

MySQL

-- Seconds → DATETIME
SELECT FROM_UNIXTIME(1704067200);
-- Result: 2024-01-01 00:00:00 (in your session timezone)

-- Seconds → DATE only
SELECT DATE(FROM_UNIXTIME(1704067200));
-- Result: 2024-01-01

-- Milliseconds → DATETIME (divide by 1000; keep decimals if needed)
SELECT FROM_UNIXTIME(1704067200000 / 1000);
-- Result: 2024-01-01 00:00:00

-- Fractional seconds (MySQL supports decimals)
SELECT FROM_UNIXTIME(1704067200.5);
-- Result: 2024-01-01 00:00:00.500000

DATETIME / DATE to Unix Timestamp

MySQL

-- Current epoch seconds
SELECT UNIX_TIMESTAMP();
-- Result: 1704067200

-- Current epoch milliseconds
SELECT UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)) * 1000 AS unix_ms;
-- Result: 1704067200123

-- From a DATETIME literal (interpreted in your session timezone)
SELECT UNIX_TIMESTAMP('2024-01-01 00:00:00');
-- Result: depends on @@session.time_zone

-- From a DATE (midnight)
SELECT UNIX_TIMESTAMP('2024-01-01');
-- Result: epoch for 2024-01-01 00:00:00 (session timezone)

DATE and DATETIME Types (and why TIMESTAMP matters)

DATE stores only the calendar date. DATETIME stores date and time without timezone conversion. TIMESTAMP stores an instant and is converted between UTC (storage) and your session time zone (display).

MySQL

-- DATE: calendar day only
CREATE TABLE example_date (
  d DATE
);

-- DATETIME: no timezone conversion
CREATE TABLE example_datetime (
  dt DATETIME(6)
);

-- TIMESTAMP: stored in UTC, converted on input/output using @@session.time_zone
CREATE TABLE example_timestamp (
  ts TIMESTAMP(6)
);

Timezone Handling with CONVERT_TZ()

MySQL

-- Inspect session/global timezone
SELECT @@session.time_zone, @@global.time_zone;

-- Recommended: run the session in UTC for predictable epoch conversions
SET time_zone = '+00:00';

-- Convert a Unix timestamp to a specific timezone
-- (FROM_UNIXTIME() produces a DATETIME in the session timezone)
SELECT CONVERT_TZ(FROM_UNIXTIME(1704067200), '+00:00', 'America/New_York') AS ny_time;
-- Result: 2023-12-31 19:00:00

SELECT CONVERT_TZ(FROM_UNIXTIME(1704067200), '+00:00', 'Asia/Tokyo') AS tokyo_time;
-- Result: 2024-01-01 09:00:00

-- Convert an arbitrary DATETIME from one zone to another
SELECT CONVERT_TZ('2024-01-01 00:00:00', 'UTC', 'America/Los_Angeles') AS la_time;
-- Result: 2023-12-31 16:00:00

Named time zones require MySQL time zone tables

If time zone tables aren’t loaded, conversions like CONVERT_TZ(..., 'UTC', 'America/New_York') can return NULL. Offsets like +00:00 still work.

Indexing Unix Timestamp Columns

Indexing works well when you filter on the stored value. If you wrap an indexed column in FROM_UNIXTIME() or UNIX_TIMESTAMP(), MySQL typically can’t use the index efficiently.

MySQL

-- Store epoch seconds and index it
CREATE TABLE events (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  created_at BIGINT NOT NULL,
  KEY idx_created_at (created_at)
);

-- ✅ Index-friendly: compare epoch to epoch
SELECT *
FROM events
WHERE created_at >= UNIX_TIMESTAMP('2024-01-01 00:00:00')
  AND created_at <  UNIX_TIMESTAMP('2024-02-01 00:00:00');

-- ❌ Often not index-friendly: function on the column
SELECT *
FROM events
WHERE FROM_UNIXTIME(created_at) >= '2024-01-01 00:00:00';

-- Option 1: generated column + index (works in MySQL 5.7+)
ALTER TABLE events
  ADD COLUMN created_at_dt DATETIME
    GENERATED ALWAYS AS (FROM_UNIXTIME(created_at)) STORED,
  ADD KEY idx_created_at_dt (created_at_dt);

-- Option 2 (MySQL 8.0.13+): functional index
-- CREATE INDEX idx_created_at_dt ON events ((FROM_UNIXTIME(created_at)));

Common Pitfalls

Seconds vs milliseconds

MySQL’s Unix timestamp functions use seconds. If you pass milliseconds directly, you’ll get a date far in the future.

-- ❌ Wrong - milliseconds treated as seconds
SELECT FROM_UNIXTIME(1704067200000);
-- Result: far in the future (wrong)

-- ✅ Correct - divide by 1000
SELECT FROM_UNIXTIME(1704067200000 / 1000);

Session timezone affects results

FROM_UNIXTIME() and UNIX_TIMESTAMP() can depend on @@session.time_zone. Set the session timezone to UTC in apps and migrations for consistent conversions.

-- Make results consistent across environments
SET time_zone = '+00:00';
SELECT FROM_UNIXTIME(1704067200);
SELECT UNIX_TIMESTAMP('2024-01-01 00:00:00');

DATETIME vs TIMESTAMP

DATETIME stores a literal wall-clock time. TIMESTAMP stores an instant and is timezone-converted on input/output. Mixing them without a clear policy can cause subtle bugs around DST and deployments.

CONVERT_TZ() returning NULL

Named time zones require time zone tables. If they’re missing, prefer numeric offsets (e.g. +00:00) or load the tables in your MySQL environment.

Frequently Asked Questions

How do I convert a Unix timestamp to DATETIME in MySQL?

Use FROM_UNIXTIME(unix_seconds). For a DATE only, wrap it with DATE(...): DATE(FROM_UNIXTIME(unix_seconds)).

How do I get the current Unix timestamp in MySQL?

Use UNIX_TIMESTAMP() for seconds. For milliseconds, use UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)) * 1000.

Does FROM_UNIXTIME() return UTC?

Not necessarily. FROM_UNIXTIME() returns a DATETIME value in your session time zone (via @@session.time_zone). Set the session time zone to UTC for consistent results.

Why does CONVERT_TZ() return NULL?

MySQL needs its time zone tables loaded to convert named zones like America/New_York. If they are missing, CONVERT_TZ() may return NULL (offsets like +00:00 still work).

How do I keep queries index-friendly when converting epoch?

Avoid wrapping indexed columns in functions in your WHERE clause. Compare numeric epochs directly (e.g., created_at >= UNIX_TIMESTAMP('2024-01-01')) or use a generated/functional index for derived datetime values.