Skip to content
notes.vinhnq.com

Search

Database & ORMMid23 min read

Storing dates and times in PostgreSQL, MySQL and JSON: timestamptz, DATETIME, Jackson and the traps

Review 18 cards
On this page

Dates and times in the backend series: 1. Epoch, time zones and java.time · 2. Storing them in databases and JSON

Correct Java code can still write wrong data, because every layer (database, driver, ORM, JSON, frontend) has its own idea of what time means. After this post you can pick the right column types in PostgreSQL and MySQL, configure JDBC/Hibernate/Jackson, and avoid the traps of exchanging times as JSON. Read part 1, Epoch, time zones and java.time, first: this post assumes you know that an Instant is a global moment and a LocalDateTime is a wall-clock value that belongs to no region.

At a glance:

flowchart TD
  A{"What is the data?"} -->|"Instant in time"| B["Store as UTC"]
  A -->|"Future appointment"| C["Wall time + zone id"]
  B --> D["PostgreSQL: timestamptz"]
  B --> E["MySQL: DATETIME(6) in UTC"]
  C --> F["Instant: precomputed column"]
  D --> G["JVM, Hibernate, driver all UTC"]
  E --> G
  G --> H["JSON: RFC 3339 with Z"]

All SQL and Java output shown here is real (PostgreSQL 17, MySQL 8.4, JDK 21, Jackson 2.18).

1. Timestamps and datetimes are different kinds of data

Key idea: a timestamp is a point on the timeline, the same for the whole world; a datetime is a calendar-and-clock value tied to a place. The original slides call this the most important distinction.

  • Timestamp (a moment): created_at, paid_at, when an email was sent. Changing the display time zone changes the clock time shown, not the moment.
  • Datetime (a wall-clock value): “the meeting is at 9:00 on 1 July 2027 in Paris”, “the shop opens at 8:00”, “born on 15 March 1990”. Its meaning is tied to a place (or to no place at all).

Two classic bugs: storing a payment time as a wall-clock value without an offset (nobody knows whose clock), and storing a future appointment as a moment (wrong once the time zone rules change, section 4).

Data Kind PostgreSQL MySQL Java
created_at, paid_at, event time Moment timestamptz DATETIME(6) in UTC (or TIMESTAMP(6) if you’re sure it’s before 2038) Instant
Birth date, check-in date Calendar date date DATE LocalDate
Opening time Time of day time TIME LocalTime
Future appointment Wall clock + place timestamp + text zone id DATETIME + VARCHAR zone id LocalDateTime + ZoneId
User’s or property’s time zone Rules text (Asia/Ho_Chi_Minh) VARCHAR(64) ZoneId

2. PostgreSQL: timestamptz and timestamp

Key idea: the name is misleading: timestamptz (timestamp with time zone) doesn’t store a time zone, it stores a UTC moment. timestamp (without time zone) stores the wall-clock value as is, and silently drops the offset.

  • timestamptz: 8 bytes, microsecond precision. On write, a value with an offset is converted to UTC; a value without one is interpreted in the session’s TimeZone (the time zone setting of the connection). On read, it’s displayed in the session’s TimeZone.
  • timestamp: 8 bytes, no conversion.
SET TimeZone = 'UTC';
CREATE TABLE ev (id int, at_tz timestamptz, at_local timestamp);
INSERT INTO ev VALUES (1, '2023-09-25T16:20:52.123456789+07:00', '2023-09-25T16:20:52+07:00');
SELECT * FROM ev;
--  at_tz                          | at_local
--  2023-09-25 09:20:52.123457+00  | 2023-09-25 16:20:52

SET TimeZone = 'Asia/Ho_Chi_Minh';
SELECT * FROM ev;
--  2023-09-25 16:20:52.123457+07  | 2023-09-25 16:20:52

Three things to notice:

  1. at_tz is the same moment, just displayed differently per session.
  2. at_local drops the +07:00 without a word; nobody knows whose 16:20 it is.
  3. The .123456789 fraction (nanoseconds) is rounded to .123457: PostgreSQL keeps microseconds only.

2.1 What the session TimeZone affects

Key idea: the session TimeZone decides which local date and time a timestamptz turns into. With PgJDBC, it is the JVM’s default time zone.

Same moment, different session, different date:

SET TimeZone = 'UTC';              SELECT '2024-06-09T20:00:00Z'::timestamptz::date;  -- 2024-06-09
SET TimeZone = 'Asia/Ho_Chi_Minh'; SELECT '2024-06-09T20:00:00Z'::timestamptz::date;  -- 2024-06-10

So the same SELECT now()::date run from the app (JVM in UTC) and from DBeaver on your laptop (JVM in Asia/Ho_Chi_Minh) returns different dates between midnight and 7 a.m.

A reminder from part 1: in PostgreSQL, AT TIME ZONE '+07:00' or SET TIME ZONE '+07:00' means UTC−7 (the POSIX convention). Use IANA zone names.

Session-dependent arithmetic: interval '1 day' and both directions of AT TIME ZONE

On a DST change day in Paris, adding “1 day” differs from adding “24 hours”, because interval '1 day' on a timestamptz follows the session’s calendar:

SET TimeZone = 'Europe/Paris';
SELECT '2024-03-30 09:00'::timestamptz + interval '1 day'    AS plus_day,  -- 2024-03-31 09:00:00+02
       '2024-03-30 09:00'::timestamptz + interval '24 hours' AS plus_24h;  -- 2024-03-31 10:00:00+02
-- With TimeZone = 'UTC' both columns give the same result

AT TIME ZONE converts in both directions, and the direction is easy to get wrong:

Expression Result Meaning
timestamptz AT TIME ZONE 'Europe/Paris' timestamp “At this moment, what does a clock in Paris show?”
timestamp AT TIME ZONE 'Europe/Paris' timestamptz “If this wall-clock value is in Paris, which moment is it?”

2.2 Migrating a legacy timestamp column to timestamptz

Key idea: say explicitly which region the old data belongs to (USING ... AT TIME ZONE), and for large tables use expand/contract instead of one ALTER that locks the whole table.

Example: the old column stores Vietnam time:

ALTER TABLE legacy
  ALTER COLUMN created_at TYPE timestamptz
  USING created_at AT TIME ZONE 'Asia/Ho_Chi_Minh';
-- 2024-06-10 08:00:00 (Vietnam time)  ->  2024-06-10 01:00:00+00
Why large tables need expand/contract

The statement above rewrites the whole table under an ACCESS EXCLUSIVE lock. For large tables, use expand/contract: add a new column, backfill it in batches, switch the app to read the new column, then drop the old one.

PostgreSQL 12+ can change timestamp to timestamptz without a rewrite, but only when the session is in UTC and there’s no USING clause, which is only correct if the old data was already UTC.

3. MySQL: TIMESTAMP and DATETIME

Key idea: TIMESTAMP stores UTC and converts per session time_zone, but runs out in 2038; DATETIME stores the value as is, with no conversion. Both default to zero fractional digits and round.

TIMESTAMP DATETIME
What it stores A UTC moment; converted from the session time_zone to UTC on write and back on read The wall-clock value as is, no conversion
Range 1970-01-01 00:00:01 to 2038-01-19 03:14:07 UTC 1000-01-01 to 9999-12-31
Fractional seconds TIMESTAMP(0..6), default 0 DATETIME(0..6), default 0
Good for Moments, if you’re sure they’re before 2038 Moments stored in UTC by convention, far-future dates, wall-clock values
SET time_zone = '+00:00';
CREATE TABLE ev (id int, ts TIMESTAMP(6) NULL, dt DATETIME(6), dt0 DATETIME, dt3 DATETIME(3));
INSERT INTO ev VALUES (1, '2023-09-25 09:20:52.123456', '2023-09-25 09:20:52.123456',
                          '2023-09-25 09:20:52.6', '2023-09-25 09:20:52.1239');
SELECT * FROM ev;
-- ts: 2023-09-25 09:20:52.123456  dt: 2023-09-25 09:20:52.123456
-- dt0: 2023-09-25 09:20:53        dt3: 2023-09-25 09:20:52.124

SET time_zone = '+07:00';
SELECT ts, dt FROM ev;
-- ts: 2023-09-25 16:20:52.123456   (converted per session)
-- dt: 2023-09-25 09:20:52.123456   (unchanged)

INSERT INTO ev (id, ts) VALUES (3, '2038-01-19 03:14:08');  -- with session +00:00
-- ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 03:14:08' for column 'ts'
Offsets in MySQL and CONVERT_TZ returning NULL
  • In MySQL, offsets in time_zone and CONVERT_TZ follow the ISO convention ('+07:00' is UTC+7), the opposite of PostgreSQL.
  • CONVERT_TZ with zone names ('Asia/Ho_Chi_Minh') only works once MySQL’s time zone tables are loaded (mysql_tzinfo_to_sql). The official Docker image has them; many other installations don’t, and the function then returns NULL without an error.

Choosing types for the other columns (strings, numbers, money), plus a TIMESTAMP vs DATETIME demo with a changed session time_zone on MySQL 8.4, is in Evolving a schema: data types.

4. Future appointments: store wall-clock time plus zone id

Key idea: store what the user meant (“9:00 in Paris”) together with the zone id, and convert to an instant only when you need it. An instant computed up front goes wrong if the time zone rules change before the appointment.

Example: an appointment at 9:00 a.m. on 1 July 2027 in Paris, stored right away as 2027-07-01T07:00:00Z under today’s rules. If France drops summer time before 2027 (the European Parliament voted for exactly that in 2019), that instant shows up as 8:00, a time the user never booked.

record Appointment(LocalDateTime localStart, ZoneId zone) {
    Instant startInstant() { return localStart.atZone(zone).toInstant(); }
}
new Appointment(LocalDateTime.of(2027, 7, 1, 9, 0), ZoneId.of("Europe/Paris")).startInstant();
// 2027-07-01T07:00:00Z  (under current tzdata; corrects itself once the JDK has new tzdata)
-- PostgreSQL
CREATE TABLE appointment (
  id          bigserial PRIMARY KEY,
  local_start timestamp   NOT NULL,   -- the wall-clock time the user chose
  time_zone   text        NOT NULL,   -- 'Europe/Paris'
  created_at  timestamptz NOT NULL DEFAULT now()
);

When you need to query by moment (a job that sends reminders an hour before), add a precomputed start_at timestamptz column for indexing, and recompute it for future appointments whenever tzdata (the IANA time zone rules database) is updated.

Recurring appointments are a step harder: store the recurrence rule (RRULE) with the local time and zone id, pre-generate occurrences for a window so “my events this week” becomes a range scan, and handle occurrences that land in a nonexistent DST hour; see Data modeling case studies.

5. JDBC, Hibernate and drivers

Key idea: make every layer speak UTC: a JVM in UTC, Hibernate jdbc.time_zone=UTC, Connector/J forcing the session to UTC. In code use Instant/OffsetDateTime and avoid java.sql.Timestamp.

JDBC 4.2 requires drivers to support LocalDate, LocalTime, LocalDateTime, OffsetTime and OffsetDateTime through setObject/getObject. Instant is not on the mandatory list. With plain JDBC on PostgreSQL, write timestamptz as an OffsetDateTime in UTC:

ps.setObject(1, instant.atOffset(ZoneOffset.UTC));               // write timestamptz
Instant read = rs.getObject("paid_at", OffsetDateTime.class).toInstant();

Avoid java.sql.Timestamp: it converts through the JVM’s default time zone, a classic source of shifted times.

Hibernate 6 / Spring Boot 3: Instant is the safest type for moments because it carries no local time.

spring:
  jpa:
    properties:
      hibernate.jdbc.time_zone: UTC     # normalise when binding/reading temporal types
  jackson:
    time-zone: UTC                       # affects java.util.Date and offset normalisation on read

MySQL Connector/J 8.0.23+: a safe setup when both app and database follow a UTC convention:

jdbc:mysql://db:3306/app?connectionTimeZone=UTC&forceConnectionTimeZoneToSession=true
Offset types in Hibernate 6 and the Connector/J properties
  • For OffsetDateTime and ZonedDateTime, Hibernate 6 has @TimeZoneStorage and the hibernate.timezone.default_storage setting to choose between normalising to UTC and keeping the offset in a separate column. The defaults have changed between versions, so look at the DDL Hibernate generates and write a write-then-read test rather than guessing.
  • Connector/J has three related properties: connectionTimeZone (formerly serverTimezone), forceConnectionTimeZoneToSession, preserveInstants.

6. JSON: ISO 8601, Jackson and JavaScript

Key idea: send moments as RFC 3339 strings in UTC and calendar dates as yyyy-MM-dd. The traps live in hand-made ObjectMappers and in JavaScript’s Date.

6.1 ISO 8601 strings or epoch numbers

Key idea: public APIs default to RFC 3339 strings because they’re readable and unambiguous; use epoch numbers only with a reason (performance, IoT), and then put the unit in the field name (createdAtMs).

Strings vs epoch numbers in detail

6.2 Jackson: real runs with 2.18

Key idea: you need JavaTimeModule and WRITE_DATES_AS_TIMESTAMPS disabled; Spring Boot does both for you, a hand-made new ObjectMapper() does neither.

public record Booking(Instant createdAt, LocalDate checkin, OffsetDateTime paidAt) {}

Booking b = new Booking(Instant.parse("2023-09-25T09:20:52.342Z"), LocalDate.of(2023, 10, 1),
                        OffsetDateTime.parse("2023-09-25T16:20:52+07:00"));
ObjectMapper setup Result
new ObjectMapper() (no module) InvalidDefinitionException: Java 8 date/time type java.time.Instant not supported by default: add Module "jackson-datatype-jsr310"
+ JavaTimeModule {"createdAt":1695633652.342000000,"checkin":[2023,10,1],"paidAt":1695633652.000000000}
+ JavaTimeModule, WRITE_DATES_AS_TIMESTAMPS disabled {"createdAt":"2023-09-25T09:20:52.342Z","checkin":"2023-10-01","paidAt":"2023-09-25T16:20:52+07:00"}

The middle row is the trap: an Instant becomes fractional seconds and a LocalDate becomes the array [2023,10,1]. It shows up when someone writes new ObjectMapper() themselves (a test, a Kafka serializer, an internal library). Jackson 3 (used by Spring Boot 4) builds java.time support into databind; even so, keep a serialisation test for your date formats.

Reading: offsets get normalised, strings without T are rejected (real output)
// A Spring Boot-like mapper reading "paidAt":"2023-09-25T16:20:52+07:00"
read.paidAt();  // 2023-09-25T09:20:52Z   (the +07:00 offset is normalised to UTC)

// With ADJUST_DATES_TO_CONTEXT_TIME_ZONE disabled, the offset is kept
read.paidAt();  // 2023-09-25T16:20:52+07:00

// A string without 'T' or an offset, for an Instant
"createdAt":"2023-09-25 09:20:52"  // InvalidFormatException

6.3 JavaScript Date on the client

Key idea: Date parses strings by two different rules and keeps only milliseconds, so the backend always sends moments with Z and calendar dates as date strings.

  • new Date("2023-09-25") (date only) is UTC midnight, so in the US it displays as the 24th. new Date("2023-09-25T09:20:52") (date and time, no offset) is the browser’s local time.
  • Send calendar dates (birth dates, check-in dates) as "2023-09-25" so the frontend treats them as dates, without going through Date.
  • Microseconds from the backend are cut off; if the frontend sends the value back as a key (a cursor, an optimistic lock), it won’t match the database. Truncate to milliseconds in the backend, or use opaque cursors instead of raw timestamps.

A suggested API convention (put it in your API docs, see Designing REST APIs with Spring Boot):

Kind JSON format Example
Moment RFC 3339, UTC, milliseconds "2023-09-25T09:20:52.342Z"
Calendar date yyyy-MM-dd "2023-10-01"
Wall-clock time + region Two fields "localStart": "2027-07-01T09:00:00", "timeZone": "Europe/Paris"
Duration ISO 8601 duration or a number with a unit "PT30M" or "timeoutSeconds": 1800

Exercises

Exercise 1: Choose column types

Task (paraphrased): Choose PostgreSQL and MySQL column types for: (a) when an order was created, (b) a customer’s birth date, (c) a doctor’s appointment next year in Singapore, (d) a branch’s daily opening time, (e) the end date of a 30-year contract.

Solution to Exercise 1
Field PostgreSQL MySQL Why
(a) created_at timestamptz DATETIME(6) in UTC A moment
(b) birth_date date DATE A calendar date, not a moment (remember Asia/Ho_Chi_Minh was +08:00 in 1970)
(c) appointment timestamp + time_zone text (+ a precomputed start_at timestamptz if you need an index) DATETIME + VARCHAR(64) A future wall-clock value; preserve the user’s intent
(d) opening time time + the branch’s time_zone TIME + VARCHAR(64) Repeats daily, in local time
(e) end date date (or timestamptz if the contract specifies an exact time) DATE / DATETIME(6) Beyond 2038, so not MySQL TIMESTAMP

Exercise 2: A save-and-reload test that fails now and then

Task (paraphrased): An integration test saves an entity with Instant createdAt = Instant.now() to MySQL, reads it back and calls assertEquals. It’s green on one person’s machine and randomly red on CI. Find the cause and fix it.

Solution to Exercise 2

The column is DATETIME (no fractional digits) or DATETIME(3), while Instant.now() has microseconds; MySQL rounds, so the value read back differs (sometimes it’s even the next second). “Now and then” because it depends on the random fraction.

Fix both sides: declare the column as DATETIME(6) (or deliberately accept millisecond precision) and truncate in code before saving; in tests use Clock.fixed so values are deterministic:

Instant createdAt = Instant.now(clock).truncatedTo(ChronoUnit.MICROS); // matches DATETIME(6) and timestamptz

A real simulation: 2024-06-10T08:00:00.123789Z into a 3-digit column becomes .124, so equals is false; truncatedTo(MILLIS) gives a stable .123.

Exercise 3: Migrate MySQL data stored in Vietnam time

Task (paraphrased): The orders.created_at DATETIME column stores Vietnam time. The team decides to normalise to UTC. Write the plan.

Solution to Exercise 3
  1. Add a new column created_at_utc DATETIME(6) NULL.
  2. Backfill in batches by primary key (avoid one huge UPDATE that locks the table and bloats the undo log):
UPDATE orders
SET created_at_utc = CONVERT_TZ(created_at, '+07:00', '+00:00')
WHERE id BETWEEN ? AND ? AND created_at_utc IS NULL;

Vietnam has no DST, so the fixed +07:00 offset is safe here; for zones with DST use zone names (which need the time zone tables loaded) or do it in Java with ZoneId. Watch out for data from before mid-1975 (if any) in the south, which was +08:00. 3. The app writes both columns (dual write), then switches to reading the new one; compare report numbers before and after. 4. Drop the old column and rename the new one. Set Connector/J connectionTimeZone=UTC and run the JVM in UTC so new data doesn’t regress.

Remember

  • Explain why timestamptz doesn’t store a time zone, and what a timestamp column loses.
  • Know when to use MySQL TIMESTAMP, when DATETIME(6) in UTC, and which driver settings go with it.
  • Explain why a save-and-reload test on an Instant fails at random, and where to fix it.
  • Design an appointments table that survives time zone rule changes.
  • Know which two Jackson settings break date JSON, and why JavaScript Date needs strings with Z.

Interview questions

Junior

Does PostgreSQL’s timestamptz store a time zone?

No. It stores a UTC moment (microseconds); to remember the original zone you store the zone id in a separate column.

On write, a value with an offset is converted to UTC and a value without one is interpreted in the session TimeZone; on read, it’s displayed in the session TimeZone.

Which column type should created_at use?

PostgreSQL timestamptz; MySQL DATETIME(6) with a UTC convention enforced in the driver; Java Instant.

MySQL TIMESTAMP(6) also works if the 2038 limit is acceptable. Not timestamp without time zone, and not DATETIME without a clear UTC convention, because nobody can tell whose clock the value is.

Which format should an API use for dates and times?

Moments: RFC 3339 strings with an offset, usually UTC ("2023-09-25T09:20:52.342Z"). Calendar dates: "2023-10-01".

Not display formats like 25/09/2023 or Date#toString. Displaying in the user’s local time is the frontend’s job.

Mid

How do MySQL TIMESTAMP and DATETIME differ?

TIMESTAMP stores UTC and converts per session time_zone, only up to 2038; DATETIME stores the value as is, from the year 1000 to 9999.

The TIMESTAMP range is 1970 to 2038-01-19 03:14:07 UTC. Both default to zero fractional digits and round the fraction. For data that may go past 2038, or when you want no implicit conversion, use DATETIME(6) with a UTC convention.

Why is WHERE created_at::date = '2024-06-10' a poor filter?

Because the “date” depends on the session TimeZone, and wrapping the column in an expression stops the index from being used.

PgJDBC sets the session from the JVM’s zone, so the same query returns different results depending on who runs it. Write a half-open range with explicit offsets: created_at >= '2024-06-10T00:00+07:00' AND created_at < '2024-06-11T00:00+07:00'.

A save-and-reload test on an Instant sometimes isn’t equal. Why?

The database rounds fractional seconds, so the value read back differs from the one written.

Instant.now() may have micro- or nanoseconds; PostgreSQL keeps microseconds (rounding the nanos), and MySQL keeps whatever the column precision is (default 0) and rounds. Fix: declare column precision explicitly, truncatedTo in code before writing, and use Clock.fixed in tests.

Spring Boot returns a LocalDate as [2023,10,1]. What happened?

The ObjectMapper in use has JavaTimeModule but WRITE_DATES_AS_TIMESTAMPS still enabled, usually because someone built their own mapper instead of Boot’s bean.

Typical causes: new ObjectMapper(), or overriding Boot’s bean without the builder; Boot disables that feature by default. Fix: use the Spring-managed ObjectMapper (or Jackson2ObjectMapperBuilder), or disable SerializationFeature.WRITE_DATES_AS_TIMESTAMPS; add a serialisation test for DTOs with dates.

Senior

Design storage for future appointments in a multi-country booking app.

The source of truth is the wall-clock time the user chose plus an IANA zone id, because time zone rules may change before the appointment; the instant is only a precomputed column.

  • PostgreSQL: timestamp + text. Add start_at timestamptz for indexing, reminder scheduling and time-range queries; a job recomputes it for future appointments after every tzdata update.
  • Validate zone ids on input.
  • Times in DST gaps or overlaps: an explicit policy (shift past the gap, pick the earlier offset in an overlap), and tell the user.
  • The API returns both the local time with zone and the instant.
You find a MySQL system whose DATETIME columns mix UTC and Vietnam time. What do you do?

First stop the bleeding by forcing every layer to UTC, then classify the old data and migrate with expand/contract.

  • Force the JVM and Connector/J to UTC (connectionTimeZone=UTC, forceConnectionTimeZoneToSession=true), and record when that was deployed.
  • Classify existing data by writer and by time period (which service and version wrote which zone), using reference data such as logs with instants or time-based IDs to determine the offset.
  • Migrate: a new DATETIME(6) UTC column, a batched backfill with CONVERT_TZ, dual writes, report reconciliation, then drop the old column.
  • Write an ADR for the convention and add write-then-read integration tests.
Why can Jackson lose information when reading an OffsetDateTime, and how do you decide what to do?

ADJUST_DATES_TO_CONTEXT_TIME_ZONE is on by default, so the offset is normalised to UTC: the moment is right but the original offset is gone. Decide by business need.

If you only need the moment, use Instant in the DTO to make that explicit. If the user’s offset or region matters (invoices printed in local time, behaviour analysis by hour of day), disable the feature or receive the zone id as a separate field and store it separately, since timestamptz doesn’t keep the offset either.

Useful phrases for interviews

  • “Despite the name, timestamptz doesn’t store a time zone; it stores a UTC instant and renders it in the session’s zone.”
  • “For any _at column, my default is timestamptz in Postgres and DATETIME(6) in UTC in MySQL.”
  • “MySQL TIMESTAMP stops at 2038, so I wouldn’t use it for contract or loan dates.”
  • “We truncate to microseconds before saving so the round-trip test is deterministic.”
  • “A future appointment is the user’s wall-clock time plus a zone id; the instant is derived from it.”
  • “Casting the column to a date makes the predicate non-sargable, so I’d use a half-open range instead.”
  • “Our API sends RFC 3339 strings in UTC for instants and plain yyyy-MM-dd for calendar dates.”
  • “If someone news up their own ObjectMapper, dates come out as arrays, so we test serialisation explicitly.”
  • “Jackson normalises offsets to UTC by default; if the offset matters, we keep the zone as a separate field.”

Notes based on the Advanced Backend course (Ronin Engineer), rewritten in my own words.