SQL Snippets

SQL Unix Timestamp Snippets

SQL examples for Unix timestamp work across MySQL, PostgreSQL, and SQLite, including current epoch values, conversion to dates, and conversion back to timestamps.

JavaScript Python PHP Go Java C#Ruby Rust SQL C Perl

MySQL — current timestamp

-- Seconds
UNIX_TIMESTAMP()

-- Milliseconds (MySQL 8.0+)
ROUND(UNIX_TIMESTAMP(NOW(3)) * 1000)

MySQL — timestamp to datetime

FROM_UNIXTIME(1700000000)
FROM_UNIXTIME(1700000000, '%Y-%m-%d %H:%i:%S')

MySQL — datetime to timestamp

UNIX_TIMESTAMP('2023-11-15 06:13:20')

PostgreSQL — current timestamp

-- Seconds (integer)
EXTRACT(EPOCH FROM NOW())::BIGINT

-- Milliseconds
ROUND(EXTRACT(EPOCH FROM NOW()) * 1000)::BIGINT

PostgreSQL — timestamp to timestamptz

TO_TIMESTAMP(1700000000)
TO_TIMESTAMP(1700000000) AT TIME ZONE 'America/New_York'

PostgreSQL — datetime to epoch

EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2023-11-15T06:13:20Z')::BIGINT

SQLite — current timestamp

strftime('%s', 'now')  -- seconds

SQLite — timestamp to datetime

datetime(1700000000, 'unixepoch')
datetime(1700000000, 'unixepoch', 'localtime')

These SQL examples use Unix seconds for the portable baseline and show milliseconds only where the runtime exposes them naturally. The question to answer before copying a snippet is simple: what unit does the next API expect?

Get the current Unix time

-- PostgreSQL
SELECT EXTRACT(EPOCH FROM NOW())::bigint;

-- MySQL
SELECT UNIX_TIMESTAMP();

Convert a timestamp to UTC

-- PostgreSQL
SELECT to_timestamp(1700000000);

-- MySQL
SELECT FROM_UNIXTIME(1700000000);

Production note

Database functions vary by engine and session timezone. Use timezone-aware column types where available, and define range boundaries in one declared unit.

Keep the unit in the field name when a value crosses a boundary: createdAtSeconds and createdAtMs are longer than createdAt, but much less mysterious during an incident.

Epoch to Date Date to Epoch Timezone Converter All Code Snippets

Frequent questions:

Q: Should epoch milliseconds be stored in INT?: A: No. Modern millisecond timestamps are 13 digits and do not fit safely in a 32-bit INT. Use BIGINT or a native timestamp type.
Q: Are database timestamps always UTC?: A: No. Behavior depends on the database type and session timezone. Be explicit about UTC when importing, exporting, and comparing values across systems.

SQL Unix Timestamp Snippets

#Get the current Unix time

#Convert a timestamp to UTC

#Production note

Frequent questions:

Get the current Unix time

Convert a timestamp to UTC

Production note