SQL cho phân tích dữ liệu · Hướng dẫn · 30 tháng 10, 2026 · 12 phút đọc

Hàm xử lý ngày tháng trong SQL: EXTRACT, date_trunc, cộng khoảng thời gian và tính số ngày giữa hai ngày

Bốn nhóm hàm xử lý ngày tháng giải quyết gần như mọi việc mà người làm phân tích gặp phải. EXTRACT lấy ra một thành phần của ngày (năm, tháng, giờ, thứ trong tuần). date_trunc làm tròn một mốc thời gian xuống đầu ngày, đầu tuần hoặc đầu tháng của nó, đó là cách bạn nhóm theo kỳ. Khoảng thời gian (+ INTERVAL '2 hours') dịch một ngày tới trước hoặc lùi về sau. Phép trừ cho bạn khoảng thời gian giữa hai ngày. Nắm vững bốn thứ này là bạn dựng được phần lớn các báo cáo theo thời gian.

Mọi ví dụ ở đây đều chạy trên cơ sở dữ liệu hãng hàng không của khóa SQL trên Nomto: 33.121 chuyến bay từ 12/7 đến 10/9/2026 và 262.788 đơn đặt chỗ, với thời gian tính theo giờ Moscow. Truy vấn dùng cú pháp PostgreSQL và chạy trên DuckDB, động cơ của phòng học SQL trên Nomto; mọi bảng kết quả đều là kết quả chạy thật. Ở những chỗ PostgreSQL và DuckDB đặt tên hàm khác nhau, bài viết sẽ nói rõ.

DATE, TIMESTAMP và INTERVAL

Ba kiểu dữ liệu đảm nhận mọi việc:

Trong dữ liệu hãng hàng không, flights.scheduled_departure, flights.actual_departure và bookings.book_date là kiểu timestamp. Để biến một timestamp thành ngày, hãy ép kiểu: scheduled_departure::date (hoặc cách viết chuẩn CAST(scheduled_departure AS DATE)). Cả hai đều chạy được trong PostgreSQL và DuckDB.

Số chuyến bay mỗi ngày trong ba ngày đầu tháng 8:

SELECT scheduled_departure::date AS day,
       COUNT(*) AS flights
FROM flights
WHERE scheduled_departure >= '2026-08-01'
  AND scheduled_departure <  '2026-08-04'
GROUP BY 1
ORDER BY 1;

Hãy để ý bộ lọc: “từ đầu ngày đầu tiên trở đi, trước đầu ngày sau ngày cuối cùng”. Cách này chạy đúng với timestamp mà không cần hàm nào bọc quanh cột, nhờ đó cơ sở dữ liệu còn dùng được chỉ mục. BETWEEN '2026-08-01' AND '2026-08-03' sẽ dừng ở nửa đêm đầu ngày 3/8 và âm thầm làm mất gần trọn một ngày.

EXTRACT: lấy năm, tháng, giờ hoặc thứ trong tuần

EXTRACT(part FROM value) trả về một thành phần dưới dạng số:

SELECT flight_no,
       scheduled_departure,
       EXTRACT(year  FROM scheduled_departure) AS yr,
       EXTRACT(month FROM scheduled_departure) AS mon,
       EXTRACT(day   FROM scheduled_departure) AS d,
       EXTRACT(hour  FROM scheduled_departure) AS hr,
       EXTRACT(dow   FROM scheduled_departure) AS dow
FROM flights
WHERE flight_id = 1;

Ngày 12/7/2026 là Chủ nhật, và dow trả về 0: trong cả PostgreSQL lẫn DuckDB, dow chạy từ 0 (Chủ nhật) đến 6 (thứ Bảy). Nếu bạn muốn thứ Hai = 1 và Chủ nhật = 7, hãy dùng isodow. Các thành phần hữu ích khác: quarter, week (số tuần theo ISO), minute và epoch (số giây kể từ năm 1970, hoặc độ dài của một khoảng thời gian tính bằng giây, xem thêm bên dưới).

Cách dùng điển hình là nhóm theo một thành phần của ngày. Những giờ cất cánh đông nhất:

SELECT EXTRACT(hour FROM scheduled_departure) AS hour,
       COUNT(*) AS flights
FROM flights
GROUP BY 1
ORDER BY flights DESC
LIMIT 5;

Và theo thứ trong tuần, bắt đầu từ thứ Hai:

SELECT EXTRACT(isodow FROM scheduled_departure) AS iso_dow,
       COUNT(*) AS flights
FROM flights
GROUP BY 1
ORDER BY 1;

Thứ Sáu là ngày vắng nhất trong lịch bay này, Chủ nhật là ngày đông nhất. Hãy nhớ rằng khoảng thời gian này không tròn số tuần, nên một số thứ xuất hiện nhiều hơn các thứ khác một lần. Để so sánh công bằng, hãy chia cho số lần mỗi thứ xuất hiện trong khoảng đó.

PostgreSQL và DuckDB. Cả hai đều hỗ trợ EXTRACT(part FROM x) và dạng hàm date_part('part', x). PostgreSQL 14+ trả về kiểu numeric từ EXTRACT và double precision từ date_part; DuckDB trả về số nguyên. DuckDB còn có các hàm tắt như year(x), hour(x), dayname(x) và monthname(x) mà PostgreSQL không có.

date_trunc: nhóm theo ngày, tuần hoặc tháng

EXTRACT(month ...) trả về 7 cho tháng 7 của mọi năm, điều này sai khi dữ liệu của bạn trải dài hơn một năm. Để nhóm theo kỳ trên lịch, hãy làm tròn timestamp xuống đầu kỳ bằng date_trunc('unit', value):

SELECT date_trunc('month', book_date) AS month,
       COUNT(*) AS bookings,
       ROUND(SUM(total_amount)) AS revenue
FROM bookings
GROUP BY 1
ORDER BY 1;

Tháng 6 và tháng 8 ở đây là tháng không trọn vẹn: đơn đặt chỗ bắt đầu từ 17/6 và dữ liệu dừng ở 11/8. Hãy luôn kiểm tra ngày đầu và ngày cuối trước khi so sánh các kỳ.

Tuần cũng hoạt động tương tự, và date_trunc('week', ...) bắt đầu tuần từ thứ Hai trong cả PostgreSQL lẫn DuckDB:

SELECT date_trunc('week', scheduled_departure) AS week,
       COUNT(*) AS flights
FROM flights
GROUP BY 1
ORDER BY 1
LIMIT 5;

Tuần đầu tiên chỉ có Chủ nhật 12/7, đó là lý do nó chỉ có 558 chuyến bay. Các đơn vị khác: 'day', 'hour', 'quarter', 'year'.

date_trunc trên một timestamp trả về một timestamp. Thêm ::date nếu bạn muốn một ngày gọn gàng trong báo cáo. Lưu ý rằng một kỳ không có dòng nào sẽ đơn giản là không xuất hiện; nếu bạn cần mọi ngày hoặc mọi tháng trong kết quả, kể cả những kỳ trống, hãy dựng một bảng lịch trước, như trong bài về date spine.

Cộng và trừ khoảng thời gian

Cộng hoặc trừ một INTERVAL để dịch chuyển một timestamp:

SELECT flight_no,
       scheduled_departure,
       scheduled_departure + INTERVAL '2 hours' AS plus_2h,
       scheduled_departure - INTERVAL '1 day'   AS minus_1d,
       CAST(scheduled_departure AS DATE) + 7    AS week_later
FROM flights
WHERE flight_id = 1;

Cộng một số nguyên vào một giá trị DATE là cộng thêm ngày, trong cả hai hệ quản trị. Với timestamp, hãy dùng interval.

Tính toán với tháng cần cẩn thận, vì các tháng dài ngắn khác nhau. Cộng một tháng vào ngày 31/1 không tràn sang tháng 3 mà dừng ở ngày cuối cùng của tháng 2:

SELECT DATE '2026-01-31' + INTERVAL '1 month' AS a;

Cùng ý tưởng đó cho bạn ngày cuối tháng: đầu tháng, cộng một tháng, trừ một ngày.

SELECT d,
       date_trunc('month', d) + INTERVAL '1 month' - INTERVAL '1 day' AS month_end,
       last_day(d) AS ld
FROM (SELECT DATE '2026-02-10' AS d);

last_day có trong DuckDB (và MySQL) nhưng không có trong PostgreSQL; phiên bản dùng date_trunc chạy được ở mọi nơi.

Interval cũng là cách bạn viết bộ lọc “N ngày gần nhất”. Trong bộ dữ liệu này, bản chụp được thực hiện lúc 18:00 ngày 11/8/2026, nên hãy dùng mốc đó thay cho now(): WHERE book_date >= TIMESTAMP '2026-08-11 18:00' - INTERVAL '7 days'. Trên một cơ sở dữ liệu đang hoạt động, now() và current_date chạy được trong cả PostgreSQL lẫn DuckDB.

Tính số ngày và số phút giữa hai ngày

Lấy một timestamp trừ đi một timestamp khác sẽ cho ra một interval. Những lần cất cánh trễ lâu nhất:

SELECT flight_no,
       scheduled_departure,
       actual_departure,
       actual_departure - scheduled_departure AS delay,
       EXTRACT(epoch FROM actual_departure - scheduled_departure) / 60 AS delay_min
FROM flights
WHERE actual_departure IS NOT NULL
ORDER BY delay DESC
LIMIT 3;

Một interval như 04:37:00 dễ đọc, nhưng bạn không thể lấy trung bình nó thành một KPI hay so sánh nó với một con số. EXTRACT(epoch FROM interval) biến nó thành số giây; chia cho 60 để ra phút, hoặc cho 3.600 để ra giờ. Cách này chạy giống nhau trong PostgreSQL và DuckDB, nên đó là thói quen an toàn nhất. Độ trễ trung bình theo tháng:

SELECT date_trunc('month', scheduled_departure)::date AS month,
       COUNT(*) AS departed,
       ROUND(AVG(EXTRACT(epoch FROM actual_departure - scheduled_departure) / 60), 1) AS avg_delay_min
FROM flights
WHERE actual_departure IS NOT NULL
GROUP BY 1
ORDER BY 1;

Để tính số ngày tròn, hãy trừ hai giá trị ngày. DATE - DATE trả về một số nguyên là số ngày, trong cả hai hệ quản trị. Hành khách đặt chỗ trước bao lâu? Mỗi dòng bên dưới là một chặng bay trong một đơn đặt chỗ:

SELECT b.book_ref,
       b.book_date,
       f.scheduled_departure,
       f.scheduled_departure::date - b.book_date::date AS days_ahead
FROM bookings b
JOIN tickets t         USING (book_ref)
JOIN ticket_flights tf USING (ticket_no)
JOIN flights f         USING (flight_id)
ORDER BY b.book_ref, f.scheduled_departure
LIMIT 5;

Chia khoảng cách đó thành các nhóm bằng CASE, và bạn có một báo cáo về thời gian đặt chỗ trước:

SELECT CASE WHEN days < 7  THEN '0-6 days'
            WHEN days < 14 THEN '7-13 days'
            WHEN days < 30 THEN '14-29 days'
            ELSE '30+ days' END AS booked_ahead,
       COUNT(*) AS segments
FROM (
  SELECT f.scheduled_departure::date - b.book_date::date AS days
  FROM bookings b
  JOIN tickets t         USING (book_ref)
  JOIN ticket_flights tf USING (ticket_no)
  JOIN flights f         USING (flight_id)
) s
GROUP BY 1
ORDER BY MIN(days);

Cách CASE chia giá trị thành nhóm được giải thích chi tiết trong bài CASE WHEN trong SQL.

date_diff, DATEDIFF và age

Mỗi hệ quản trị có những hàm tắt khác nhau, và đây là chỗ các truy vấn sao chép từ nơi khác hay bị hỏng:

PostgreSQL không có date_diff hay DATEDIFF, còn DuckDB không có to_char. age trả về một interval có tính đến lịch: age(TIMESTAMP '2026-08-11 18:00', TIMESTAMP '2026-06-17 00:00') cho ra 1 month 24 days 18:00:00 trong DuckDB (PostgreSQL in ra 1 mon 24 days 18:00:00).

Một cái bẫy với date_diff của DuckDB: nó đếm số ranh giới bị vượt qua, không phải số đơn vị trọn vẹn đã trôi qua:

SELECT date_diff('day', TIMESTAMP '2026-07-12 23:50', TIMESTAMP '2026-07-13 00:10') AS dd;

Hai mươi phút, nhưng là một “ngày”. Với ngày trên lịch (đặt chỗ ngày 10, bay ngày 24), đó đúng là điều bạn muốn. Với thời gian đã trôi qua (độ trễ có dài hơn một ngày không?), hãy tính interval và dùng EXTRACT(epoch ...).

Các lỗi thường gặp

• Nhóm theo EXTRACT(month ...) trên dữ liệu nhiều năm. Tháng 7/2025 và tháng 7/2026 rơi vào cùng một nhóm. Hãy dùng date_trunc('month', ...).
• BETWEEN trên timestamp. Cận trên là nửa đêm đầu ngày cuối cùng. Hãy dùng >= start AND < next_start.
• Bọc cột trong một hàm bên trong WHERE. WHERE date_trunc('day', scheduled_departure) = '2026-08-01' cho kết quả đúng nhưng buộc cơ sở dữ liệu tính hàm trên từng dòng. Một khoảng trên cột gốc cho kết quả tương đương và dùng được chỉ mục.
• Lấy trung bình interval để làm KPI. Hãy chuyển sang phút hoặc giờ trước bằng EXTRACT(epoch ...), để con số dễ đọc và dễ so sánh.
• Dùng now() trên một bản chụp dữ liệu. Bộ dữ liệu luyện tập và dữ liệu lịch sử có “hôm nay” của riêng chúng. Hãy ghi cứng mốc thời gian của bản chụp.
• Quên múi giờ. Mọi thời gian trong bộ dữ liệu này là giờ Moscow, lưu dưới dạng timestamp thường. Với dữ liệu thật, hãy kiểm tra xem cột là timestamp hay timestamptz và sự kiện được ghi theo múi giờ nào trước khi lấy ra giờ.

Ngày tháng thường đi cùng với việc nhóm dữ liệu và tổng lũy kế; xem bài GROUP BY và HAVING trong SQL và bài về tổng lũy kế trong SQL. Cột văn bản có bộ công cụ riêng, được trình bày trong bài về hàm xử lý chuỗi SQL.

Câu hỏi thường gặp

Làm sao lấy riêng phần ngày từ một timestamp trong SQL?

Hãy ép kiểu: scheduled_departure::date hoặc CAST(scheduled_departure AS DATE). Cả hai đều chạy được trong PostgreSQL và DuckDB. date_trunc('day', ...) cũng bỏ phần giờ nhưng vẫn giữ kiểu timestamp.

EXTRACT và date_trunc khác nhau thế nào?

EXTRACT trả về một thành phần của ngày dưới dạng số (7 cho tháng 7). date_trunc trả về một timestamp đầy đủ được làm tròn xuống đầu kỳ (2026-07-01 00:00:00). Dùng EXTRACT cho câu hỏi về tính mùa vụ (“giờ nào đông nhất?”) và date_trunc cho chuỗi thời gian (“số đơn đặt chỗ mỗi tháng”).

Làm sao tính số ngày giữa hai ngày trong PostgreSQL?

Trừ hai giá trị ngày: end_date - start_date trả về một số nguyên. Nếu các cột là timestamp, hãy ép kiểu trước (t2::date - t1::date) để tính theo ngày trên lịch, hoặc dùng EXTRACT(epoch FROM t2 - t1) / 86400 để tính số ngày đã trôi qua có phần lẻ.

date_trunc(‘week’) bắt đầu tuần từ thứ Hai hay Chủ nhật?

Từ thứ Hai, trong PostgreSQL và DuckDB. Với tuần bắt đầu từ Chủ nhật, hãy dịch ngày: date_trunc('week', d + INTERVAL '1 day') - INTERVAL '1 day'.

PostgreSQL có DATEADD không?

Không. Thay vào đó hãy cộng một interval: order_date + INTERVAL '3 days', hoặc + 3 trên cột kiểu DATE.

Ngôn ngữ khác: English, Español, Português (Brasil), 简体中文