Trino SQL Bài học 13: Các hàm xử lý ngày giờ (Date and Time Functions)

Mục tiêu bài học:

• Lấy thời gian hệ thống một cách chính xác theo chuẩn phân tán.

• Bóc tách các thành phần (năm, tháng, ngày, giờ) từ một cột thời gian.

• Thực hiện các phép cộng, trừ thời gian (Date Arithmetic) để tính toán kỳ hạn, độ trễ.

• Chuyển đổi qua lại giữa chuỗi văn bản (String) và kiểu thời gian (Timestamp) phục vụ cho việc làm báo cáo.

1. Lấy thời gian hiện tại của hệ thống

Trong báo cáo tự động, bạn thường xuyên phải so sánh dữ liệu với ngày hôm nay (ví dụ: lấy doanh thu 7 ngày gần nhất). Trino cung cấp các hàm sau để lấy thời gian từ hệ thống (lưu ý các hàm chuẩn ANSI SQL này thường không có dấu ngoặc đơn ở cuối).

• current_date: Lấy ngày hiện tại (Kiểu DATE).

• current_time: Lấy giờ hiện tại có chứa múi giờ (Kiểu TIME WITH TIME ZONE).

• current_timestamp (hoặc now()): Lấy đầy đủ ngày giờ và múi giờ hiện tại.

SQL

SELECT 
    current_date AS hom_nay,
    current_timestamp AS bay_gio;

2. Trích xuất thành phần thời gian (Extract)

Khi phân tích xu hướng (Trend Analysis), bạn cần gom nhóm dữ liệu theo từng tháng, hoặc từng quý. Hàm EXTRACT giúp bạn bóc tách một thành phần cụ thể ra khỏi dữ liệu thời gian.

Cú pháp chuẩn: EXTRACT(đơn_vị FROM thời_gian)

SQL

-- Lấy năm, tháng và quý từ ngày đặt hàng
SELECT 
    orderdate,
    EXTRACT(YEAR FROM orderdate) AS order_year,
    EXTRACT(MONTH FROM orderdate) AS order_month,
    EXTRACT(QUARTER FROM orderdate) AS order_quarter
FROM tpch.tiny.orders
LIMIT 5;

Mẹo viết nhanh: Trino hỗ trợ các hàm viết tắt thay cho EXTRACT như year(x), month(x), day(x), day_of_week(x) (1 = Thứ Hai, 7 = Chủ Nhật).

SQL

SELECT year(orderdate), month(orderdate) FROM tpch.tiny.orders;

3. Tính toán cộng trừ thời gian (Date Arithmetic)

Đây là nhóm hàm quan trọng nhất khi làm việc với chuỗi cung ứng hoặc vòng đời khách hàng (tính thời gian giao hàng, tính ngày đáo hạn).

A. Cộng thêm / Trừ lùi thời gian với date_add()

Cú pháp: date_add(unit, value, timestamp)

• unit: Đơn vị thời gian (chuỗi ký tự, ví dụ: 'day', 'month', 'year', 'hour').

• value: Số lượng muốn cộng (số nguyên dương để cộng tiến, số âm để trừ lùi).

SQL

-- Tính ngày dự kiến giao hàng (cộng thêm 15 ngày vào ngày đặt hàng)
SELECT 
    orderkey, 
    orderdate, 
    date_add('day', 15, orderdate) AS estimated_delivery
FROM tpch.tiny.orders;

-- Lấy danh sách các đơn hàng trong vòng 1 tháng trở lại đây
SELECT orderkey, orderdate
FROM tpch.tiny.orders
WHERE orderdate >= date_add('month', -1, current_date);

B. Khoảng cách giữa hai mốc thời gian với date_diff()

Cú pháp: date_diff(unit, timestamp1, timestamp2) Hàm này trả về số nguyên thể hiện khoảng cách từ timestamp1 đến timestamp2 (Công thức: timestamp2 - timestamp1).

SQL

-- Dùng bảng lineitem để tính độ trễ từ lúc cam kết (commitdate) đến lúc thực giao (shipdate)
SELECT 
    orderkey,
    commitdate,
    shipdate,
    date_diff('day', commitdate, shipdate) AS delay_days
FROM tpch.tiny.lineitem
WHERE date_diff('day', commitdate, shipdate) > 0; -- Chỉ lấy các đơn giao trễ

4. Chuyển đổi định dạng Thời gian và Chuỗi (Format & Parse)

Dữ liệu thô từ log thường lưu dưới dạng chuỗi (String) với đủ loại định dạng lạ mắt (ví dụ: 2026/10/11 hoặc 11-10-2026). Bạn phải biến nó thành kiểu DATE để tính toán, sau đó lại định dạng thành chuỗi đẹp mắt để hiển thị lên Dashboard.

Trino sử dụng hệ thống định dạng định danh (Format Specifiers) tương tự như MySQL (ví dụ: %Y là năm 4 số, %m là tháng 2 số, %d là ngày).

A. Ép Chuỗi thành Thời gian với date_parse()

SQL

-- Biến chuỗi '2026/10/11' thành kiểu TIMESTAMP thực thụ
SELECT date_parse('2026/10/11 14:30:00', '%Y/%m/%d %H:%i:%s') AS parsed_time;

B. Ép Thời gian thành Chuỗi hiển thị với date_format()

SQL

-- Biến ngày đặt hàng chuẩn '1995-12-01' thành định dạng 'Tháng 12, Năm 1995'
SELECT 
    orderdate,
    date_format(orderdate, '%m-%Y') AS month_year_str
FROM tpch.tiny.orders;

5. Làm việc với Múi giờ (Timezones)

Khi công ty của bạn có server ở Mỹ nhưng phục vụ khách hàng ở Việt Nam, sự chênh lệch múi giờ sẽ làm sai lệch hoàn toàn số liệu doanh thu chốt theo ngày.

Toán tử AT TIME ZONE giúp bạn dịch chuyển một mốc thời gian sang múi giờ tương ứng.

SQL

-- Giả sử hệ thống lưu log theo múi giờ UTC, bạn muốn xem theo giờ Việt Nam
SELECT 
    current_timestamp AS server_time, 
    current_timestamp AT TIME ZONE 'Asia/Saigon' AS vietnam_time;

Lưu ý: Việc thay đổi múi giờ không làm thay đổi khoảnh khắc thực tế trong vũ trụ, nó chỉ thay đổi con số giờ hiển thị trên đồng hồ của bạn tại khu vực đó.

Cạm bẫy Epoch Time: Trong các hệ thống NoSQL (như MongoDB) hoặc Kafka log, thời gian thường lưu ở dạng số nguyên (Unix Epoch - số mili-giây đếm từ 01/01/1970). Để chuyển nó thành Timestamp trên Trino, hãy dùng hàm from_unixtime(số_giây) hoặc from_unixtime(số_mili_giây / 1000.0).