Làm việc với khoảng thời gian: INTERVAL

Buổi tập kéo dài một tiếng rưỡi, ngày làm việc — 8 tiếng, nghỉ giải lao — 20 phút, còn luộc trứng thì cần 10 phút. Tất cả những thứ này đều liên quan đến khoảng thời gian.

Các khoảng thời gian rất hữu ích trong ứng dụng thực tế, ví dụ như khi cần tính deadline hoàn thành task, xác định sự khác biệt giữa hai ngày hoặc cộng/trừ thời gian. Nhưng trước khi đi sâu vào chi tiết, tụi mình cùng ôn lại chút xíu những gì đã học ở bài trước nhé.

Trong PostgreSQL, INTERVAL là một kiểu dữ liệu đặc biệt dùng để biểu diễn khoảng thời gian. Khác với các kiểu thời gian khác, nó không lưu trữ ngày hoặc giờ cụ thể mà chỉ mô tả một khoảng, ví dụ: "2 ngày", "3 giờ", "5 phút".

Cú pháp INTERVAL

Có thể khai báo khoảng thời gian theo dạng:

INTERVAL 'số đơn_vị_thời_gian'

Ví dụ:

INTERVAL '2 days' -- Hai ngày
INTERVAL '3 hours' -- Ba giờ
INTERVAL '15 minutes' -- Mười lăm phút
INTERVAL '1 day 2 hours' -- Một ngày hai giờ

PostgreSQL hỗ trợ nhiều đơn vị thời gian khác nhau: year, month, day, hour, minute, second. Bạn có thể kết hợp chúng trong cùng một biểu thức.

Sử dụng INTERVAL trong truy vấn

Kiểu dữ liệu INTERVAL cực kỳ hữu ích khi bạn kết hợp nó với các kiểu thời gian khác (DATE, TIMESTAMP). Cùng xem qua các thao tác cơ bản nhé.

Cộng INTERVAL vào ngày/giờ

Có thể cộng khoảng thời gian vào giá trị ngày hoặc giờ. Ví dụ:

SELECT CURRENT_DATE + INTERVAL '7 days' AS delivery_date;
-- Lấy ngày sau 7 ngày kể từ hôm nay

Kết quả:

Trừ INTERVAL khỏi ngày/giờ

Ngoài cộng, bạn cũng có thể trừ khoảng thời gian:

SELECT NOW() - INTERVAL '2 hours' AS two_hours_ago;
-- Lấy thời gian cách đây hai tiếng

Kết quả:

Tính chênh lệch giữa hai ngày

Một trong những tính năng mạnh của INTERVAL là tính sự khác biệt giữa hai ngày:

SELECT '2023-10-15'::DATE - '2023-10-01'::DATE AS days_difference;
-- Có bao nhiêu ngày giữa hai ngày này

Kết quả:

Lưu ý là kết quả cũng được trả về dưới dạng khoảng thời gian.

Ví dụ thực tế với INTERVAL

Tính ngày giao hàng. Giả sử có một shop online, giao hàng mất từ 3 đến 7 ngày. Đây là cách tính các ngày giao hàng có thể xảy ra:

SELECT CURRENT_DATE + INTERVAL '3 days' AS earliest_delivery,
       CURRENT_DATE + INTERVAL '7 days' AS latest_delivery;

Kết quả:

Tính thời gian hoàn thành task. Giả sử có bảng tasks, mỗi task có ngày bắt đầu. Cần tính ngày kết thúc dựa trên thời lượng (duration) của task tính bằng giờ:

CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    task_name TEXT,
    start_time TIMESTAMP,
    duration INTERVAL
);

INSERT INTO tasks (task_name, start_time, duration)
VALUES
('Chuẩn bị báo cáo', '2023-10-01 09:00:00', INTERVAL '4 hours'),
('Kiểm thử ứng dụng', '2023-10-01 10:00:00', INTERVAL '2 hours 30 minutes');

SELECT task_name,
       start_time,
       start_time + duration AS end_time
FROM tasks;

Kết quả:

So sánh thời gian hoàn thành. Đôi khi cần tìm tất cả các task hoàn thành dưới 3 tiếng:

SELECT task_name
FROM tasks
WHERE duration < INTERVAL '3 hours';

Kết quả:

Mẹo hay với INTERVAL

Tính INTERVAL động. Có thể kết hợp INTERVAL với các phép toán khác. Ví dụ, lấy số ngày từ một cột rồi chuyển thành INTERVAL:

SELECT CURRENT_DATE + (order_days || ' days')::INTERVAL AS order_due_date
FROM orders;

Chuyển INTERVAL thành chuỗi. Đôi khi cần chuyển INTERVAL thành chuỗi để hiển thị:

SELECT INTERVAL '2 days 3 hours'::TEXT AS interval_text;

Kết quả:

Lỗi thường gặp khi dùng INTERVAL

Làm việc với INTERVAL dù mạnh mẽ nhưng cũng dễ gặp lỗi. Một lỗi phổ biến là dùng sai format. Ví dụ, nếu bạn thử INTERVAL '2 hours and 30 minutes' sẽ bị lỗi vì PostgreSQL không hiểu từ "and". Cách đúng là: INTERVAL '2 hours 30 minutes'.

Một lỗi nữa là quên ghi đơn vị thời gian, kiểu như INTERVAL '2'. PostgreSQL không biết "2" là gì, nên luôn nhớ ghi rõ đơn vị (2 days, 2 hours).