Compare

TIMESTAMP vs DATETIME vs TIMESTAMPTZ

Q: Should I use TIMESTAMP or DATETIME?

On Postgres: prefer TIMESTAMPTZ (timezone-aware) for events. On MySQL: DATETIME is usually the safer default — TIMESTAMP auto-converts to/from UTC based on session timezone, which causes off-by-N-hour bugs across servers and DST transitions. SQL Server: DATETIME2 for new code; the legacy DATETIME has 3.33ms precision and a narrow range.

Q: What's the difference between TIMESTAMP and TIMESTAMPTZ in Postgres?

TIMESTAMP (without time zone) stores a fixed wall-clock value with no timezone awareness — "May 4 at 3pm" without saying where. TIMESTAMPTZ (with time zone) stores an unambiguous instant in time, displayed in the session's timezone. For event timestamps, TIMESTAMPTZ. For "scheduled at 9am locally" semantics, TIMESTAMP.

Q: Why does my TIMESTAMP column return different values on different servers?

On MySQL, TIMESTAMP columns auto-convert to/from UTC based on the session timezone. Same row, different connection settings, different displayed time. To fix: either set a consistent session timezone (SET time_zone = '+00:00'), or use DATETIME which doesn't auto-convert.

Q: How do I store a date with a known timezone?

Use a TZ-aware type (TIMESTAMPTZ on Postgres, DATETIMEOFFSET on SQL Server, TIMESTAMP_TZ on Snowflake) — the value carries the offset. Or store two columns: a wall-clock TIMESTAMP plus a tz_name like 'America/Los_Angeles'. The two-column form is more flexible (handles DST transitions cleanly).

Q: How do I convert a UTC timestamp to a different timezone?

Postgres: ts AT TIME ZONE 'America/Los_Angeles'. MySQL: CONVERT_TZ(ts, 'UTC', 'America/Los_Angeles'). SQL Server: ts AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time'. BigQuery: TIMESTAMP_TRUNC(ts, DAY, 'America/Los_Angeles') for date bucketing in a specific TZ.

Short answer: the names mean different things on different engines, and that's the source of most timezone bugs. The only durable framing: there are three real types. (1) Wall-clock with no timezone (DATETIME on MySQL, TIMESTAMP on Postgres without TZ). (2) Instant in time, timezone-aware (TIMESTAMPTZ on Postgres, TIMESTAMP on Snowflake/BigQuery). (3) Local time at the user's location, stored separately. Choose by which one you need; ignore the engine's preferred name.

Quick comparison #

	"Wall clock" — no TZ	"Instant" — TZ-aware	"Local time" — stored as wall + TZ
Means	"May 4 at 3pm" — at unspecified location	"May 4 at 3pm UTC" — globally unambiguous	"May 4 at 3pm in Tokyo"
Postgres	`TIMESTAMP`	`TIMESTAMPTZ`	(custom: store TZ separately)
MySQL	`DATETIME`	`TIMESTAMP` (auto-converts to UTC)	(custom)
SQL Server	`DATETIME2` / `DATETIME`	`DATETIMEOFFSET`	(custom)
Snowflake	`TIMESTAMP_NTZ`	`TIMESTAMP_TZ`	`TIMESTAMP_LTZ`
BigQuery	`DATETIME`	`TIMESTAMP`	(custom)
Oracle	`TIMESTAMP`	`TIMESTAMP WITH TIME ZONE`	`TIMESTAMP WITH LOCAL TIME ZONE`
Use for	scheduling at local time, calendar dates	events that happened, audit timestamps	per-user "my time" displays

See the difference #

Loading SQL editor...

When to use which #

Wall-clock (no TZ) — when the value represents a clock reading at a place, not an instant. "The store opens at 9am local time" — that 9am is a wall-clock time, and it means different absolute instants in Tokyo vs Berlin. Calendar reservations, scheduled jobs that fire "at 3am locally," birthdays.

Timezone-aware instant — the default for events that happened. "User signed up at this point in time." Stored canonically as UTC; displayed in whatever timezone makes sense for the consumer. created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP is the canonical audit-column shape.

Local time with stored timezone — when the user's local context is part of the meaning. A flight scheduled for "10:30 AM Tokyo time" — the timezone is part of the data, not the presentation. Often modeled as a wall-clock column plus a timezone column rather than a special engine type.

Common mistakes #

MySQL's TIMESTAMP quietly converts to/from UTC: storing '2026-04-26 14:30:00' in a TIMESTAMP column on a session set to UTC returns the same string later, but on a session set to America/New_York returns '2026-04-26 10:30:00'. This caught entire engineering teams out for years; DATETIME does not auto-convert (and it cannot, because it carries no TZ).
Mixing TIMESTAMP and TIMESTAMPTZ in comparisons: most engines auto-cast, but the cast assumes a session timezone — silently wrong if you assumed UTC and the session was set to local. Pick one type per column and stick with it.
Displaying TIMESTAMP without conversion in user-facing dashboards: the user sees "14:30" with no clue whether that's UTC or local. Always present TZ-aware times with an explicit timezone in the UI, or convert to the user's TZ first.
DATETIME2 vs DATETIME on SQL Server: DATETIME2 is newer, more precise, larger range. Default to DATETIME2 unless you have a legacy reason for DATETIME (which has 3.33ms precision and only goes back to 1753).

Dialect notes #

Postgres: TIMESTAMP WITHOUT TIME ZONE (alias TIMESTAMP) and TIMESTAMP WITH TIME ZONE (alias TIMESTAMPTZ). TIMESTAMPTZ stores in UTC internally; displays in session timezone. The default; recommended.
MySQL: DATETIME (no TZ, fixed clock) vs TIMESTAMP (TZ-aware, auto-converts to/from UTC for display, range limited to 1970-2038). Most teams use DATETIME plus an application-side UTC convention to avoid the TIMESTAMP gotchas.
SQL Server: DATETIME2 (modern, no TZ), DATETIMEOFFSET (with TZ), DATETIME (legacy, low precision, narrow range). Avoid DATETIME for new code.
Snowflake: three explicit types: TIMESTAMP_NTZ (no TZ), TIMESTAMP_TZ (with TZ), TIMESTAMP_LTZ (TZ-aware but always rendered in session TZ). TIMESTAMP is an alias for one of these depending on the TIMESTAMP_TYPE_MAPPING parameter — explicit naming is safer.
BigQuery: TIMESTAMP (UTC-based instant) vs DATETIME (no TZ, civil time). Use TIMESTAMP for events; DATETIME for "wall clock at a place."
Oracle: TIMESTAMP (no TZ), TIMESTAMP WITH TIME ZONE (stores TZ), TIMESTAMP WITH LOCAL TIME ZONE (stores in DB TZ, displays in session TZ).

FAQ #

Should I use TIMESTAMP or DATETIME?

On Postgres: prefer TIMESTAMPTZ (timezone-aware) for events. On MySQL: DATETIME is usually the safer default — TIMESTAMP auto-converts to/from UTC based on session timezone, which causes off-by-N-hour bugs across servers and DST transitions. SQL Server: DATETIME2 for new code; the legacy DATETIME has 3.33ms precision and a narrow range.

What's the difference between TIMESTAMP and TIMESTAMPTZ in Postgres?

TIMESTAMP (without time zone) stores a fixed wall-clock value with no timezone awareness — "May 4 at 3pm" without saying where. TIMESTAMPTZ (with time zone) stores an unambiguous instant in time, displayed in the session's timezone. For event timestamps, TIMESTAMPTZ. For "scheduled at 9am locally" semantics, TIMESTAMP.

Why does my TIMESTAMP column return different values on different servers?

On MySQL, TIMESTAMP columns auto-convert to/from UTC based on the session timezone. Same row, different connection settings, different displayed time. To fix: either set a consistent session timezone (SET time_zone = '+00:00'), or use DATETIME which doesn't auto-convert.

How do I store a date with a known timezone?

Use a TZ-aware type (TIMESTAMPTZ on Postgres, DATETIMEOFFSET on SQL Server, TIMESTAMP_TZ on Snowflake) — the value carries the offset. Or store two columns: a wall-clock TIMESTAMP plus a tz_name like 'America/Los_Angeles'. The two-column form is more flexible (handles DST transitions cleanly).

Why am I getting off-by-one-hour errors at month-end?

Daylight-saving transition. If you store local time, the same wall-clock can mean two different instants on the DST switch day. Solution: store all event timestamps in UTC, convert to display timezone at the edge. The bugs disappear.

How do I convert a UTC timestamp to a different timezone?

Postgres: ts AT TIME ZONE 'America/Los_Angeles'. MySQL: CONVERT_TZ(ts, 'UTC', 'America/Los_Angeles'). SQL Server: ts AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time'. BigQuery: TIMESTAMP_TRUNC(ts, DAY, 'America/Los_Angeles') for date bucketing in a specific TZ.

Tip

**The defensive pattern that survives every engine: store every event timestamp as a TZ-aware instant, in UTC, in a clearly named column (_at_utc or just _at if you've documented the convention).** Convert to user-local timezone only at the display layer. The bugs disappear: comparisons are unambiguous, daylight-saving-time transitions don't move events, and "what time is it?" has a single answer everywhere in your system.