Đã đăng vào khoảng 2 giờ trước 10 phút đọc

Google BigQuery - Bài học 15: Xử lý Thời gian (Time-series) – Phân biệt TIMESTAMP, DATETIME, DATE và bài toán lệch múi giờ (Asia/Ho_Chi_Minh vs UTC)

Chào anh em! Ở Bài học 14, chúng ta đã làm chủ nghệ thuật xử lý chuỗi và biểu thức chính quy để bóc tách log thô. Hôm nay, chúng ta sẽ bước sang một "cục máu đông" kinh điển khác trong mọi hệ thống phần mềm từ Backend cho đến Data Warehouse: Xử lý Thời gian và Time-series.

Bạn đã bao giờ rơi vào tình cảnh này chưa: Đội tài chính gửi báo cáo doanh thu ngày 18/10/2026, nhưng khi bạn chạy câu lệnh SELECT trên BigQuery, số liệu lại khớp lệch so với hệ thống quản trị (Admin Panel) của công ty đúng 7 tiếng buổi sáng? Sếp nhìn bạn với ánh mắt hoài nghi, còn bạn thì mướt mồ hôi kiểm tra lại từng dòng code SQL.

Thủ phạm chính là sự nhầm lẫn tai hại giữa các kiểu dữ liệu thời gian và cái bẫy múi giờ UTC vs Asia/Ho_Chi_Minh. Hãy cùng mổ xẻ tường tận để không bao giờ mất điểm vì thời gian nữa!

1. Ôn lại "Tứ trụ" kiểu dữ liệu Thời gian trong GoogleSQL

Ở Bài 9 chúng ta đã điểm qua, nhưng khi làm việc với chuỗi thời gian (Time-series), bạn phải nắm thật chắc bản chất vật lý của 4 kiểu dữ liệu sau:

DATE (Chỉ có Ngày): Định dạng YYYY-MM-DD (Ví dụ: 2026-10-18). Không có giờ, không có múi giờ. Đây là kiểu dữ liệu tuyệt vời nhất để làm Partitioning Key cho các bảng lịch sử lớn.
TIME (Chỉ có Giờ): Định dạng HH:MM:SS (Ví dụ: 14:30:00). Đại diện cho thời điểm trong ngày bất kể ngày nào.
DATETIME (Ngày + Giờ, KHÔNG có Múi giờ): Định dạng YYYY-MM-DD HH:MM:SS (Ví dụ: 2026-10-18 14:30:00). Nó đại diện cho Thời gian tại chỗ (Wall-clock time) – tức là đồng hồ treo trên tường nhà bạn chỉ mấy giờ thì nó là bấy nhiêu, hoàn toàn độc lập với vị trí địa lý hay múi giờ trên Trái Đất.
TIMESTAMP (Thời điểm tuyệt đối gắn liền với UTC): Đại diện cho một khoảnh khắc vật lý duy nhất trên toàn vũ trụ, được tính chính xác bằng số micro-second kể từ mốc Epoch (01/01/1970 UTC). Bất kể bạn lưu TIMESTAMP ở đâu, bên dưới gầm máy nó luôn được quy đổi về múi giờ UTC (+0).

2. Cạm bẫy kinh điển: Tại sao bảng doanh thu của bạn lại lệch số vào đầu ngày?

Hãy hình dung kịch bản thực tế sau tại một công ty thương mại điện tử có trụ sở tại TP. Hồ Chí Minh (UTC+7):

Khách hàng bấm mua một đơn hàng vào lúc 02:00 sáng ngày 19/10/2026 theo giờ Việt Nam.
Hệ thống Backend ghi nhận thời điểm tạo đơn (created_at) vào database dưới dạng TIMESTAMP chuẩn UTC.
Vì múi giờ Việt Nam là UTC+7, nên 02:00 sáng ngày 19/10/2026 khi trừ đi 7 tiếng sẽ thành 19:00 tối ngày 18/10/2026 UTC.

Thảm họa xảy ra khi viết SQL:

Nếu bạn viết một câu lệnh thống kê doanh thu nhóm theo ngày bằng hàm DATE(created_at) trực tiếp trên cột TIMESTAMP:

SQL

-- CẢNH BÁO: CÁCH VIẾT NÀY GÂY LỆCH MÚI GIỜ!
SELECT 
    DATE(created_at) AS order_date,
    SUM(total_amount) AS revenue
FROM `my-project.core_dwh.fact_orders`
GROUP BY order_date;

Điều gì xảy ra? Hàm DATE(timestamp_col) lấy trực tiếp giá trị chuẩn UTC để cắt ngày. Do đó, đơn hàng lúc 02:00 sáng ngày 19/10 của khách Việt Nam sẽ bị BigQuery xếp vào ngày 18/10!
Kết quả: Toàn bộ doanh số khung giờ sáng sớm của Việt Nam bị đẩy lùi về ngày hôm trước, khiến báo cáo lệch hoàn toàn so với thực tế vận hành.

3. Bí quyết xử lý chuẩn xác múi giờ Việt Nam (`Asia/Ho_Chi_Minh`)

Để khắc phục triệt để bài toán lệch múi giờ, GoogleSQL cung cấp tham số múi giờ cực kỳ mạnh mẽ trong các hàm chuyển đổi thời gian.

Thay vì ép kiểu trực tiếp từ TIMESTAMP sang DATE, bạn phải chuyển đổi TIMESTAMP sang DATETIME kèm theo múi giờ địa phương ('Asia/Ho_Chi_Minh') trước, rồi mới lấy DATE:

SQL

-- CÁCH VIẾT CHUẨN XÁC CHO DOANH NGHIỆP TẠI VIỆT NAM:
SELECT 
    -- 1. Quy đổi thời gian tuyệt đối UTC sang thời gian tường (Wall-clock time) tại VN
    DATETIME(created_at, 'Asia/Ho_Chi_Minh') AS vn_local_datetime,
    
    -- 2. Cắt ngày theo đúng mốc 00h00 - 23h59 của giờ Việt Nam
    DATE(DATETIME(created_at, 'Asia/Ho_Chi_Minh')) AS vn_local_date,
    
    SUM(total_amount) AS revenue
FROM `my-project.core_dwh.fact_orders`
WHERE created_at >= TIMESTAMP('2026-10-01 00:00:00', 'Asia/Ho_Chi_Minh')
GROUP BY vn_local_date
ORDER BY vn_local_date DESC;

Quy tắc vàng: Luôn thực hiện mọi phép tính toán khoảng thời gian (như nhóm theo ngày, theo giờ hành chính, hay lọc ca trực) trên kiểu dữ liệu DATETIME đã được ép về múi giờ địa phương, còn các cột lưu trữ gốc trên bảng (created_at, updated_at) hãy luôn giữ nguyên kiểu TIMESTAMP để đảm bảo tính toàn vẹn toàn cầu.

4. Bộ công cụ Time-series thượng thừa trong GoogleSQL

Khi phân tích dữ liệu dạng chuỗi thời gian (Time-series analysis) như tính tốc độ tăng trưởng theo tuần, so sánh doanh thu cùng kỳ, hay tính tổng tích lũy (Running Total), bạn sẽ cần dùng đến các hàm chuyên dụng sau:

4.1. Hàm làm tròn mốc thời gian: `TIMESTAMP_TRUNC` / `DATE_TRUNC`

Dùng để nhóm các bản ghi theo giờ, theo tuần, theo tháng hoặc theo quý một cách tự động.

Ví dụ: Gom nhóm doanh thu theo từng tuần (bắt đầu từ thứ Hai):

SQL

SELECT 
    DATE_TRUNC(DATE(DATETIME(created_at, 'Asia/Ho_Chi_Minh')), WEEK(MONDAY)) AS week_start,
    SUM(total_amount) AS weekly_revenue
FROM `my-project.core_dwh.fact_orders`
GROUP BY week_start;

4.2. Hàm tính khoảng cách: `TIMESTAMP_DIFF` / `DATETIME_DIFF`

Dùng để đo lường khoảng thời gian giữa hai mốc (tính bằng giây, phút, giờ, ngày).

Ví dụ: Tính thời gian giao hàng (từ lúc tạo đơn đến lúc hoàn thành) tính bằng giờ:

SQL

SELECT 
    order_id,
    DATETIME_DIFF(completed_at, created_at, HOUR) as delivery_hours
FROM `my-project.core_dwh.fact_orders`
WHERE status = 'DELIVERED';

4.3. Tạo chuỗi thời gian liên tục với `GENERATE_ARRAY` hoặc `UNNEST(GENERATE_DATE_ARRAY(...))`

Một bài toán cực kỳ phổ biến trong BI: Ngày nào không có đơn hàng thì báo cáo phải hiện số 0, thay vì bị mất hút dòng ngày hôm đó.

SQL

-- Tạo ra một danh sách liên tục tất cả các ngày trong tháng 10/2026
SELECT day 
FROM UNNEST(GENERATE_DATE_ARRAY('2026-10-01', '2026-10-31', INTERVAL 1 DAY)) AS day;

Bạn có thể dùng cú pháp này LEFT JOIN với bảng doanh thu thực tế để lấp đầy các khoảng trống ngày không có giao dịch – một kỹ thuật tối quan trọng khi vẽ biểu đồ đường (Line Chart) trên Looker Studio!

5. Tối ưu hiệu năng: Tránh bẫy "Volatile Functions" làm hỏng Query Cache

Như đã học ở Bài 6, BigQuery cung cấp cơ chế Query Cache miễn phí 100% trong vòng 24 giờ nếu câu lệnh SQL chạy lại y hệt và dữ liệu gốc không đổi.

Tuy nhiên, các hàm thời gian động như CURRENT_TIMESTAMP() hoặc CURRENT_DATETIME() là những hàm thay đổi từng giây (Volatile Functions). Nếu bạn viết:

SQL

-- CẢNH BÁO: HÀM NÀY SẼ LÀM MẤT HIỆU LỰC CACHE!
SELECT * 
FROM `my-project.core_dwh.fact_orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY);

Vì thời gian hiện tại (CURRENT_TIMESTAMP()) liên tục trôi qua từng giây, nên mỗi khi bạn bấm chạy lại câu lệnh này vào các thời điểm khác nhau trong ngày, BigQuery hiểu rằng đây là một câu lệnh mới hoàn toàn và tuyệt đối không dùng Cache, buộc Dremel phải quét lại từ đầu và tính tiền lại từ đầu!

Mẹo khắc phục: Nếu bạn chỉ cần lọc dữ liệu theo khoảng cách ngày (ví dụ 7 ngày gần nhất tính từ hôm nay), hãy dùng hàm CURRENT_DATE('Asia/Ho_Chi_Minh') kết hợp với kiểu DATE gắn liền với cột phân vùng (Partition). Việc này giúp tận dụng tối đa cơ chế Partition Pruning (chỉ quét đúng các phân vùng của 7 ngày đó) và dễ dàng kích hoạt lại bộ nhớ đệm khi cần thiết.

Tổng kết Bài học 15

Xử lý thời gian trong BigQuery đòi hỏi sự cẩn trọng tuyệt đối với trục tọa độ không gian - thời gian:

Phân định rõ ràng: Dùng TIMESTAMP cho thời điểm tuyệt đối (lưu trữ gốc), dùng DATETIME kết hợp múi giờ (như 'Asia/Ho_Chi_Minh') cho các báo cáo nghiệp vụ địa phương.
Tránh bẫy lệch ngày: Luôn quy đổi múi giờ tường (Wall-clock time) trước khi cắt DATE để không bị mất mát số liệu vào những giờ đầu ngày do chênh lệch UTC+7.
Khai thác hàm Time-series: Sử dụng thành thạo DATE_TRUNC, DATETIME_DIFF và GENERATE_DATE_ARRAY để phục vụ các bài toán Business Intelligence chuyên sâu.

Nhưng trong các mô hình phân tích dữ liệu nâng cao, ngoài việc nhóm theo thời gian, chúng ta thường xuyên phải đối mặt với các bài toán Khử trùng lặp dữ liệu (Deduplication) và Xếp hạng xu hướng (Ranking) mà không làm mất đi các dòng dữ liệu chi tiết.

Làm thế nào để làm chủ các hàm cửa sổ phân tích đỉnh cao trong GoogleSQL? Hãy đón xem Bài học 16: Window Functions (Analytic Functions) - Phần 1: Thực chiến với ROW_NUMBER(), RANK(), DENSE_RANK() để khử trùng lặp!

bigquery

1. Ôn lại "Tứ trụ" kiểu dữ liệu Thời gian trong GoogleSQL

2. Cạm bẫy kinh điển: Tại sao bảng doanh thu của bạn lại lệch số vào đầu ngày?

Thảm họa xảy ra khi viết SQL:

3. Bí quyết xử lý chuẩn xác múi giờ Việt Nam (Asia/Ho_Chi_Minh)

4. Bộ công cụ Time-series thượng thừa trong GoogleSQL

4.1. Hàm làm tròn mốc thời gian: TIMESTAMP_TRUNC / DATE_TRUNC

4.2. Hàm tính khoảng cách: TIMESTAMP_DIFF / DATETIME_DIFF

4.3. Tạo chuỗi thời gian liên tục với GENERATE_ARRAY hoặc UNNEST(GENERATE_DATE_ARRAY(...))