How to Store Dates and Times in MySQL (with Time Zones)

MySQL gives you two column types for a date-and-time value, and they behave differently in the one way that matters most: time zones. DATETIME is zone-naive: MySQL stores whatever you give it and hands the exact same value back, no conversion. TIMESTAMP is zone-aware: MySQL converts the value from the session time zone to UTC on the way in and back to the session time zone on the way out. MySQL has no timestamp with time zone type like PostgreSQL's timestamptz, so the correct pattern is to store every instant in UTC and, if you also need the original local zone, keep its IANA name in a separate column. Below is the comparison, the storage costs, and a worked events schema.

Short answer: store instants in UTC. Use TIMESTAMP for audit columns (created_at, updated_at) where you want MySQL's automatic UTC normalization and ON UPDATE CURRENT_TIMESTAMP, accepting the 2038 cap. Use DATETIME (holding a UTC value, with your app reading and writing UTC) for business dates, far-future dates, and anything that must outlive 2038. If you need to show the original wall-clock time, store the instant plus a VARCHAR IANA zone name like 'America/New_York' and convert for display.

DATETIME vs TIMESTAMP: the core difference

Both types hold a date and a time down to the second (or to the microsecond, see fractional seconds below). The split is entirely about what MySQL does with the value at the boundary.

DATETIME is a literal. You write 2017-11-14 09:00:00, MySQL stores those exact digits, and every client reads 2017-11-14 09:00:00 back regardless of their session time zone. Nothing is interpreted. It is a wall-clock value with no zone attached, which is a feature when you genuinely mean "9am local, whatever local turns out to be" and a trap when you meant a specific instant in time and forgot to record the zone.

TIMESTAMP is an instant. When you insert 2017-11-14 09:00:00 and your session time zone is America/New_York, MySQL converts that to UTC (2017-11-14 14:00:00) and stores the UTC value. When a client in Europe/London reads the same row, MySQL converts the stored UTC back to that client's session zone and returns 2017-11-14 14:00:00. Two clients, two different displayed strings, one underlying instant. That is exactly the behavior you want for "when did this happen", and exactly the surprise you do not want if you assumed the column was a dumb literal.

The conversion is driven by the connection's time_zone setting (SET time_zone = '+00:00' or an IANA name if the zone tables are loaded). If your session zone is UTC, TIMESTAMP and DATETIME look identical, which is why so many people never notice the difference until a server's time_zone changes or a client connects from another region.

Storage cost and range

The two types also differ in size and in the span of dates they can represent. This is where TIMESTAMP shows its age.

TIMESTAMP is a 32-bit count of seconds since the Unix epoch, which is why it both saves a byte and dies on 2038-01-19, when the signed 32-bit count overflows. DATETIME packs the broken-down date directly, so it runs to the year 9999 at the cost of one extra byte. For most modern schemas the byte is irrelevant and the year-2038 ceiling is the deciding factor. The fractional-second cost is the same for both: +1 byte for 1 to 2 digits, +2 for 3 to 4, +3 for 5 to 6, so a DATETIME(6) is 8 bytes and a TIMESTAMP(6) is 7.

Why there is no "timestamp with time zone" in MySQL

If you have come from PostgreSQL you are looking for timestamptz, the type that stores an instant and lets you attach or recover an offset. MySQL does not have it. TIMESTAMP is the closest thing, but it does not store a zone: it normalizes everything to UTC and the zone information is gone the moment the value is written. Two inserts of 09:00 from two different session zones become two different UTC instants, and neither row remembers which zone it originally came from.

The practical consequence is that MySQL cannot, on its own, tell you the original local time of an event. It can give you the instant in UTC, and it can render that instant in any zone you ask for at query time, but the "the user was in Sydney when this happened" fact is not something the column type preserves. If you need it, you store it yourself. This is also true on MariaDB, which mirrors MySQL's DATETIME/TIMESTAMP semantics exactly and likewise has no native zone-carrying type, so everything in this article ports to MariaDB unchanged.

The pattern: store instants in UTC

The rule that avoids almost every time-zone bug is: store every instant as UTC and convert only at the edges, when you display to a user. There are two clean ways to do that in MySQL.

Option A: DATETIME holding UTC. Your application sets its session zone to UTC (SET time_zone = '+00:00' on connect, or it just works in UTC end to end) and writes UTC values into a plain DATETIME. Because DATETIME does no conversion, what you write is what you read, and as long as your whole stack agrees on UTC there is no session-zone surprise. You also dodge the 2038 cap, which makes this the right choice for far-future dates (subscription expiries, scheduled jobs, legal retention dates) and anything long-lived.

Option B: TIMESTAMP with automatic normalization. You let MySQL do the UTC conversion for you and lean on the audit-column conveniences (DEFAULT CURRENT_TIMESTAMP, ON UPDATE CURRENT_TIMESTAMP). This is genuinely nice for created_at / updated_at: the database stamps and re-stamps the row without your code lifting a finger, and the value is correct UTC regardless of which app server (in which zone) did the write. The price is the 2038 ceiling, which is fine for audit metadata that describes recent activity and is not so fine for a date decades in the future.

My default split: TIMESTAMP for audit columns, DATETIME (in UTC) for business dates. The one thing you must not do is store a local wall-clock time without recording its zone. 2017-11-05 01:30:00 in America/New_York is genuinely ambiguous, because that local time occurs twice on the autumn DST fall-back. A bare DATETIME cannot disambiguate it. Store the UTC instant, and the ambiguity disappears.

Keeping the original local zone

When you do need the original wall-clock time (an event listing that says "starts 7:00 PM in the venue's local time", a calendar that must survive the city's DST rules changing), store two things: the instant in UTC, and the IANA time-zone name as a string.

The IANA name ('America/New_York', 'Europe/Paris', 'Asia/Tokyo') goes in a VARCHAR. Do not store a fixed offset like -05:00 instead, because the offset for a city changes across the year with DST, and the IANA name is what lets a library compute the correct offset for any given instant. With the UTC instant plus the zone name you can always reconstruct the local time, and you can re-render it correctly even if the zone's rules change later (governments adjust DST more often than you would think).

To convert at the database, MySQL's CONVERT_TZ(value, from_zone, to_zone) shifts a value between zones. You can call it with numeric offsets out of the box (CONVERT_TZ(starts_at, '+00:00', '-05:00')), but to use named zones like 'America/New_York' the server's time-zone tables must be loaded first, which is a one-time admin step:

# Populate mysql.time_zone* tables from the OS zoneinfo database (Linux/macOS)
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Without those tables, a named-zone CONVERT_TZ returns NULL. Many teams skip the database conversion entirely and convert in the application layer instead (where a full IANA database ships with the language runtime), which is often the cleaner choice. Either way, the storage pattern is the same: UTC instant plus zone name. For rendering the result in a SELECT, see formatting a MySQL DATETIME, which covers DATE_FORMAT and CONVERT_TZ for output.

A worked schema

Here is an events table that uses both types deliberately. The audit columns are TIMESTAMP so MySQL stamps them automatically in UTC; the user-facing event time is a DATETIME holding UTC plus a separate IANA zone column so the listing can show local wall-clock time.

CREATE TABLE events (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title       VARCHAR(200)    NOT NULL,
  -- the instant the event starts, stored in UTC
  starts_at   DATETIME        NOT NULL,
  -- the IANA zone the event is "in", for local-time display
  tz          VARCHAR(64)     NOT NULL DEFAULT 'UTC',
  -- audit columns: MySQL stamps these in UTC, no app code needed
  created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP
                                        ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- a 7:00 PM event in New York on 2026-06-15.
-- 7:00 PM EDT is 23:00 UTC, so starts_at holds the UTC instant.
INSERT INTO events (title, starts_at, tz)
VALUES ('Summer meetup', '2026-06-15 23:00:00', 'America/New_York');

To list the event in its own local time, convert the stored UTC starts_at into the row's tz at query time (this needs the time-zone tables loaded, per the section above):

SELECT
  title,
  starts_at                              AS starts_at_utc,
  CONVERT_TZ(starts_at, '+00:00', tz)    AS starts_at_local,
  tz
FROM events;
-- starts_at_local for the row above: 2026-06-15 19:00:00 (7:00 PM)

created_at and updated_at need no insert value: MySQL fills created_at from CURRENT_TIMESTAMP and bumps updated_at on every modification, both in UTC. Because they are TIMESTAMP, a client in any session zone reads them converted to its own zone automatically, which is usually what you want for "when was this row last touched". The starts_at DATETIME, by contrast, always reads back as the literal UTC value, and you decide how to present it.

For exact byte sizes of every column type, see MySQL data types and sizes, and for the conversion and formatting functions on one page, the MySQL cheat sheet covers NOW, UTC_TIMESTAMP, and CONVERT_TZ.

FAQ

See also

• Formatting a MySQL DATETIME for output covers DATE_FORMAT and CONVERT_TZ for rendering the stored UTC instant in a reader's zone.
• The MySQL data types and storage sizes reference lists the exact byte cost of DATETIME, TIMESTAMP, and every other column type.
• My MySQL cheat sheet keeps NOW, UTC_TIMESTAMP, and CONVERT_TZ on one page alongside the rest of the day-to-day syntax.
• When you need to migrate an existing column between these types, see changing a column type in MySQL for the ALTER TABLE path and its gotchas.
• Spin up a throwaway server to test the time-zone tables with running MySQL in Docker.
• Storing other tricky values? How to store a UUID in MySQL covers the BINARY(16) versus CHAR(36) tradeoff the same way this post weighs DATETIME against TIMESTAMP.