Ngày tháng và múi giờ

Bạn sẽ học được

• Phân biệt date, timestamp và timestamptz.
• Chuyển đổi múi giờ và cạm bẫy khi báo cáo.
• Viết khoảng ngày nửa mở để không đếm trùng.
• Xử lý ngày sai định dạng và ngày không tồn tại.
• Tính chênh lệch thời gian đúng đơn vị.

Khái niệm cốt lõi

Ba kiểu thời gian, ba mục đích:

Mặc định nên dùng timestamptz cho sự kiện. Chỉ dùng date khi khái niệm nghiệp vụ thật sự là "ngày", không phải "thời điểm".

Chuyển múi giờ

Việt Nam là UTC+7 và không có giờ mùa hè, nên chuyển đổi đơn giản. Với timestamptz:

SELECT
  created_at,
  created_at AT TIME ZONE 'Asia/Ho_Chi_Minh' AS gio_vn
FROM orders;

Với date hoặc timestamp không múi giờ, AT TIME ZONE diễn giải theo chiều ngược lại (gắn múi giờ vào giá trị). Cần kiểm tra kỹ khi trộn hai kiểu.

Lỗi phổ biến: máy chủ ghi UTC, dashboard hiển thị "hôm nay" theo UTC. Từ 0h đến 7h giờ Việt Nam, dashboard đang hiển thị dữ liệu của hôm qua. Cách sửa: chốt múi giờ báo cáo và chuyển đổi tường minh.

-- "Hôm nay" theo giờ Việt Nam
WHERE created_at >= (CURRENT_DATE AT TIME ZONE 'Asia/Ho_Chi_Minh')
  AND created_at <  (CURRENT_DATE AT TIME ZONE 'Asia/Ho_Chi_Minh') + INTERVAL '1 day'

Khoảng ngày nửa mở

Đừng dùng BETWEEN cho ngày có giờ:

-- Sai: bỏ sót đơn từ 00:00 đến 09:30 ngày cuối nếu cột là timestamp
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

-- Đúng: nửa mở
WHERE created_at >= '2026-01-01'
  AND created_at <  '2026-02-01';

BETWEEN bao gồm hai đầu; với timestamp, đầu cuối là 00:00:00 nên mất gần cả ngày 31. Khoảng nửa mở tránh được lỗi này và hoạt động với mọi độ chính xác.

Với cột date thì BETWEEN chấp nhận được, nhưng dùng nửa mở cho nhất quán vẫn tốt hơn.

Ngày sai định dạng

Dữ liệu nguồn thường có nhiều định dạng:

Điểm nguy hiểm: 05/01/2026 vừa có thể là 5 tháng 1 vừa có thể là 1 tháng 5 tuỳ quy ước. Không đoán — xác định từ nguồn dữ liệu và áp dụng một quy ước cho toàn bộ.

Parse nhiều định dạng bằng CASE:

SELECT
  id,
  CASE
    WHEN ngay_raw ~ '^\d{4}-\d{2}-\d{2}$' THEN to_date(ngay_raw, 'YYYY-MM-DD')
    WHEN ngay_raw ~ '^\d{2}/\d{2}/\d{4}$' THEN to_date(ngay_raw, 'DD/MM/YYYY')
    ELSE NULL
  END AS ngay
FROM staging_orders;

Dòng không khớp nhận NULL và nên được gắn cờ, không bị bỏ im lặng.

Ngày không tồn tại

to_date('31/02/2026', 'DD/MM/YYYY') trả về 2026-03-03 thay vì báo lỗi. Để phát hiện, định dạng ngược lại và so sánh:

SELECT id, ngay_raw
FROM staging_orders
WHERE ngay_raw ~ '^\d{2}/\d{2}/\d{4}$'
  AND to_char(to_date(ngay_raw, 'DD/MM/YYYY'), 'DD/MM/YYYY') <> ngay_raw;

Truy vấn này bắt được các ngày đã bị cuộn tháng.

Chênh lệch thời gian

SELECT
  created_at,
  shipped_at,
  shipped_at - created_at AS khoang_cach,
  EXTRACT(EPOCH FROM (shipped_at - created_at)) / 3600 AS so_gio,
  EXTRACT(DAY FROM (shipped_at - created_at)) AS so_ngay
FROM orders
WHERE shipped_at IS NOT NULL;

Lưu ý:

• Trừ hai timestamp cho interval; EXTRACT lấy phần nguyên.
• EXTRACT(DAY FROM interval) chỉ là phần ngày, không phải tổng ngày — với 50 giờ, kết quả là 2 ngày và phần giờ riêng.
• Muốn tổng giờ, dùng EXTRACT(EPOCH ...) / 3600.
• Chênh lệch âm (giao trước khi đặt) là lỗi dữ liệu, nên kiểm tra.

Làm tròn về kỳ báo cáo

SELECT
  date_trunc('month', order_date)::date AS thang,
  COUNT(*) AS so_don
FROM orders
GROUP BY 1
ORDER BY 1;

date_trunc giữ nguyên kiểu timestamp; ép về date để nhãn sạch. Với tuần, date_trunc('week') bắt đầu từ thứ Hai theo ISO.

Thực hành

1. Chuyển một timestamptz sang giờ Việt Nam.
2. Viết khoảng nửa mở cho tháng 1/2026 trên cột timestamp.
3. Parse cột ngày có hai định dạng bằng CASE.
4. Tìm các dòng ngày không tồn tại.
5. Tính số giờ trung bình từ đặt đến giao.

Lỗi thường gặp

Tóm tắt

• timestamptz cho sự kiện, date cho ngày nghiệp vụ.
• Chuyển múi giờ tường minh; Việt Nam UTC+7 không có DST.
• Dùng khoảng nửa mở thay BETWEEN cho timestamp.
• Kiểm tra ngày không tồn tại bằng định dạng ngược.
• EXTRACT(DAY) không phải tổng ngày; dùng EPOCH khi cần.