Đến nội dung chính
notes.vinhnq.com

Tìm kiếm

Database & ORMMid24 phút đọc

Lưu ngày giờ trong PostgreSQL, MySQL và JSON: timestamptz, DATETIME, Jackson và các bẫy

Ôn 18 thẻ
Trong bài này

Cụm bài Ngày giờ trong backend: 1. Epoch, timezone và java.time · 2. Lưu trong DB và JSON

Code Java đúng vẫn có thể ghi ra dữ liệu sai, vì mỗi tầng (DB, driver, ORM, JSON, frontend) hiểu thời gian theo cách riêng. Đọc xong, bạn chọn đúng kiểu cột trong PostgreSQL và MySQL, cấu hình JDBC/Hibernate/Jackson, và tránh các bẫy khi trao đổi qua JSON. Nên đọc trước phần 1, Epoch, timezone và java.time: bài này giả định bạn biết Instant là một thời điểm toàn cầu còn LocalDateTime là giờ đồng hồ không thuộc vùng nào.

Sơ đồ tóm tắt:

flowchart TD
  A{"Dữ liệu là gì?"} -->|"Thời điểm"| B["Lưu ở UTC"]
  A -->|"Lịch hẹn tương lai"| C["Giờ đồng hồ + zone id"]
  B --> D["PostgreSQL: timestamptz"]
  B --> E["MySQL: DATETIME(6) ở UTC"]
  C --> F["Instant: cột tính sẵn"]
  D --> G["JVM, Hibernate, driver đều UTC"]
  E --> G
  G --> H["JSON: RFC 3339 có Z"]

Output SQL và Java trong bài là output thật (PostgreSQL 17, MySQL 8.4, JDK 21, Jackson 2.18).

1. Timestamp và datetime là hai loại dữ liệu khác nhau

Ý chính: timestamp là một điểm trên trục thời gian, giống nhau cho cả thế giới; datetime là giờ trên lịch và đồng hồ, gắn với một nơi. Slide gốc coi đây là phân biệt quan trọng nhất.

  • Timestamp (thời điểm): created_at, paid_at, lúc gửi email. Đổi timezone hiển thị thì giờ hiện ra đổi, thời điểm không đổi.
  • Datetime (giờ đồng hồ): “họp 9:00 ngày 01/07/2027 ở Paris”, “cửa hàng mở 8:00”, “ngày sinh 15/03/1990”. Ý nghĩa gắn với một nơi (hoặc không nơi nào).

Hai bug điển hình: lưu thời điểm thanh toán thành giờ không offset (không biết giờ của ai), và lưu lịch hẹn tương lai thành thời điểm (sai khi luật timezone đổi, mục 4).

Dữ liệu Loại PostgreSQL MySQL Java
created_at, paid_at, event time Thời điểm timestamptz DATETIME(6) ở UTC (hoặc TIMESTAMP(6) nếu chắc chắn trước 2038) Instant
Ngày sinh, ngày check-in Ngày lịch date DATE LocalDate
Giờ mở cửa Giờ trong ngày time TIME LocalTime
Lịch hẹn tương lai Giờ đồng hồ + nơi timestamp + text zone id DATETIME + VARCHAR zone id LocalDateTime + ZoneId
Timezone của user, tài sản Luật text (Asia/Ho_Chi_Minh) VARCHAR(64) ZoneId

2. PostgreSQL: timestamptz và timestamp

Ý chính: tên gây hiểu nhầm: timestamptz (timestamp with time zone) không lưu timezone mà lưu một thời điểm UTC. timestamp (without time zone) lưu nguyên giờ đồng hồ, và lặng lẽ bỏ offset.

  • timestamptz: 8 byte, độ chính xác micro giây. Khi ghi, giá trị có offset được đổi về UTC; giá trị không offset được hiểu theo TimeZone của session (cấu hình timezone của phiên kết nối). Khi đọc, hiển thị theo TimeZone của session.
  • timestamp: 8 byte, không đổi gì.
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

Ba điều cần thấy:

  1. at_tz là cùng một thời điểm, chỉ hiển thị khác theo session.
  2. at_local bỏ +07:00 không báo gì; không ai biết 16:20 là giờ ở đâu.
  3. .123456789 (nano giây) bị làm tròn thành .123457: PostgreSQL chỉ giữ micro giây.

2.1 Session TimeZone ảnh hưởng gì

Ý chính: TimeZone của session quyết định timestamptz đổi thành ngày giờ địa phương nào. Với PgJDBC, nó chính là timezone mặc định của JVM.

Cùng một thời điểm, khác session, khác ngày:

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

Vì vậy SELECT now()::date chạy từ app (JVM UTC) và từ DBeaver trên laptop (JVM Asia/Ho_Chi_Minh) cho hai ngày khác nhau trong khoảng 0–7 giờ sáng.

Nhắc lại từ phần 1: trong PostgreSQL, AT TIME ZONE '+07:00' hay SET TIME ZONE '+07:00' là UTC−7 (quy ước POSIX). Dùng tên zone IANA.

Phép tính theo session: interval '1 day' và hai chiều của AT TIME ZONE

Vào ngày đổi giờ ở Paris, cộng “1 ngày” khác cộng “24 giờ”, vì interval '1 day' trên timestamptz theo lịch của session:

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
-- Với TimeZone = 'UTC' hai cột cho cùng kết quả

AT TIME ZONE đổi qua lại giữa hai kiểu, và chiều của nó hay bị nhầm:

Biểu thức Kết quả Ý nghĩa
timestamptz AT TIME ZONE 'Europe/Paris' timestamp “Thời điểm này, đồng hồ ở Paris chỉ mấy giờ?”
timestamp AT TIME ZONE 'Europe/Paris' timestamptz “Giờ đồng hồ này, nếu ở Paris, là thời điểm nào?”

2.2 Chuyển cột timestamp cũ sang timestamptz

Ý chính: phải nói rõ dữ liệu cũ là giờ của vùng nào (USING ... AT TIME ZONE), và với bảng lớn thì làm kiểu expand/contract thay vì một ALTER khoá cả bảng.

Ví dụ cột cũ lưu giờ Việt Nam:

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 (giờ VN)  ->  2024-06-10 01:00:00+00
Vì sao bảng lớn cần expand/contract

Lệnh trên viết lại cả bảng dưới khoá ACCESS EXCLUSIVE. Với bảng lớn, làm expand/contract (mở rộng rồi thu gọn): thêm cột mới, backfill theo batch, chuyển app sang đọc cột mới, rồi bỏ cột cũ.

PostgreSQL 12+ có thể đổi timestamp sang timestamptz không viết lại bảng, nhưng chỉ khi session là UTC và không có USING, tức là chỉ đúng nếu dữ liệu cũ vốn đã là UTC.

3. MySQL: TIMESTAMP và DATETIME

Ý chính: TIMESTAMP lưu UTC và tự đổi theo time_zone của session, nhưng hết phạm vi năm 2038; DATETIME lưu nguyên giá trị, không đổi gì. Cả hai mặc định 0 chữ số lẻ và làm tròn.

TIMESTAMP DATETIME
Lưu gì Thời điểm UTC; ghi thì đổi từ time_zone của session sang UTC, đọc thì đổi ngược lại Nguyên giờ đồng hồ, không đổi gì
Phạm vi 1970-01-01 00:00:01 đến 2038-01-19 03:14:07 UTC 1000-01-01 đến 9999-12-31
Phần lẻ của giây TIMESTAMP(0..6), mặc định 0 DATETIME(0..6), mặc định 0
Hợp với Thời điểm, nếu chắc chắn trước 2038 Thời điểm lưu ở UTC theo quy ước, ngày giờ tương lai xa, giờ đồng hồ
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   (đổi theo session)
-- dt: 2023-09-25 09:20:52.123456   (giữ nguyên)

INSERT INTO ev (id, ts) VALUES (3, '2038-01-19 03:14:08');  -- với session +00:00
-- ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 03:14:08' for column 'ts'
Offset trong MySQL và CONVERT_TZ trả về NULL
  • Ở MySQL, offset trong time_zone và CONVERT_TZ theo quy ước ISO ('+07:00' là UTC+7), ngược với PostgreSQL.
  • CONVERT_TZ với tên zone ('Asia/Ho_Chi_Minh') chỉ chạy khi bảng timezone của MySQL đã được nạp (mysql_tzinfo_to_sql). Image Docker chính thức đã nạp sẵn; nhiều bản cài khác thì không, và hàm trả về NULL mà không báo lỗi.

Chọn kiểu cho các cột khác (chuỗi, số, tiền) và một demo TIMESTAMP với DATETIME khi đổi time_zone của session trên MySQL 8.4 có ở Schema thay đổi theo thời gian: kiểu dữ liệu.

4. Lịch hẹn tương lai: lưu giờ đồng hồ + zone id

Ý chính: lưu đúng ý định của người dùng (“9:00 ở Paris”) cùng zone id, rồi mới quy đổi ra Instant khi cần. Instant tính trước sẽ sai nếu luật timezone đổi trước ngày hẹn.

Ví dụ: hẹn 9:00 sáng 01/07/2027 ở Paris, lưu ngay thành 2027-07-01T07:00:00Z theo luật hiện tại. Nếu trước 2027 Pháp bỏ giờ mùa hè (EU đã bỏ phiếu về việc này năm 2019), Instant đó hiện thành 8:00, giờ người dùng không hẹn.

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  (theo tzdata hiện tại; tự đúng lại khi JDK có tzdata mới)
-- PostgreSQL
CREATE TABLE appointment (
  id          bigserial PRIMARY KEY,
  local_start timestamp   NOT NULL,   -- giờ đồng hồ người dùng chọn
  time_zone   text        NOT NULL,   -- 'Europe/Paris'
  created_at  timestamptz NOT NULL DEFAULT now()
);

Cần truy vấn theo thời điểm (job nhắc 1 giờ trước) thì thêm cột start_at timestamptz tính sẵn để có index, và tính lại nó cho lịch tương lai mỗi khi cập nhật tzdata (bộ luật timezone IANA).

Lịch hẹn lặp lại khó hơn một bậc: lưu quy tắc lặp (RRULE) cùng giờ địa phương và zone id, sinh sẵn các lần xuất hiện trong một cửa sổ để truy vấn “lịch tuần này” thành range scan, và xử lý các lần rơi vào giờ DST không tồn tại; xem Case study data modeling.

5. JDBC, Hibernate và driver

Ý chính: cho mọi tầng nói cùng một ngôn ngữ là UTC: JVM UTC, Hibernate jdbc.time_zone=UTC, Connector/J ép session về UTC. Trong code dùng Instant/OffsetDateTime, tránh java.sql.Timestamp.

JDBC 4.2 bắt buộc driver hỗ trợ LocalDate, LocalTime, LocalDateTime, OffsetTime, OffsetDateTime qua setObject/getObject. Instant không nằm trong danh sách bắt buộc. Với JDBC thuần trên PostgreSQL, ghi timestamptz bằng OffsetDateTime ở UTC:

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

Đừng dùng java.sql.Timestamp: nó đổi qua timezone mặc định của JVM, nguồn lệch giờ kinh điển.

Hibernate 6 / Spring Boot 3: Instant an toàn nhất cho thời điểm vì không mang giờ địa phương.

spring:
  jpa:
    properties:
      hibernate.jdbc.time_zone: UTC     # chuẩn hoá khi bind/đọc kiểu thời gian
  jackson:
    time-zone: UTC                       # ảnh hưởng java.util.Date và việc chuẩn hoá offset khi đọc

MySQL Connector/J 8.0.23+: cấu hình an toàn khi app và DB đều quy ước UTC:

jdbc:mysql://db:3306/app?connectionTimeZone=UTC&forceConnectionTimeZoneToSession=true
Kiểu có offset trong Hibernate 6 và thuộc tính Connector/J
  • Với OffsetDateTime, ZonedDateTime, Hibernate 6 có @TimeZoneStorage và thiết lập hibernate.timezone.default_storage để chọn chuẩn hoá về UTC hay lưu offset ở cột riêng. Mặc định đã thay đổi qua các phiên bản, nên hãy xem DDL Hibernate sinh ra và viết một test ghi rồi đọc lại thay vì đoán.
  • Connector/J có ba thuộc tính liên quan: connectionTimeZone (tên cũ serverTimezone), forceConnectionTimeZoneToSession, preserveInstants.

6. JSON: ISO 8601, Jackson và JavaScript

Ý chính: gửi thời điểm dạng chuỗi RFC 3339 ở UTC, ngày lịch dạng yyyy-MM-dd. Bẫy nằm ở ObjectMapper tự tạo và ở Date của JavaScript.

6.1 Chuỗi ISO 8601 hay số epoch

Ý chính: API công khai mặc định dùng chuỗi RFC 3339 vì đọc được và không mơ hồ; chỉ dùng số epoch khi có lý do (hiệu năng, IoT), và khi đó ghi đơn vị vào tên field (createdAtMs).

So sánh chi tiết chuỗi và số epoch

6.2 Jackson: chạy thật với 2.18

Ý chính: cần JavaTimeModule và tắt WRITE_DATES_AS_TIMESTAMPS; Spring Boot làm sẵn cả hai, nhưng new ObjectMapper() tự tạo thì không.

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"));
Cấu hình ObjectMapper Kết quả
new ObjectMapper() (không 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, tắt WRITE_DATES_AS_TIMESTAMPS {"createdAt":"2023-09-25T09:20:52.342Z","checkin":"2023-10-01","paidAt":"2023-09-25T16:20:52+07:00"}

Dòng giữa là cái bẫy: Instant thành số giây có phần thập phân, LocalDate thành mảng [2023,10,1]. Nó xuất hiện khi ai đó tự new ObjectMapper() (test, Kafka serializer, thư viện nội bộ). Jackson 3 (đi cùng Spring Boot 4) tích hợp sẵn java.time vào databind; dù vậy vẫn nên có test serialize cho định dạng ngày giờ.

Chiều đọc: offset bị đổi, chuỗi thiếu T bị từ chối (output thật)
// Mapper giống Spring Boot: đọc "paidAt":"2023-09-25T16:20:52+07:00"
read.paidAt();  // 2023-09-25T09:20:52Z   (offset +07:00 bị đổi về UTC)

// Tắt ADJUST_DATES_TO_CONTEXT_TIME_ZONE thì giữ offset
read.paidAt();  // 2023-09-25T16:20:52+07:00

// Chuỗi thiếu 'T' và offset cho Instant
"createdAt":"2023-09-25 09:20:52"  // InvalidFormatException

6.3 JavaScript Date ở phía client

Ý chính: Date hiểu chuỗi theo hai quy tắc khác nhau và chỉ giữ mili giây, nên backend luôn gửi thời điểm có Z và ngày lịch dạng chuỗi ngày.

  • new Date("2023-09-25") (chỉ ngày) là UTC nửa đêm, ở Mỹ hiển thị thành ngày 24. new Date("2023-09-25T09:20:52") (có giờ, không offset) lại là giờ địa phương của trình duyệt.
  • Ngày lịch (ngày sinh, check-in) gửi dạng "2023-09-25" để frontend xử lý như ngày, không qua Date.
  • Micro giây từ backend bị cắt; frontend gửi lại làm khoá (cursor, optimistic lock) sẽ không khớp DB. Cắt về mili giây ở backend, hoặc dùng cursor mờ (opaque) thay vì timestamp thô.

Quy ước API gợi ý (đưa vào tài liệu API, xem Thiết kế REST API với Spring Boot):

Loại Định dạng JSON Ví dụ
Thời điểm RFC 3339, UTC, mili giây "2023-09-25T09:20:52.342Z"
Ngày lịch yyyy-MM-dd "2023-10-01"
Giờ đồng hồ + vùng Hai field "localStart": "2027-07-01T09:00:00", "timeZone": "Europe/Paris"
Thời lượng ISO 8601 duration hoặc số có đơn vị "PT30M" hoặc "timeoutSeconds": 1800

Bài tập

Bài 1: Chọn kiểu cột

Đề (diễn giải): Chọn kiểu cột PostgreSQL và MySQL cho: (a) thời điểm tạo đơn, (b) ngày sinh khách, (c) lịch khám bác sĩ năm sau ở Singapore, (d) giờ mở cửa hằng ngày của chi nhánh, (e) ngày hết hạn hợp đồng 30 năm.

Lời giải Bài 1
Trường PostgreSQL MySQL Lý do
(a) created_at timestamptz DATETIME(6) UTC Thời điểm
(b) birth_date date DATE Ngày lịch, không phải thời điểm (nhớ ví dụ Asia/Ho_Chi_Minh từng là +08:00 năm 1970)
(c) lịch khám timestamp + time_zone text (+ start_at timestamptz tính sẵn nếu cần index) DATETIME + VARCHAR(64) Giờ đồng hồ tương lai, giữ ý định của người dùng
(d) giờ mở cửa time + time_zone của chi nhánh TIME + VARCHAR(64) Lặp lại mỗi ngày, theo giờ địa phương
(e) ngày hết hạn date (hoặc timestamptz nếu hợp đồng quy định giờ chính xác) DATE / DATETIME(6) Vượt 2038, nên không dùng MySQL TIMESTAMP

Bài 2: Test “lưu rồi đọc lại” thỉnh thoảng thất bại

Đề (diễn giải): Một integration test lưu entity có Instant createdAt = Instant.now() vào MySQL rồi đọc lại và assertEquals. Test chạy xanh trên máy một người, đỏ ngẫu nhiên trên CI. Tìm nguyên nhân và sửa.

Lời giải Bài 2

Cột là DATETIME (0 chữ số lẻ) hoặc DATETIME(3), còn Instant.now() có micro giây; MySQL làm tròn nên giá trị đọc lại khác (có khi sang giây sau). “Thỉnh thoảng” vì phụ thuộc phần lẻ ngẫu nhiên.

Sửa ở cả hai phía: khai báo cột DATETIME(6) (hoặc chấp nhận độ chính xác mili giây có chủ đích) và cắt trong code trước khi lưu; trong test dùng Clock.fixed để giá trị xác định:

Instant createdAt = Instant.now(clock).truncatedTo(ChronoUnit.MICROS); // khớp DATETIME(6) và timestamptz

Mô phỏng chạy thật: 2024-06-10T08:00:00.123789Z vào cột 3 chữ số lẻ thành .124, equals trả về false; truncatedTo(MILLIS) cho .123 ổn định.

Bài 3: Di chuyển dữ liệu MySQL đã lưu theo giờ Việt Nam

Đề (diễn giải): Bảng orders.created_at DATETIME đang lưu giờ Việt Nam. Team quyết định chuẩn hoá về UTC. Viết kế hoạch.

Lời giải Bài 3
  1. Thêm cột mới created_at_utc DATETIME(6) NULL.
  2. Backfill theo batch theo khoá chính (tránh một UPDATE khổng lồ khoá bảng và làm phình 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;

Việt Nam không có DST nên offset cố định +07:00 an toàn ở đây; với vùng có DST phải dùng tên zone (cần bảng timezone đã nạp) hoặc làm trong Java bằng ZoneId. Lưu ý dữ liệu trước giữa năm 1975 (nếu có) ở miền Nam là +08:00. 3. App ghi cả hai cột (dual write), rồi chuyển sang đọc cột mới; so sánh số liệu báo cáo trước/sau. 4. Bỏ cột cũ, đổi tên cột mới. Cập nhật Connector/J connectionTimeZone=UTC và JVM UTC để dữ liệu mới không tái phạm.

Ghi nhớ

  • Giải thích được vì sao timestamptz không lưu timezone, và cột timestamp làm mất gì.
  • Biết khi nào dùng MySQL TIMESTAMP, khi nào DATETIME(6) UTC, và cấu hình driver nào đi kèm.
  • Giải thích được vì sao test “lưu rồi đọc lại” Instant fail ngẫu nhiên và sửa ở đâu.
  • Thiết kế được bảng lịch hẹn tương lai chịu được việc luật timezone thay đổi.
  • Biết hai cấu hình Jackson nào làm JSON ngày giờ sai, và vì sao JavaScript Date cần chuỗi có Z.

Câu hỏi phỏng vấn

Junior

timestamptz của PostgreSQL có lưu timezone không?

Không. Nó lưu một thời điểm UTC (micro giây); muốn nhớ timezone gốc phải lưu zone id ở cột riêng.

Khi ghi, giá trị có offset được đổi về UTC, giá trị không offset được hiểu theo TimeZone của session; khi đọc, hiển thị theo TimeZone của session.

Nên dùng kiểu cột nào cho created_at?

PostgreSQL timestamptz; MySQL DATETIME(6) với quy ước UTC ép ở driver; Java Instant.

MySQL TIMESTAMP(6) cũng được nếu chấp nhận giới hạn 2038. Không dùng timestamp without time zone hay DATETIME mà không có quy ước UTC rõ ràng, vì không ai biết giá trị là giờ của vùng nào.

API nên trả ngày giờ ở định dạng nào?

Thời điểm: chuỗi RFC 3339 có offset, thường UTC ("2023-09-25T09:20:52.342Z"). Ngày lịch: "2023-10-01".

Không trả định dạng hiển thị như 25/09/2023 hay Date#toString. Frontend chịu trách nhiệm hiển thị theo giờ địa phương của người dùng.

Mid

MySQL TIMESTAMP khác DATETIME thế nào?

TIMESTAMP lưu UTC và tự đổi theo time_zone của session, chỉ tới 2038; DATETIME lưu nguyên giá trị, từ năm 1000 đến 9999.

Phạm vi TIMESTAMP là 1970 đến 2038-01-19 03:14:07 UTC. Cả hai mặc định 0 chữ số lẻ và làm tròn phần lẻ. Dữ liệu có thể vượt 2038 hoặc muốn không có chuyển đổi ngầm thì dùng DATETIME(6) với quy ước UTC.

Vì sao WHERE created_at::date = '2024-06-10' là cách lọc tồi?

Vì “ngày” phụ thuộc TimeZone của session, và biểu thức bọc cột làm index không dùng được.

PgJDBC đặt session theo timezone của JVM, nên cùng một query ra kết quả khác nhau tuỳ ai chạy. Viết thành khoảng nửa mở với offset tường minh: created_at >= '2024-06-10T00:00+07:00' AND created_at < '2024-06-11T00:00+07:00'.

Test lưu rồi đọc lại Instant thỉnh thoảng không bằng nhau. Vì sao?

DB làm tròn phần lẻ của giây, nên giá trị đọc lại khác giá trị đã ghi.

Instant.now() có thể có micro hoặc nano giây; PostgreSQL giữ micro giây (làm tròn phần nano), MySQL giữ theo độ chính xác của cột (mặc định 0) và làm tròn. Sửa: khai báo độ chính xác cột rõ ràng, truncatedTo trong code trước khi ghi, dùng Clock.fixed trong test.

Spring Boot trả LocalDate thành [2023,10,1]. Chuyện gì xảy ra?

ObjectMapper đang dùng có JavaTimeModule nhưng WRITE_DATES_AS_TIMESTAMPS vẫn bật, thường vì ai đó tự tạo mapper thay cho bean của Boot.

Nguyên nhân hay gặp: new ObjectMapper() hoặc ghi đè bean của Boot mà không dùng builder; Boot mặc định tắt tính năng này. Sửa: dùng ObjectMapper do Spring quản lý (hoặc Jackson2ObjectMapperBuilder), hoặc tắt SerializationFeature.WRITE_DATES_AS_TIMESTAMPS; thêm test serialize cho DTO có ngày giờ.

Senior

Thiết kế lưu lịch hẹn tương lai cho một ứng dụng đặt lịch đa quốc gia.

Nguồn sự thật là giờ đồng hồ người dùng chọn + zone id IANA, vì luật timezone có thể đổi trước ngày hẹn; Instant chỉ là cột tính sẵn.

  • PostgreSQL: timestamp + text. Thêm start_at timestamptz để index, lên lịch nhắc và truy vấn khoảng thời gian; job tính lại cột đó cho lịch tương lai sau mỗi lần cập nhật tzdata.
  • Validate zone id khi nhận.
  • Giờ rơi vào gap/overlap DST: chính sách rõ ràng (dời sau gap, chọn offset sớm hơn ở overlap) và báo cho người dùng.
  • API trả cả giờ địa phương + zone và Instant.
Bạn phát hiện hệ thống MySQL lưu DATETIME lẫn lộn UTC và giờ Việt Nam. Xử lý thế nào?

Trước hết dừng chảy máu bằng cách ép mọi tầng về UTC, rồi phân loại dữ liệu cũ và migrate theo expand/contract.

  • Ép JVM và Connector/J về UTC (connectionTimeZone=UTC, forceConnectionTimeZoneToSession=true), ghi lại thời điểm triển khai.
  • Phân loại dữ liệu cũ theo nguồn ghi và mốc thời gian (service nào, phiên bản nào ghi giờ gì), đối chiếu với log có Instant hoặc ID dạng thời gian để xác định offset.
  • Migrate: cột mới DATETIME(6) UTC, backfill theo batch với CONVERT_TZ, dual write, đối soát báo cáo, rồi bỏ cột cũ.
  • Ghi ADR về quy ước và thêm test tích hợp ghi rồi đọc lại.
Vì sao Jackson có thể làm mất thông tin khi đọc OffsetDateTime, và bạn quyết định thế nào?

ADJUST_DATES_TO_CONTEXT_TIME_ZONE bật mặc định nên offset bị chuẩn hoá về UTC: thời điểm đúng nhưng offset gốc mất. Quyết định theo nghiệp vụ.

Chỉ cần thời điểm thì dùng Instant trong DTO cho rõ ý. Nếu offset hay vùng của người dùng có ý nghĩa (hoá đơn in theo giờ địa phương, phân tích hành vi theo giờ), tắt tính năng đó hoặc nhận zone id thành field riêng và lưu riêng, vì timestamptz cũng không giữ offset.

Từ vựng tiếng Anh

Term Phát âm Nghĩa Ví dụ câu tiếng Anh dùng trong phỏng vấn
point in time /pɔɪnt ɪn taɪm/ thời điểm “A payment is a point in time, so we store it as timestamptz.”
wall-clock time /wɔːl klɑːk taɪm/ giờ đồng hồ địa phương “A future appointment is a wall-clock time plus a zone id.”
session time zone /ˈseʃ.ən taɪm zoʊn/ timezone của phiên kết nối “The session time zone changes how timestamptz is rendered.”
fractional seconds /ˈfræk.ʃən.əl ˈsek.əndz/ phần lẻ của giây “MySQL rounds fractional seconds unless you configure it to truncate.”
round-trip /ˈraʊnd.trɪp/ ghi rồi đọc lại “Every date type gets a round-trip test against the real database.”
sargable /ˈsɑːr.ɡə.bəl/ dùng được index “Casting the column to date makes the predicate non-sargable.”
backfill /ˈbæk.fɪl/ điền dữ liệu cho cột mới “We backfill the UTC column in batches of ten thousand rows.”
expand and contract /ɪkˈspænd ənd kənˈtrækt/ thêm mới, chuyển dần, rồi bỏ cũ “The migration follows expand and contract to avoid locking the table.”
source of truth /sɔːrs əv truːθ/ nguồn sự thật “The local time and zone are the source of truth; the instant is derived.”

Ghi chép dựa trên khoá Advanced Backend (Ronin Engineer), viết lại theo cách hiểu của mình.