unixtimestamp.live

Unix Timestamp in SQL

Every engine spells epoch conversion differently, and the differences are exactly where portable code breaks. Store UTC and convert on read.

Need to convert one value rather than write code? Use the interactive converter — it handles every unit and time zone without leaving your browser.

PostgreSQL

-- Current epoch
SELECT EXTRACT(EPOCH FROM now())::bigint;

-- Timestamp to date
SELECT to_timestamp(1710050400);                          -- timestamptz
SELECT to_timestamp(1710050400) AT TIME ZONE 'Asia/Kolkata';

-- Date to timestamp
SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '2024-03-10 06:00:00+00')::bigint;

-- Milliseconds
SELECT to_timestamp(1710050400000 / 1000.0);

MySQL / MariaDB

SELECT UNIX_TIMESTAMP();                       -- current epoch
SELECT FROM_UNIXTIME(1710050400);              -- uses session time_zone
SELECT UNIX_TIMESTAMP('2024-03-10 06:00:00');

-- Force UTC for the session so results are reproducible
SET time_zone = '+00:00';

-- Milliseconds
SELECT FROM_UNIXTIME(1710050400000 / 1000);

SQLite

SELECT strftime('%s', 'now');                          -- epoch as text
SELECT datetime(1710050400, 'unixepoch');              -- UTC
SELECT datetime(1710050400, 'unixepoch', 'localtime');
SELECT strftime('%s', '2024-03-10 06:00:00');

SQL Server / BigQuery / Snowflake

-- SQL Server
SELECT DATEDIFF_BIG(SECOND, '1970-01-01', GETUTCDATE());
SELECT DATEADD(SECOND, 1710050400, CAST('1970-01-01' AS datetime2));

-- BigQuery
SELECT UNIX_SECONDS(CURRENT_TIMESTAMP());
SELECT TIMESTAMP_SECONDS(1710050400);

-- Snowflake
SELECT DATE_PART(EPOCH_SECOND, CURRENT_TIMESTAMP());
SELECT TO_TIMESTAMP(1710050400);

Pitfalls specific to SQL

  • MySQL's FROM_UNIXTIME and UNIX_TIMESTAMP both apply the session time_zone. Two connections can get different answers from the same query.
  • PostgreSQL timestamp (without time zone) is not UTC — it is zoneless. Use timestamptz for real instants.
  • MySQL's TIMESTAMP column is 32-bit and overflows in 2038; DATETIME does not. This is still the default in many older schemas.

Rules that apply in every language

  1. Store UTC, display local. Keep the instant in UTC everywhere in your system and convert only at the point a human reads it.
  2. Name the unit in the identifier. expires_at_ms rather than expires_at costs nothing and prevents the single most common timestamp bug.
  3. Never trust a client clock. Stamp anything security-relevant on the server. See the note on clock skew.
  4. Use 64-bit time. Anything still storing seconds in a signed 32-bit field breaks in January 2038 — see the Year 2038 problem.

The same task in other languages

Frequently asked questions

How do I get the current Unix timestamp in SQL?

Use the snippet in the "Current Unix timestamp" section above. SQL works in varies by engine natively, so converting to another unit is a multiplication or an integer division away.

How do I convert a Unix timestamp to a date in SQL?

The "Timestamp to date" snippet above shows the idiomatic approach, including how to render the result in a specific time zone rather than whatever zone the machine happens to be set to.

Does SQL handle time zones and daylight saving correctly?

Yes, provided you pass an explicit zone rather than relying on the system default. The gotchas listed on this page cover the specific ways SQL makes that easy to get wrong.