SQL Current Timestamp (NOW) — All Dialects
Getting the current timestamp in SQL is a fundamental operation in database programming, but the exact syntax varies significantly between database systems. This guide covers all major SQL dialects — MySQL/MariaDB, PostgreSQL, SQLite, SQL Server, and Oracle — with the correct functions for each.
Beyond just getting the current time, you'll often need to perform arithmetic on it: find records from the last 24 hours, calculate when a subscription expires, or determine the age of a record in days. Each database has its own idioms for these operations, which are covered below alongside the basic current timestamp functions.
Current timestamp by SQL dialect
MySQL / MariaDB:
SELECT NOW(); — returns DATETIME in local timezone
SELECT UTC_TIMESTAMP(); — returns DATETIME in UTC
SELECT UNIX_TIMESTAMP(); — returns Unix seconds as INT
PostgreSQL:
SELECT NOW(); — returns TIMESTAMPTZ (transaction start time)
SELECT CLOCK_TIMESTAMP(); — returns actual current time
SELECT EXTRACT(EPOCH FROM NOW())::INT; — Unix seconds
SQLite:
SELECT datetime('now'); — UTC datetime string
SELECT unixepoch('now'); — Unix seconds (SQLite 3.38+)
SELECT strftime('%s', 'now'); — Unix seconds (all versions)
SQL Server:
SELECT GETDATE(); — local datetime
SELECT GETUTCDATE(); — UTC datetime
SELECT DATEDIFF(SECOND, '1970-01-01', GETUTCDATE()); — Unix seconds
Oracle:
SELECT SYSDATE FROM DUAL; — local datetime
SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL; — UTC
SELECT (SYSDATE - DATE '1970-01-01') * 86400 FROM DUAL; — Unix seconds