SQL Timestamp Conversion: MySQL, PostgreSQL, and SQLite

If you work across multiple databases, you've probably noticed that timestamp handling is different everywhere. The SQL standard is more of a suggestion than a rule, and each database has its own set of functions, quirks, and date types.

I've spent way too many hours staring at TIMESTAMP vs TIMESTAMPTZ errors and Googling "MySQL unix timestamp to date" for the dozenth time. So here's a side-by-side reference for the three databases you're most likely to encounter in the wild.

First, a Word on the Data Types

How each database stores timestamps is where the divergence starts:

• MySQL: DATETIME (no timezone, range 1000-9999) vs TIMESTAMP (UTC storage, range 1970-2038, automatic timezone conversion on read/write based on session timezone). Yes, that 2038 limit is real in MySQL's TIMESTAMP — it's a 4-byte integer under the hood.

• PostgreSQL: TIMESTAMP (no timezone, also called TIMESTAMP WITHOUT TIME ZONE) vs TIMESTAMPTZ (with timezone, actually TIMESTAMP WITH TIME ZONE). PostgreSQL's TIMESTAMPTZ doesn't store the offset — it converts everything to UTC internally and uses the session timezone for display.

• SQLite: No dedicated date/time type at all. It stores dates as text ('2024-07-01 00:00:00'), real numbers (Julian day), or integers (Unix seconds). Whatever you throw at it, it stores as-is.

Converting Unix Timestamp to Date

This is the most common task — you have a number like 1719820800 and you want a human-readable date.

MySQL

SELECT FROM_UNIXTIME(1719820800);
-- 2024-07-01 00:00:00

SELECT FROM_UNIXTIME(1719820800, '%Y-%m-%d');
-- 2024-07-01

FROM_UNIXTIME() uses the session's time_zone setting. If your MySQL session is set to UTC, you get UTC. If it's set to Asia/Shanghai, you get 2024-07-01 08:00:00. Check your session with SELECT @@session.time_zone;.

For milliseconds, divide by 1000:

SELECT FROM_UNIXTIME(1719820800000 / 1000);

PostgreSQL

SELECT to_timestamp(1719820800);
-- 2024-07-01 00:00:00+00

SELECT to_timestamp(1719820800)::date;
-- 2024-07-01

SELECT to_timestamp(1719820800)::timestamp;
-- 2024-07-01 00:00:00

to_timestamp() returns a TIMESTAMPTZ (UTC). Cast it to ::date or ::timestamp depending on what you need. For milliseconds, pass seconds as a decimal:

SELECT to_timestamp(1719820800000 / 1000.0);

SQLite

SELECT datetime(1719820800, 'unixepoch');
-- 2024-07-01 00:00:00

SELECT date(1719820800, 'unixepoch');
-- 2024-07-01

SELECT strftime('%Y-%m-%d %H:%M:%S', 1719820800, 'unixepoch');
-- 2024-07-01 00:00:00

The 'unixepoch' modifier tells SQLite to treat the number as seconds since 1970. Without it, SQLite treats the number as a Julian day and you get a garbage date.

SQLite's date functions always return UTC. There's no session timezone — you add offsets manually:

SELECT datetime(1719820800, 'unixepoch', '+8 hours');
-- 2024-07-01 08:00:00

Converting Date to Unix Timestamp

MySQL

SELECT UNIX_TIMESTAMP('2024-07-01');
-- 1719792000 (or 1719820800, depends on session timezone)

SELECT UNIX_TIMESTAMP('2024-07-01 00:00:00 UTC');

UNIX_TIMESTAMP() respects the session timezone. Pass '2024-07-01' in a UTC+8 session, and you get the timestamp for 2024-07-01 00:00:00 UTC+8, which is 2024-06-30 16:00:00 UTC. That's 1719792000 — not 1719820800. If you always want UTC, set the timezone explicitly at the session level or pass UTC strings.

PostgreSQL

SELECT extract(epoch from timestamp '2024-07-01');
-- 1719792000

SELECT extract(epoch from timestamptz '2024-07-01 00:00:00+00');
-- 1719792000

SELECT extract(epoch from now());
-- current timestamp

extract(epoch from ...) returns seconds as a double precision float. Cast to integer if you need a whole number. The timezone matters — timestamp '2024-07-01' is assumed to be in the session timezone, while timestamptz '2024-07-01 00:00:00+00' is explicitly UTC.

SQLite

SELECT strftime('%s', '2024-07-01');
-- 1719792000

SELECT strftime('%s', 'now');
-- current timestamp

strftime('%s', ...) returns seconds since epoch in UTC. It's always UTC, no ambiguity. If you need to account for a timezone, append the offset as a modifier:

SELECT strftime('%s', '2024-07-01 00:00:00', '+8 hours');
-- 1719763200 (which is 2024-06-30 16:00:00 UTC)

Storing Timestamps: What I've Learned

After moving between these databases a few times, here's what's worked for me:

Store as Unix integer when you can. An INTEGER or BIGINT column containing a Unix timestamp is the most portable format across databases. No data type mapping issues, no timezone ambiguity, no 2038 problems if you use 64-bit.

Use the native TIMESTAMPTZ if your ORM expects it. If your application framework (like Entity Framework, Django ORM, or ActiveRecord) maps columns to language-native DateTime types, fighting the convention is usually more trouble than it's worth. Just be aware of the session timezone — set it explicitly in your connection string or on session startup.

Test with a query at 2 AM on a DST boundary. Daylight saving time transitions have broken more production queries than I can count. A timestamp at 2024-03-10 02:30:00 in US Eastern time simply doesn't exist during the spring-forward transition. TIMESTAMPTZ handles this gracefully; TIMESTAMP might not.

Quick Reference Table

Wrapping Up

Switching between MySQL, PostgreSQL, and SQLite doesn't have to mean relearning date handling every time. The concepts are the same — it's just the function names that change.

If you remember nothing else:

• MySQL's FROM_UNIXTIME() / UNIX_TIMESTAMP() — watch the session timezone
• PostgreSQL's to_timestamp() / extract(epoch from ...) — cast to the type you need
• SQLite's datetime(..., 'unixepoch') / strftime('%s', ...) — always UTC, always explicit
• When in doubt, store as a 64-bit integer and convert in application code

For quick timestamp checks across timezones, the FastUnix Timestamp Converter saves more time than writing a SQL query just to check one number.