DATE_TRUNC() là một công cụ mạnh mẽ cho phép bạn cắt ngắn giá trị thời gian đến một đơn vị thời gian nhất định. Ví dụ, bạn có thể làm tròn timestamp đến đầu ngày, tháng, năm, giờ, v.v. Cái này cực kỳ hữu ích khi phân tích dữ liệu theo từng giai đoạn (kiểu như muốn nhóm đơn hàng theo ngày, tháng hoặc năm).
Hãy tưởng tượng ngày giờ giống như một chuỗi văn bản dài, nơi bạn có giờ, phút, giây. Hàm DATE_TRUNC() lấy chuỗi đó và "cắt" phần thừa, chỉ giữ lại phần bạn cần. Ví dụ:
- Bạn muốn cắt ngày
2023-10-01 15:30:45đến đầu ngày. Kết quả sẽ là2023-10-01 00:00:00. - Hoặc bạn chỉ muốn giữ lại giây đầu tiên của giờ, tức là
2023-10-01 15:00:00.
Cú pháp
Cú pháp của hàm DATE_TRUNC() như sau:
DATE_TRUNC(field, source)
- field — là đơn vị thời gian mà bạn muốn "cắt" ngày đến. Ví dụ,
year,month,day,hour,minute. - source — là giá trị thời gian mà bạn muốn cắt ngắn. Nó có thể là một cột kiểu
TIMESTAMPhoặc kết quả của một hàm khác, ví dụNOW().
Ví dụ gọi đơn giản:
SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45');
-- Kết quả: 2023-10-01 00:00:00
Các trường được hỗ trợ
Dưới đây là danh sách một số đơn vị thời gian được hỗ trợ mà bạn có thể dùng với DATE_TRUNC():
| Đơn vị thời gian | Mô tả |
|---|---|
year |
Đầu năm (ví dụ, 2023-01-01 00:00:00) |
quarter |
Đầu quý (ví dụ, 2023-07-01 00:00:00) |
month |
Đầu tháng (ví dụ, 2023-10-01 00:00:00) |
week |
Đầu tuần* (ví dụ, 2023-09-25 00:00:00) |
day |
Đầu ngày (ví dụ, 2023-10-01 00:00:00) |
hour |
Đầu giờ (ví dụ, 2023-10-01 15:00:00) |
minute |
Đầu phút (ví dụ, 2023-10-01 15:30:00) |
second |
Đầu giây (ví dụ, 2023-10-01 15:30:45) |
Đơn vị thời gian càng nhỏ thì kết quả cắt càng chính xác. À mà, tuần bắt đầu từ chủ nhật nhé :)
Ví dụ sử dụng DATE_TRUNC()
Cắt đến đầu ngày. Trong ví dụ này, mình sẽ lấy một timestamp và làm tròn nó đến đầu ngày:
SELECT DATE_TRUNC('day', TIMESTAMP '2023-10-01 15:30:45') AS truncated_day;
-- Kết quả: 2023-10-01 00:00:00
Cắt đến đầu tháng. Giờ mình sẽ cắt ngày đến đầu tháng:
SELECT DATE_TRUNC('month', TIMESTAMP '2023-10-01 15:30:45') AS truncated_month;
-- Kết quả: 2023-10-01 00:00:00
Cắt đến đầu năm. Thử làm tròn ngày đến đầu năm nhé:
SELECT DATE_TRUNC('year', TIMESTAMP '2023-10-01 15:30:45') AS truncated_year;
-- Kết quả: 2023-01-01 00:00:00
Dùng với thời gian hiện tại (NOW()). Nếu bạn muốn luôn làm việc với ngày giờ hiện tại, bạn có thể kết hợp DATE_TRUNC() và NOW():
SELECT DATE_TRUNC('hour', NOW()) AS truncated_hour;
-- Kết quả sẽ phụ thuộc vào thời gian hiện tại, ví dụ: 2023-10-01 15:00:00
Nhóm đơn hàng theo tháng. Giờ đến ví dụ thực tế hơn. Giả sử bạn có một bảng đơn hàng, mỗi bản ghi có ngày đặt hàng. Bạn muốn đếm số đơn hàng mỗi tháng:
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
order_date TIMESTAMP NOT NULL
);
INSERT INTO orders (order_date) VALUES
('2023-10-01 10:15:00'),
('2023-10-01 15:30:00'),
('2023-09-15 12:45:00'),
('2023-08-20 09:00:00'),
('2023-08-25 10:30:00');
SELECT DATE_TRUNC('month', order_date) AS order_month,
COUNT(*) AS total_orders
FROM orders
GROUP BY order_month
ORDER BY order_month;Kết quả:
| order_month | total_orders |
|---|---|
| 2023-08-01 00:00 | 2 |
| 2023-09-01 00:00 | 1 |
| 2023-10-01 00:00 | 2 |
Các case thực tế khi dùng
Phân tích dữ liệu thời gian theo giai đoạn: muốn biết có bao nhiêu user đăng ký mỗi năm, tháng hay ngày? Dùng DATE_TRUNC() để nhóm dữ liệu nhé.
Làm báo cáo: làm tròn timestamp đúng cách sẽ giúp báo cáo dễ đọc hơn nhiều.
So sánh ngày giờ: nếu bạn có dữ liệu thời gian với độ chính xác cao (kiểu có cả mili giây), hãy cắt nó đến mức cần thiết để so sánh cho chuẩn.
Các lỗi thường gặp khi dùng DATE_TRUNC()
Dùng trường không được hỗ trợ. Ví dụ, trường millisecond không được hỗ trợ, nếu dùng sẽ bị lỗi liền.
Sai kiểu dữ liệu. Hàm DATE_TRUNC() chỉ hoạt động với các kiểu dữ liệu thời gian như TIMESTAMP. Nếu truyền vào chuỗi, bạn sẽ bị lỗi.
Lỗi làm tròn. Nhớ là DATE_TRUNC() luôn cắt thời gian đến đầu đơn vị thời gian chỉ định. Nếu bạn muốn làm tròn thời gian, nên dùng cách khác nhé.
GO TO FULL VERSION