Unix timestamps in SQL: Postgres, MySQL, and SQLite

If you have ever opened a database and seen a column called created_at containing a 10-digit number, you have run into one of the great schema arguments of our profession: do you store time as a Unix integer, or as a native timestamp type?

There is no single right answer, but each database does treat Unix timestamps differently, and the conversion functions are easy to forget. This guide is a quick reference for Postgres, MySQL, and SQLite — and a short take on which storage strategy to pick.

Postgres

Postgres has the strongest opinion of the three: it really wants you to use TIMESTAMPTZ. The conversions are first-class, but they are not as obvious as UNIX_TIMESTAMP() in MySQL.

Now as Unix seconds:

SELECT EXTRACT(EPOCH FROM NOW())::bigint AS unix_seconds;
-- 1745301600

EXTRACT(EPOCH FROM ...) returns a double precision (float). Casting to bigint truncates to integer seconds. If you want milliseconds, multiply first:

SELECT (EXTRACT(EPOCH FROM NOW()) * 1000)::bigint AS unix_millis;

Convert a Unix integer to a timestamp:

SELECT to_timestamp(1745301600);
-- 2026-04-22 08:00:00+00

to_timestamp accepts seconds (integer or float). For milliseconds, divide:

SELECT to_timestamp(1745301600847 / 1000.0);

Use it in a query:

-- Find rows from the last 7 days, given an integer column.
SELECT *
FROM events
WHERE created_at > EXTRACT(EPOCH FROM NOW() - INTERVAL '7 days');

-- Or, if created_at is a TIMESTAMPTZ:
SELECT *
FROM events
WHERE created_at > NOW() - INTERVAL '7 days';

The second form is shorter, more readable, and uses Postgres’s native operators. This is why most Postgres advice nudges you towards TIMESTAMPTZ columns.

Index considerations: a B-tree on a BIGINT column is slightly smaller than on a TIMESTAMPTZ (8 bytes vs 8 bytes — actually the same, but TIMESTAMPTZ has microsecond resolution baked in). Performance is essentially identical. Pick TIMESTAMPTZ.

MySQL

MySQL has had UNIX_TIMESTAMP() and FROM_UNIXTIME() since forever, and they are the most ergonomic of the three databases.

Now as Unix seconds:

SELECT UNIX_TIMESTAMP();
-- 1745301600

For milliseconds, MySQL 5.6 and later give you fractional seconds:

SELECT UNIX_TIMESTAMP(NOW(6)) * 1000;
-- 1745301600847

Convert a Unix integer to a DATETIME:

SELECT FROM_UNIXTIME(1745301600);
-- 2026-04-22 10:00:00 (in the server's session timezone)

SELECT FROM_UNIXTIME(1745301600, '%Y-%m-%dT%H:%i:%sZ');
-- '2026-04-22T10:00:00Z'

Watch out for timezone behaviour. FROM_UNIXTIME formats the result in the session timezone. If your server is set to a non-UTC zone, you’ll see local time. Set the session explicitly to UTC if you need predictability:

SET time_zone = '+00:00';
SELECT FROM_UNIXTIME(1745301600);
-- 2026-04-22 08:00:00

Use it in a query:

-- Integer column.
SELECT *
FROM events
WHERE created_at > UNIX_TIMESTAMP() - 7 * 86400;

-- TIMESTAMP column.
SELECT *
FROM events
WHERE created_at > NOW() - INTERVAL 7 DAY;

Storage choice: MySQL’s TIMESTAMP column type is internally a 32-bit Unix integer. That means it has the Year 2038 problem baked in. If you’re on MySQL 8, prefer DATETIME(6) for new columns — it’s calendar-aware, microsecond-precise, and not affected by 2038. For backwards compatibility with old applications, BIGINT storing seconds or milliseconds is also fine.

SQLite

SQLite has no dedicated date/time type. Everything is text or numbers, and you choose how to store it. The three common storage formats are:

• ISO 8601 strings: '2026-04-22T08:00:00Z'
• Unix seconds as integers: 1745301600
• Julian day numbers as floats (rarely used)

The built-in datetime, date, time, and strftime functions accept any of these.

Now as Unix seconds:

SELECT unixepoch();
-- 1745301600

unixepoch() was added in SQLite 3.38 (February 2022). Earlier versions need:

SELECT strftime('%s', 'now');
-- '1745301600'  (a string!)

SELECT CAST(strftime('%s', 'now') AS INTEGER);
-- 1745301600

Convert a Unix integer to ISO 8601:

SELECT datetime(1745301600, 'unixepoch');
-- '2026-04-22 08:00:00'

SELECT strftime('%Y-%m-%dT%H:%M:%SZ', 1745301600, 'unixepoch');
-- '2026-04-22T08:00:00Z'

The 'unixepoch' modifier tells SQLite that the number is in seconds since 1970, not Julian days.

Storage choice: for application data in SQLite, ISO 8601 strings are usually the right call. They sort lexicographically (because ISO 8601 was designed that way), they are self-documenting when you SELECT them, and they round-trip cleanly with most application code. Unix integers are slightly smaller and slightly faster for range queries, but the difference is rarely meaningful.

If you do go with integers, prefer milliseconds — that matches what most application languages produce by default and avoids the conversion at the boundary.

Storing Unix integers vs native timestamp types

Here is the trade-off, generalised:

Pros of storing Unix integers:

• Compact (8 bytes for a BIGINT).
• Trivial round-trip with application code.
• Same value across timezones — you never have to think about session zones.
• Easy to do arithmetic in SQL (subtract, add intervals as raw seconds).

Pros of storing native timestamps:

• Type safety. The database knows it’s a date and helps you with operators.
• Built-in formatting (TO_CHAR in Postgres, DATE_FORMAT in MySQL).
• Indexes know they’re sorting time, which sometimes enables better plans.
• Postgres’ TIMESTAMPTZ handles timezones automatically.

For most applications, the answer is: use native timestamps (TIMESTAMPTZ in Postgres, DATETIME(6) in MySQL, ISO strings in SQLite). The slight loss in storage compactness is dominated by the gain in correctness and ergonomics.

The case for Unix integers is strongest when:

• You have a very high-volume table where every byte matters.
• Your data is already coming from a system that produces Unix integers and you don’t want a conversion layer.
• You are working with a database (or ORM) that handles native timestamps poorly.

A reference cheat sheet

Quick lookup for common conversions:

Operation	Postgres	MySQL	SQLite
Now → Unix seconds	EXTRACT(EPOCH FROM NOW())::bigint	UNIX_TIMESTAMP()	unixepoch()
Unix seconds → DateTime	to_timestamp(1745301600)	FROM_UNIXTIME(1745301600)	datetime(1745301600, 'unixepoch')
Now → ISO 8601	to_char(NOW() AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')	DATE_FORMAT(UTC_TIMESTAMP(), '%Y-%m-%dT%H:%i:%sZ')	strftime('%Y-%m-%dT%H:%M:%SZ', 'now')
Last 7 days (integer col)	created_at > EXTRACT(EPOCH FROM NOW()) - 7 * 86400	created_at > UNIX_TIMESTAMP() - 7 * 86400	created_at > unixepoch() - 7 * 86400

If you have a Unix integer in front of you and want to know what date it represents, paste it into the Unixdates converter — auto-detect handles seconds, milliseconds, and microseconds.