Lọc theo ngày

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

• Viết literal ngày theo chuẩn ISO mà PostgreSQL luôn hiểu đúng.
• Lọc theo khoảng thời gian bằng >= và < thay vì BETWEEN khi có giờ phút.
• Tính số ngày giữa hai mốc và cộng trừ khoảng thời gian.
• Dùng EXTRACT để lọc theo tháng hoặc năm.
• Tránh lỗi múi giờ và lỗi định dạng ngày khiến kết quả lệch một ngày.

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

Cột ngày trong bộ dữ liệu này có kiểu date: chỉ lưu ngày, tháng, năm, không có giờ. Literal ngày viết theo chuẩn ISO YYYY-MM-DD, kèm từ khóa DATE:

SELECT id FROM orders WHERE order_date >= DATE '2025-06-01';

Có thể bỏ DATE và viết '2025-06-01', PostgreSQL sẽ tự chuyển kiểu khi so sánh với cột date. Nhưng viết rõ DATE an toàn hơn: nó không phụ thuộc vào việc cột bên kia có phải kiểu ngày hay không, và người đọc biết ngay ý định.

Không dùng định dạng Việt Nam như '01/06/2025'. PostgreSQL có thể hiểu đó là ngày 1 tháng 6 hoặc 6 tháng 1 tùy thiết lập DateStyle, và trong nhiều trường hợp nó báo lỗi. Chuẩn ISO là ngôn ngữ chung, không phụ thuộc cấu hình máy chủ.

So sánh ngày dùng các toán tử giống số, vì bên trong ngày được lưu dưới dạng số ngày kể từ một mốc:

SELECT id, order_date
FROM orders
WHERE order_date BETWEEN DATE '2025-01-01' AND DATE '2025-03-31'
ORDER BY order_date;

BETWEEN với date bao gồm cả hai biên, nên truy vấn trên lấy trọn quý I. Sáu dòng: đơn 1, 2, 3, 4, 5, 6.

Với cột có giờ (timestamp, timestamptz), BETWEEN ... '2025-03-31' sẽ bỏ sót các bản ghi từ 00:00:01 ngày 31/3 trở đi, vì '2025-03-31' được hiểu là nửa đêm. Cách viết đúng là khoảng nửa mở:

WHERE created_at >= TIMESTAMP '2025-03-01 00:00:00'
  AND created_at <  TIMESTAMP '2025-04-01 00:00:00'

Biên dưới bao gồm, biên trên loại trừ. Nhờ vậy mọi thời điểm trong tháng 3 đều được lấy, không trùng và không sót, bất kể độ chính xác của cột.

Cộng trừ ngày bằng INTERVAL:

SELECT DATE '2025-06-30' - DATE '2025-06-01' AS so_ngay;          -- 29
SELECT order_date + INTERVAL '30 days' AS ngay_het_han FROM orders;

Hai ngày cách nhau 29 ngày, vì phép trừ đếm khoảng cách chứ không đếm số ngày có mặt. Muốn đếm cả hai đầu thì cộng 1.

Lấy thành phần của ngày bằng EXTRACT:

SELECT EXTRACT(YEAR FROM order_date) AS nam,
       EXTRACT(MONTH FROM order_date) AS thang,
       COUNT(*) AS so_don
FROM orders
GROUP BY nam, thang
ORDER BY nam, thang;

Truy vấn này dùng GROUP BY — nội dung của chương 4 — nên ở đây chỉ cần hiểu EXTRACT trả về một số. Có thể dùng nó trong WHERE mà không cần gom nhóm:

SELECT id FROM orders WHERE EXTRACT(MONTH FROM order_date) = 6;

Cách này đúng nhưng không dùng được chỉ mục trên order_date, vì mỗi dòng phải tính lại biểu thức. Với bảng lớn, viết khoảng ngày >= DATE '2025-06-01' AND < DATE '2025-07-01' nhanh hơn nhiều. Đây là nguyên tắc chung: giữ cột trần ở một bên phép so sánh, đừng bọc nó trong hàm.

Cuối cùng, cẩn thận với timestamptz và múi giờ. Khi lưu thời điểm có múi giờ, PostgreSQL chuyển về UTC. Một đơn tạo lúc 23:30 ngày 30/6 giờ Việt Nam là 16:30 UTC cùng ngày, nhưng nếu máy chủ cấu hình múi giờ khác, DATE(order_date) có thể trả ngày khác. Với cột date thuần như bộ dữ liệu này, vấn đề không xảy ra; khi gặp cột thời gian thật, hãy xác định rõ múi giờ trước khi viết báo cáo.

Ví dụ chạy được

Đếm đơn trong quý I năm 2025:

SELECT id, order_date, status
FROM orders
WHERE order_date >= DATE '2025-01-01'
  AND order_date < DATE '2025-04-01'
ORDER BY order_date;

Sáu dòng:

Chú ý biên trên là 2025-04-01, không phải 2025-03-31. Với cột date, cả hai cách cho cùng kết quả, nhưng dùng mốc mở đầu tháng sau giúp câu truy vấn đúng cả khi cột đổi sang timestamp. Đây là thói quen nên giữ từ đầu.

Khách đăng ký trong năm 2025:

SELECT name, signup_date
FROM customers
WHERE signup_date >= DATE '2025-01-01'
  AND signup_date < DATE '2026-01-01'
ORDER BY signup_date;

Năm dòng: Inh Đỗ (10/01), Khánh Hồ (14/02), Linh Ngô (09/03), Minh Dương (21/04), Nga Lý (30/06). Bảy khách còn lại đăng ký 2023 hoặc 2024. Cộng lại: 5 + 5 + 2 = 12, khớp toàn bộ bảng.

Đơn trong tháng 6 năm 2025:

SELECT id, order_date, status
FROM orders
WHERE order_date >= DATE '2025-06-01'
  AND order_date < DATE '2025-07-01';

Hai dòng: đơn 10 ngày 01/06 (cancelled) và đơn 11 ngày 18/06 (completed). Nếu dùng BETWEEN '2025-06-01' AND '2025-06-30', kết quả vẫn đúng vì cột là date. Nhưng nếu ai đó đổi cột sang timestamp để lưu giờ đặt hàng, cách BETWEEN sẽ bỏ sót đơn đặt lúc 08:00 ngày 30/06. Khoảng nửa mở không có nhược điểm đó.

Một ví dụ tính toán:

SELECT id,
       order_date,
       DATE '2025-12-31' - order_date AS ngay_con_lai
FROM orders
WHERE status = 'completed'
ORDER BY ngay_con_lai
LIMIT 5;

Kết quả là số ngày từ mỗi đơn đến cuối năm 2025. Phép trừ ngày trả về số nguyên, rất tiện để tính tuổi đơn hàng hoặc thời gian còn lại.

Thực hành

1. Đếm số đơn trong từng quý của năm 2025. Tổng bốn quý phải bằng 16. Viết bốn truy vấn độc lập trước, sau đó nghĩ cách gộp bằng EXTRACT và GROUP BY.
2. Lấy khách đăng ký trong năm 2024. Kết quả năm dòng; so sánh với truy vấn năm 2025 để thấy tổng là 12.
3. Viết truy vấn lấy các đơn trong 90 ngày kể từ 2025-08-01. Xác định rõ biên trên trước khi chạy, và giải thích vì sao chọn 2025-10-30 hay 2025-10-31.
4. Thử dùng EXTRACT(MONTH FROM order_date) = 6 và so sánh kết quả với khoảng ngày. Hai cách phải cho cùng hai dòng; ghi lại cách nào dễ đọc hơn và cách nào sẽ nhanh hơn trên bảng lớn.

Lỗi thường gặp

Lỗi thứ hai là lỗi âm thầm nguy hiểm nhất: truy vấn không báo gì, chỉ thiếu dữ liệu. Nó thường xuất hiện khi hệ thống chuyển từ cột date sang timestamp mà báo cáo không được cập nhật. Dùng khoảng nửa mở ngay từ đầu giúp tránh cả lớp lỗi này.

Tóm tắt

• Literal ngày dùng DATE 'YYYY-MM-DD'; tránh định dạng phụ thuộc locale.
• Khoảng nửa mở >= đầu kỳ AND < đầu kỳ sau đúng cho cả date và timestamp.
• BETWEEN với ngày bao gồm hai biên nhưng dễ bỏ sót giờ khi cột có thời gian.
• INTERVAL để cộng trừ thời gian; phép trừ hai ngày trả số ngày.
• Giữ cột trần trong WHERE để cơ sở dữ liệu dùng được chỉ mục.