Bẻ khóa các hàm ngày tháng trong SQLite

Một số hàm hữu ích nhất trong các triển khai SQL của Postgres (như Amazon Redshift) là DATE_DIFF và DATE_TRUNC:

• DATE_DIFF cho biết khoảng thời gian đã trôi qua giữa hai ngày khác nhau. Ví dụ, đoạn mã sau sẽ cho số ngày giữa date1 và date2:

DATE_DIFF('day', date1, date2)
Được hỗ trợ bởi

DATE_DIFF rất hữu ích để tính số ngày từ lúc đăng ký đến lúc hủy hoặc số giờ từ lúc đăng nhập đến lúc đăng xuất.

• DATE_TRUNC cắt một ngày về mốc gần nhất theo ngày, tuần, tháng hoặc năm. Ví dụ, đoạn mã sau sẽ trả về thứ Hai gần nhất đối với dấu thời gian my_timestamp:

DATE_TRUNC('week', my_timestamp)
Được hỗ trợ bởi

DATE_TRUNC rất phù hợp để tổng hợp dữ liệu. Chẳng hạn, chúng ta có thể dùng nó để tìm số Người dùng hoạt động hàng tháng (MAU) bằng cách cắt về ngày bắt đầu tháng gần nhất.

Nhưng không phải mọi triển khai SQL đều có những hàm tuyệt vời này. Với mã Học SQL từ đầu của chúng tôi, chúng tôi dùng SQLite, một triển khai SQL nhẹ có thể chạy trên một phiên bản Docker đơn lẻ. SQLite rất phù hợp cho backend website và các dự án nhỏ, nhưng lại thiếu hai hàm yêu thích của tôi. May mắn là vẫn có cách xử lý.

Để mô phỏng DATE_DIFF, chúng ta có thể dùng một hàm ít người biết gọi là juliandate. Theo Wikipedia, “Julian Day Number (JDN) là số nguyên gán cho một ngày dương lịch trọn vẹn trong hệ đếm ngày Julian bắt đầu từ buổi trưa theo giờ Phối hợp Quốc tế, với số Julian day 0 được gán cho ngày bắt đầu lúc trưa Thứ Hai, ngày 1 tháng 1 năm 4713 TCN”. Bằng cách chuyển một ngày sang số thực dấu phẩy động, chúng ta có thể dùng phép trừ để tìm chênh lệch giữa hai dấu thời gian.

Chúng ta thậm chí có thể đổi kết quả sang giờ bằng cách nhân với 24 hoặc sang phút bằng cách nhân với 24 * 60.

Chúng ta có thể mô phỏng một phần chức năng của DATE_TRUNC bằng cách dùng strftime. Hàm này chuyển một dấu thời gian thành chuỗi theo định dạng cho trước.

%dngày trong tháng: 00 %fgiây thập phân: SS.SSS %Hgiờ: 00-24%jngày trong năm: 001-366 %JJulian day number%mtháng: 01-12 %Mphút: 00-59 %sgiây tính từ 1970-01-01 %Sgiây: 00-59 %wthứ trong tuần 0-6 với Chủ nhật==0 %Wtuần trong năm: 00-53 %Ynăm: 0000-9999

Thông thường, chúng ta có thể dùng hàm này để chuyển đổi giữa các định dạng dấu thời gian như YYYY-MM-DD sang MM-DD-YYYY:

strftime('%M-%D-%Y', mydate)
Được hỗ trợ bởi

Nhưng chúng ta có thể khéo léo chọn định dạng để cắt về mốc phù hợp. Ví dụ, nếu muốn cắt về mốc tháng gần nhất, ta có thể:

strftime('%M/%Y', mydate)
Được hỗ trợ bởi

Hoặc có thể cắt về mốc tuần gần nhất bằng cách dùng:

strftime('%Y-%w', mydate)
Được hỗ trợ bởi

Với hai mẹo đơn giản này, bạn có thể dùng SQLite để thực hiện một số phân tích tuyệt vời tương tự như trên Amazon Redshift!

Nếu bạn muốn tìm hiểu thêm về những điều cơ bản của SQL, hãy học khóa Intro to SQL for Data Science của DataCamp và xem Hướng dẫn SQL cho người mới bắt đầu của chúng tôi.