Unix Timestamp in SQL

-- Current epoch (seconds)
SELECT EXTRACT(EPOCH FROM NOW())::bigint;
SELECT FLOOR(EXTRACT(EPOCH FROM NOW()));

-- Epoch to timestamp
SELECT TO_TIMESTAMP(1700000000);
SELECT TO_TIMESTAMP(1700000000) AT TIME ZONE 'UTC';

-- Timestamp to epoch
SELECT EXTRACT(EPOCH FROM '2023-11-14 22:13:20'::timestamp)::bigint;
SELECT EXTRACT(EPOCH FROM created_at)::bigint FROM users;

-- Milliseconds to timestamp
SELECT TO_TIMESTAMP(1700000000000 / 1000.0);

-- Filter rows within last 24 hours
SELECT * FROM events
WHERE created_at > NOW() - INTERVAL '24 hours';

-- Filter using epoch column
SELECT * FROM events
WHERE epoch_col > EXTRACT(EPOCH FROM NOW()) - 86400;

-- Current epoch (seconds)
SELECT UNIX_TIMESTAMP();
SELECT UNIX_TIMESTAMP(NOW());

-- Epoch to datetime
SELECT FROM_UNIXTIME(1700000000);
SELECT FROM_UNIXTIME(1700000000, '%Y-%m-%d %H:%i:%s');
SELECT FROM_UNIXTIME(1700000000, '%Y-%m-%dT%H:%i:%sZ');  -- ISO 8601

-- Datetime to epoch
SELECT UNIX_TIMESTAMP('2023-11-14 22:13:20');
SELECT UNIX_TIMESTAMP(created_at) FROM users;

-- Filter last 24 hours
SELECT * FROM events
WHERE created_at > NOW() - INTERVAL 24 HOUR;

-- Milliseconds
SELECT UNIX_TIMESTAMP() * 1000;  -- current ms epoch

-- Current epoch (seconds)
SELECT strftime('%s', 'now');
SELECT unixepoch();                  -- SQLite 3.38+

-- Epoch to datetime
SELECT datetime(1700000000, 'unixepoch');
SELECT datetime(1700000000, 'unixepoch', 'localtime');

-- Datetime to epoch
SELECT strftime('%s', '2023-11-14 22:13:20');
SELECT unixepoch('2023-11-14T22:13:20');

-- Filter last 7 days
SELECT * FROM events
WHERE created_at > strftime('%s', 'now', '-7 days');

-- Human-readable from stored epoch
SELECT datetime(created_at, 'unixepoch') FROM events;

-- Current epoch (seconds)
SELECT DATEDIFF(SECOND, '1970-01-01', GETUTCDATE());

-- Epoch to datetime
SELECT DATEADD(SECOND, 1700000000, '1970-01-01');

-- Datetime to epoch
SELECT DATEDIFF(SECOND, '1970-01-01', '2023-11-14 22:13:20');