Developer Guide
Unix Timestamps in Databases
Store UTC epoch seconds in BIGINT to avoid time zones and the Y2038 overflow. Covers milliseconds versus seconds, range indexes, IST, and timestamptz.
A DATETIME column that stores “local server time” will shift when you move regions, when DST changes, or when two app servers disagree about the zone. A Unix timestamp is simpler: an integer count of seconds since 1970-01-01 00:00:00 UTC. 1700000000 is always 2023-11-14 22:13:20 UTC, no matter where the database runs.
You still convert for humans. Display IST as UTC+5:30 on /tools/timestamp-ist (India has no DST). Convert in the UI or in a view, not by rewriting the stored integer. /tools/timestamp is the general converter — Use Now, → Date, → Timestamp — and it stays in the browser.
Store UTC integers
Write epoch seconds from the application using a UTC clock. MySQL TIMESTAMP types have historical quirks with time zones; an integer column does not. Name the column created_at_unix or created_at_s so nobody passes milliseconds by accident.
Do not store formatted strings ('2023-11-14 22:13:20') if you plan to sort or filter by time. Strings sort lexicographically only if they are zero-padded ISO. Integers sort as numbers, which is what you want.
BIGINT and the year 2038
A signed 32-bit integer maxes out at 2147483647, which is 2038-01-19 03:14:07 UTC. That is the Y2038 overflow. Use BIGINT (64-bit) for epoch seconds in MySQL and Postgres. A 64-bit second counter is fine for any date you will actually store.
If you inherit an INT column, plan a migration before 2038, not during it. Storing milliseconds in a 32-bit column is already broken; milliseconds need 64 bits today (Date.now() is 13 digits).
Seconds versus milliseconds
Unix time in APIs, JWT exp / iat / nbf, and POSIX is seconds (10 digits for current dates). JavaScript Date.now() is milliseconds (13 digits). Divide by 1000, floor, and then store. Mixing them produces dates in 1970 or in the 50th millennium depending on which way you mixed.
/tools/timestamp works in seconds. /tools/jwt-debugger shows JWT time claims as Unix seconds, UTC, and local. 1704067200 is 2024-01-01 00:00:00 UTC. If you see 13 digits in a JWT, the issuer used the wrong unit.
Index range queries
A btree on a BIGINT epoch column makes WHERE created_at_unix >= 1700000000 AND created_at_unix < 1704067200 a range scan. That is “from 2023-11-14 22:13:20 UTC until 2024-01-01”. Convert the bounds in the application or with /tools/timestamp before you query.
Avoid wrapping the column in a function (FROM_UNIXTIME(created_at_unix) = '2023-11-14') if you want the index. Convert the day’s UTC start and end to two integers instead.
Postgres timestamptz versus epoch int
timestamptz stores an instant (UTC internally) and is the native Postgres choice. You get date arithmetic, AT TIME ZONE, and constraints. An epoch BIGINT is useful when the rest of the stack (Redis, JWT, JSON APIs) already speaks seconds, or when you share a schema with engines that lack a decent timestamp type.
Do not store timestamp without time zone as if it were UTC unless every writer agrees. If you mix timestamptz and integers, convert at the boundary: EXTRACT(EPOCH FROM created_at) and to_timestamp(1700000000). Display IST with /tools/timestamp-ist rather than adding 19800 in ad-hoc SQL in five places.
Common pitfalls
Storing local IST hours in a “unix” column (adding 19800 before insert) is the bug that looks right in India and wrong everywhere else. Store UTC. Add 5 hours 30 minutes only in the presenter.
Other traps: 32-bit INT; milliseconds in a seconds column; comparing JWT exp to Date.now() without dividing; and using TIMESTAMP in MySQL with a session time zone you did not set. Convert a suspect value on /tools/timestamp before you migrate it.
1700000000= 2023-11-14 22:13:20 UTC.- IST is UTC+5:30 with no DST —
/tools/timestamp-ist. - JWT time claims are seconds; inspect them on
/tools/jwt-debugger.
Worked examples
Seconds to UTC
17000000002023-11-14 22:13:20 UTCIn IST that display time is 2023-11-15 03:43:20. Convert on /tools/timestamp and /tools/timestamp-ist.
New Year 2024 and a JWT-sized expiry
17040672002024-01-01 00:00:00 UTCJavaScript would use 1704067200000 milliseconds. JWT exp should be the 10-digit second value.
Frequently asked questions
Should I store milliseconds?
Only if you need sub-second resolution and every writer uses 13-digit values. Otherwise store seconds in BIGINT and keep milliseconds out of JWT-like fields.
Is timestamptz better than BIGINT?
In Postgres, timestamptz is the natural type. Use BIGINT epoch when you must match an integer API or a non-SQL store. Do not mix units.
How do I show times in India?
Keep UTC in the database. Convert on read. /tools/timestamp-ist applies UTC+5:30 with no DST.
Why is my JWT already expired in the debugger?
Check the unit. /tools/jwt-debugger reads exp as Unix seconds. A 13-digit exp is milliseconds and will not mean what Date in JavaScript meant.
Summary
Store UTC seconds in a 64-bit column (or timestamptz in Postgres), index the integer, and convert for display. /tools/timestamp turns 1700000000 into a date; /tools/timestamp-ist applies +5:30; /tools/jwt-debugger shows the same second counts inside tokens.