Unix Timestamp in SQL
SQL timestamp functions vary by database engine. MySQL, PostgreSQL, SQLite, and SQL Server each have different built-in functions for working with Unix epoch values. The core concept is the same: seconds since 1970-01-01 00:00:00 UTC.
Code Examples
MySQL — current timestamp
SELECT UNIX_TIMESTAMP();
UNIX_TIMESTAMP() returns the current Unix epoch in seconds as a decimal. With no argument, it returns the current server time.
MySQL — convert timestamp to datetime
SELECT FROM_UNIXTIME(1708560000);
FROM_UNIXTIME() converts a Unix timestamp to a DATETIME value in the server's local timezone. Use FROM_UNIXTIME(ts, '%Y-%m-%d') for formatted output.
PostgreSQL — current timestamp
SELECT EXTRACT(EPOCH FROM NOW());
EXTRACT(EPOCH FROM ...) works on any timestamp or interval. Combine with NOW() for the current Unix epoch. Returns a double-precision float.
PostgreSQL — convert timestamp to epoch
SELECT EXTRACT(EPOCH FROM TIMESTAMP '2024-06-15 12:00:00 UTC');
Convert any PostgreSQL TIMESTAMP or TIMESTAMPTZ literal to a Unix epoch. Always use UTC-aware timestamps to avoid timezone surprises.
SQLite — current timestamp
SELECT strftime('%s', 'now');SQLite has no native UNIX_TIMESTAMP function. strftime with the '%s' format specifier returns the current Unix epoch in seconds as a string — cast it with CAST(strftime('%s','now') AS INTEGER) for arithmetic.
SQL Server — current timestamp
SELECT DATEDIFF(SECOND, '1970-01-01', GETUTCDATE());
SQL Server has no built-in UNIX_TIMESTAMP. This uses DATEDIFF to count seconds from the Unix epoch to the current UTC time.
Note
MySQL's UNIX_TIMESTAMP() and FROM_UNIXTIME() are the most common. In PostgreSQL, EXTRACT(EPOCH FROM ...) is preferred. SQLite stores everything as text or integers — use strftime('%s', ...) for Unix epoch values. When storing Unix timestamps in a column, use a BIGINT to avoid the year-2038 problem that affects 32-bit integers.
Need to convert a specific timestamp? Use the live converter — paste any epoch value and see the human-readable date instantly.
← Open the converter