SQL timestamps

PostgreSQL, MySQL and SQLite use different functions. Make the time zone explicit.

SQL types and Unix numbers

The SQL TIMESTAMP type is not universally an integer count of seconds. Its range, precision and time zone support depend on the database engine. For storage, see the dedicated guides to PostgreSQL, MySQL and MariaDB.

PostgreSQL

to_timestamp() converts Unix seconds to timestamp with time zone. The display depends on the session time zone. Here, output is explicitly UTC.

SELECT to_timestamp(1711972800) AT TIME ZONE 'UTC';
-- 2024-04-01 12:00:00

SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '2024-04-01 12:00:00+00');
-- 1711972800.000000

AT TIME ZONE 'UTC' produces a timestamp without a time zone whose fields represent UTC. Keep that convention when passing it to another application.

MySQL

FROM_UNIXTIME() displays values in the session time zone. Set it before your query to get reproducible results.

SET time_zone = '+00:00';
SELECT FROM_UNIXTIME(1711972800);
-- 2024-04-01 12:00:00

SELECT UNIX_TIMESTAMP('2024-04-01 12:00:00');
-- 1711972800

SQLite

The unixepoch modifier interprets the number as Unix seconds. Without the localtime modifier, the display remains UTC.

SELECT datetime(1711972800, 'unixepoch');
-- 2024-04-01 12:00:00

SELECT strftime('%s', '2024-04-01T12:00:00Z');
-- '1711972800' (texte)

The SQL TIMESTAMP type is not always a Unix integer. Check the type, precision and time zone conventions of your database engine and application.

References

PostgreSQL · MySQL · SQLite

Open the converter

English version published September 26, 2026.