date_trunc

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

• date_trunc() đưa một mốc thời gian về đầu tháng, quý, năm hoặc tuần.
• Gom nhóm theo tháng bằng date_trunc và bằng TO_CHAR — chọn cái nào khi nào.
• Hiển thị kết quả thời gian đúng cách để không mất nghĩa.
• Điền các kỳ bị thiếu bằng generate_series.
• Vì sao bọc cột trong hàm khi lọc làm mất chỉ mục.

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

date_trunc('month', ngay) trả về mốc thời gian ở đầu đơn vị được chọn. Với ngày 2025-03-15, kết quả là 2025-03-01 00:00:00. Đây không phải phép "làm tròn" theo nghĩa gần nhất — nó luôn kéo về đầu kỳ.

SELECT date_trunc('month', DATE '2025-03-15') AS dau_thang,
       date_trunc('quarter', DATE '2025-03-15') AS dau_quy,
       date_trunc('year',    DATE '2025-03-15') AS dau_nam,
       date_trunc('week',    DATE '2025-03-15') AS dau_tuan;

Kết quả: 2025-03-01, 2025-01-01, 2025-01-01, 2025-03-10. Tuần trong PostgreSQL bắt đầu từ thứ Hai, nên 2025-03-15 (thứ Bảy) lùi về thứ Hai 2025-03-10.

Gom doanh thu theo tháng:

SELECT date_trunc('month', o.order_date)::date AS thang,
       COUNT(DISTINCT o.id) AS so_don,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY 1
ORDER BY 1;

Kết quả 11 dòng:

Hai chi tiết đáng chú ý. Thứ nhất, phải dùng COUNT(DISTINCT o.id) vì JOIN order_items nhân dòng — đếm COUNT(*) sẽ ra số dòng hàng. Thứ hai, ::date chỉ để hiển thị gọn; kiểu thật vẫn là timestamp.

Vì sao không dùng TO_CHAR? Nó trả về chuỗi:

SELECT TO_CHAR(o.order_date, 'YYYY-MM') AS thang,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY 1
ORDER BY 1;

Kết quả giống hệt về số, nhưng thang là text '2025-01'. Chuỗi này sắp xếp đúng vì định dạng YYYY-MM có độ dài cố định — nhưng bạn không thể cộng trừ, so sánh với DATE, hay nối với bảng lịch. Chọn theo mục đích:

Một lỗi hiển thị hay gặp: date_trunc trả timestamp, nên giao diện có thể in 2025-01-01T00:00:00.000Z. Hãy ép ::date hoặc format ở tầng hiển thị — đừng để người đọc báo cáo thấy giờ không có ý nghĩa.

Bảng dữ liệu có thể thiếu tháng. Nếu một tháng không có đơn, GROUP BY đơn giản sẽ bỏ qua tháng đó, khiến biểu đồ nối liền hai tháng cách xa nhau. Dựng đủ kỳ rồi nối trái:

WITH monthly AS (
  SELECT date_trunc('month', o.order_date)::date AS thang,
         SUM(oi.quantity * oi.unit_price) AS doanh_thu
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  GROUP BY 1
), calendar AS (
  SELECT generate_series(DATE '2025-01-01', DATE '2025-11-01', INTERVAL '1 month')::date AS thang
)
SELECT c.thang, COALESCE(m.doanh_thu, 0) AS doanh_thu
FROM calendar c
LEFT JOIN monthly m USING (thang)
ORDER BY c.thang;

Với dữ liệu mẫu, cả 11 tháng đều có doanh thu nên kết quả không đổi. Trên dữ liệu thật, đây là bước bắt buộc trước khi vẽ biểu đồ hoặc tính tăng trưởng.

Ví dụ chạy được

Doanh thu theo quý:

SELECT date_trunc('quarter', o.order_date)::date AS quy,
       COUNT(DISTINCT o.id) AS so_don,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY 1
ORDER BY 1;

Bốn dòng: quý 1 (tháng 1–3) 10.560.000; quý 2 (4–6) 9.900.000; quý 3 (7–9) 6.660.000; quý 4 (10–11, chưa đủ) 4.110.000. Tổng vẫn 31.230.000. Quý 4 chỉ có hai tháng nên không so sánh trực tiếp với ba quý trước — bài học về kỳ chưa đủ.

Số khách đăng ký mới theo tháng:

SELECT date_trunc('month', signup_date)::date AS thang,
       COUNT(*) AS khach_moi
FROM customers
GROUP BY 1
ORDER BY 1;

Mười hai dòng, mỗi tháng đúng một khách: 2023-03 An Nguyễn; 2023-07 Bình Trần; 2024-01 Chi Lê; 2024-02 Dũng Phạm; 2024-05 Em Võ; 2024-08 Giang Đặng; 2024-11 Hà Bùi; 2025-01 Inh Đỗ; 2025-02 Khánh Hồ; 2025-03 Linh Ngô; 2025-04 Minh Dương; 2025-06 Nga Lý. Các tháng không có khách mới không xuất hiện trong kết quả, dù về mặt kinh doanh con số của tháng đó là 0.

Đếm theo tuần:

SELECT date_trunc('week', o.order_date)::date AS tuan,
       COUNT(*) AS so_don
FROM orders o
GROUP BY 1
ORDER BY 1;

Mỗi đơn rơi vào tuần bắt đầu thứ Hai. Hai đơn cùng tuần sẽ chung một dòng, kể cả khi khác ngày. Cách gom này phù hợp cho báo cáo vận hành theo tuần, nhưng nhớ rằng tuần đầu và tuần cuối của dữ liệu thường không đủ bảy ngày.

Một biến thể hữu ích — vừa nhãn hiển thị vừa giữ kiểu ngày:

SELECT TO_CHAR(date_trunc('month', o.order_date), 'MM/YYYY') AS nhan,
       date_trunc('month', o.order_date)::date AS thang,
       SUM(oi.quantity * oi.unit_price) AS doanh_thu
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY 2, 1
ORDER BY thang;

Dùng nhan cho trục biểu đồ, thang cho sắp xếp và tính toán. Hai cột, hai mục đích, không cần ép kiểu qua lại trong ứng dụng.

Thực hành

1. Chạy câu gom tháng và đối chiếu 11 dòng với bảng ở trên. Kiểm tra tổng bằng 31.230.000.
2. Đổi date_trunc thành TO_CHAR và thử ORDER BY thang — kết quả sắp xếp có đúng không? Vì sao?
3. Gom theo quý và xác nhận quý 4 chỉ có hai tháng dữ liệu.
4. Thêm calendar bằng generate_series cho khoảng 2024-01 đến 2025-11. Bao nhiêu tháng không có khách đăng ký?
5. Viết câu đếm đơn theo tuần và tìm tuần có nhiều đơn nhất.

Lỗi thường gặp

Lỗi cuối cùng thuộc chương hiệu năng nhưng nên biết ngay: WHERE date_trunc('month', order_date) = DATE '2025-03-01' buộc PostgreSQL tính hàm cho từng dòng và không dùng được chỉ mục trên order_date. Viết WHERE order_date >= DATE '2025-03-01' AND order_date < DATE '2025-04-01' nhanh hơn nhiều và cho cùng kết quả.

Tóm tắt

• date_trunc kéo mốc thời gian về đầu tháng/quý/năm/tuần.
• Trả timestamp — ép ::date khi hiển thị.
• TO_CHAR tiện cho nhãn, date_trunc tiện cho tính toán và nối bảng.
• generate_series + LEFT JOIN để không mất kỳ trống.
• Đừng bọc cột trong hàm ở WHERE — dùng khoảng ngày.