Timestamps vs Datetimes: What's the Difference?

A timestamp pins down a specific moment in time, no matter where you are. A datetime records what a clock on the wall shows, with no context about which clock or where the wall is. The difference matters every time you store a date.

Timestamp vs datetime: the core split

A timestamp nails down one point on the timeline. 2026-10-01T14:30:00Z is a timestamp. So is 2026-10-01T14:30:00+05:30. Both give you the exact moment.

A datetime is what a clock shows and nothing more. 2026-10-01 14:30:00 is a datetime. Is that 14:30 in Tokyo, London, or New York? The string does not say. Two people reading it will interpret it as their own local time. If one sits in UTC+10 and the other in UTC-5, the same string refers to two moments 15 hours apart.

The Unix epoch is 1 January 1970 00:00:00 UTC. A signed 32-bit time_t overflows on 19 January 2038 at 03:14:07 UTC. Systems still running 32-bit time_t will fail on that date. Most modern operating systems completed the 64-bit migration by the 2010s.

How MySQL, PostgreSQL and SQLite treat timestamps and datetimes

MySQL draws a hard line. TIMESTAMP columns store values in UTC internally and convert to the session time zone on every read and write. DATETIME columns store the literal value you hand them. No conversion. Ever.

Insert 2026-10-01 14:30:00 into a TIMESTAMP column with your session set to America/New_York. MySQL converts it to UTC before writing to disk. A reader in Tokyo, session set to Asia/Tokyo, sees a different wall-clock number. Same stored moment, different display.

Insert the same string into a DATETIME column. The bytes 2026-10-01 14:30:00 land on disk exactly as sent. Every reader sees the same string. The database has no idea what moment the string represents.

PostgreSQL uses different names for the same split. TIMESTAMP WITH TIME ZONE behaves like MySQL's TIMESTAMP: UTC on disk, session-zone conversion on access. TIMESTAMP WITHOUT TIME ZONE behaves like MySQL's DATETIME: literal storage, no conversion.

SQLite stores everything as text, real numbers, or integers. Interpretation is the application's problem. There is no built-in zone conversion.

Store a timestamp for events that already happened

Log entries. Transaction records. Audit trails. The moment is fixed. Convert to local time only at display time. The common advice "always store UTC" works here.

Store a datetime plus a zone for future events

Future events need a datetime and an IANA zone identifier. Time zone rules change. Governments shift DST start dates, abolish DST entirely, or change their standard offset. The IANA time zone database ships multiple updates per year as these decisions land.

Schedule a meeting for 14:00 local time on 1 November 2027 and store only the UTC equivalent. You have baked in today's DST rules. If the zone changes its rules before that date, your stored UTC moment is wrong. Store the local datetime plus the IANA zone name. America/New_York, for example. Compute the UTC moment at display time using the zone rules current on that future date.

Why DST breaks zone-less datetimes

On the spring-forward day, the clock jumps from 01:59 to 03:00. The datetime 02:30 never existed in that zone that morning. On the fall-back day, the clock repeats the hour from 01:00 to 02:00. The datetime 01:15 exists twice.

A timestamp sidesteps this entirely. It references the moment, not the wall clock. A datetime without a zone cannot tell the two occurrences apart.

Converting between timestamp and datetime safely

Converting a timestamp to a datetime is straightforward. Apply a zone offset and drop it.

Converting a datetime to a timestamp demands knowing which zone the datetime belongs to. Guessing is not safe. A datetime string arriving from a system in Berlin does not guarantee Europe/Berlin time. The system clock might be set to UTC. An operator might have changed the zone without updating the application config.

Three-letter abbreviations make conversion logic worse. "EST" means UTC-5 in North America and UTC+10 in Australia. "IST" means UTC+5:30 in India and UTC+2 in Israel. Never use them. Reach for IANA identifiers: America/New_York, Asia/Kolkata.

The safe path: datetime string plus known IANA zone equals a moment. Store the moment as a timestamp. If you must store the datetime, store the IANA zone next to it.

Why does MySQL offer both TIMESTAMP and DATETIME?

MySQL's TIMESTAMP converts to UTC internally and back to the session zone on read. Its range is limited: 1970 to 2038. DATETIME stores the literal value with no conversion and a much larger range. Pick TIMESTAMP for past-event logging. Pick DATETIME when you need to store a future local time and you are storing the zone separately.

Can I store a UTC offset instead of a time zone name?

No. A UTC offset like +05:30 tells you the offset at the moment of storage. It does not tell you whether DST applied on that date, nor what the offset will be for a future date. Store the IANA zone name for future events. Store the offset alongside the timestamp for past events only if you need to reproduce the original local time exactly.

What happens to timestamps during a leap second?

POSIX systems ignore leap seconds. The POSIX standard mandates 86,400 seconds per day. A leap second is handled by repeating the last second of the day or by smearing the extra second across the day. The epoch value does not increase by one during a leap second. Most applications never hit this edge case. High-precision systems must account for it explicitly. Financial trading. Astronomical observation.

No new leap second has been inserted since 31 December 2016. The General Conference on Weights and Measures resolved in November 2022 to abolish leap seconds by 2035, replacing them with a larger correction at longer intervals.

Is JavaScript's Date object a timestamp or a datetime?

A timestamp. It stores a single moment as milliseconds since 1 January 1970 UTC. The confusion comes from display methods: .toString() applies the system's local zone. The underlying value is always zone-agnostic. Call toISOString() to get a UTC timestamp string.

Which format should I use for API design?

Require ISO 8601 strings with an offset or Z for all API timestamps. Accept both timestamp strings and epoch integers from clients. Reject datetime strings that lack offset information. This policy kills ambiguity at the API boundary.

What to do next

Audit your database schemas and API contracts. Find every column or field that holds a date or time. Decide: past instant or future appointment? For past instants, store UTC. For future appointments, store the local datetime and the IANA zone name. Read why you should store dates in UTC for the past-event case in detail. For database-specific type choices, see time handling in MySQL, PostgreSQL, and SQLite.