So sánh ngày tháng năm trong sql năm 2024

Để bắt đầu với dữ liệu thời gian, tất nhiên là chúng ta sẽ cần một vài cột dữ liệu là thời gian ở Database (db) đúng không nào? Tuy nhiên, do một số lý do (thiết kế bảng chưa thích hợp, chưa có thời gian để chỉnh sửa db,...), đôi khi cột thời gian lại không được nhận dạng đúng là "thời gian" (timestamp, datetime, date, interval...) mà lại ở dạng "string"

Chuyển đổi dữ liệu dạng string sang "thời gian":

• Sử dụng cast:

select cast(inbound_date as date) inbound_date from order_details

• Sử dụng "::" (postgres)

select (inbound_date||' '||inbound_time)::timestamp as inbound_datetime from order_details

Khi đã có cho mình được cột dữ liệu thời gian, có rất nhiều hàm khác nhau hỗ trợ xử lý chúng theo nhu cầu sử dụng

Lấy một thành phần Ngày/Tháng/Năm/Tuần/Giờ/Phút/Giây/...:

• date_part

select date_part('day',inbound_datetime) as inbound_day from raw; select date_part('month',inbound_datetime) as inbound_month from raw; select date_part('week',inbound_datetime) as inbound_week from raw; ...

• extract

select extract(month from inbound_datetime) as month from raw ...

Lấy Thành phần thời gian đại diện (lấy ngày đầu của tháng, ngày đầu của tuần, ngày cuối của tuần, chỉ lấy ngày bỏ qua giờ,...)

• date_trunc

select date_trunc('month', pickup_datetime) as start_of_month from raw; select date_trunc('week', pickup_datetime) as start_of_week from raw; select (date_trunc('week', pickup_datetime + interval '7' day) - interval '1' day) as end_of_week from raw; --get first day of next week then minus 1 day select date_trunc('day', pickup_datetime) as pickup_date from raw;

Lấy thời gian cách nhau giữa hai thời điểm

• Có thể sử dụng dấu trừ trực tiếp

select (inbound_datetime - pickup_datetime) as time_gap from raw

Hình 1. Kết quả khi trừ trực tiếp hai cột timestamp

• Sử dụng các hàm diff để lấy khoảng cách thời gian (Big Query)

select timestamp_diff(current_timestamp,matching_time, hour) gap_as_hour from transactions; select datetime_diff(current_datetime,matching_time, day) as gap_as_day from transactions; ...

Chuyển đổi kiểu interval sang float:

Như từ hình 1, khi trừ trực tiếp thời gian với nhau, ta nhận được kiểu dữ liệu interval, nhưng đôi khi với hiển thị như thế sẽ khiến chúng quá dài để xem, khó tính toán... Lúc ấy ta có thể đổi kiểu interval sang dạng số dễ nhìn hơn --> float, sử dụng extract kết hợp epoch

select extract(epoch from (inbound_datetime - pickup_datetime)) as gap_as_second

    ,extract(epoch from (inbound_datetime - pickup_datetime))/60 as gap_as_minute
    ,extract(epoch from (inbound_datetime - pickup_datetime))/3600 as gap_as_hour
    ,extract(epoch from (inbound_datetime - pickup_datetime))/86400 as gap_as_day

Hình 2. Sử dụng extract kết hợp epoch để lấy thời gian ở dạng số thập phân

Ý tưởng của epoch dựa trên Unix time, tham khảo thêm ở link: https://en.wikipedia.org/wiki/Unix_time

Xử lý dữ liệu thời gian thông minh hơn ở câu lệnh WHERE

KISS - Keep It Simple, Stupid!

Giả sử db đang lưu thời gian theo giờ UTC, để chuyển sang giờ Việt Nam, ta cần cộng thêm cho nó 7 tiếng, và ta chỉ quan tâm dữ liệu từ tháng 6 trở đi, ta có thể viết:

-- cach 1 select * from raw where inbound_datetime + interval '7' hour >= '2021-06-01'

Hoặc một cách viết khác tương đương:

cach 2 select * from raw where inbound_datetime >= '2021-06-01' - interval '7' hour chuyển vế đổi dấu thui

Tuy cách viết thứ 2 đơn giản chỉ là chuyển vế đổi dấu, nhưng cách này sẽ mang đến một hiệu suất tốt hơn cho câu query, hay có thể nói là db "đỡ cực" hơn khi ta viết theo cách thứ hai, vì sau ư? Hãy suy nghĩ như một cỗ máy nào?

Giả sử bảng dữ liệu của bạn có 10 triệu dòng

• Cách 1: Tôi sẽ thực hiện phép tính (inbound_datetime + interval '7' hour) 10 triệu lần và so sánh với ngày '2021-06-01'
• Cách 2: Tôi sẽ thực hiện phép tính ('2021-06-01' - interval '7' hour) 1 lần và so sánh cột inbound với kết quả phép tính ấy...

Vì thế, nếu có thể, hãy đặt tất cả phép tính toán về phía còn lại so với cột dữ liệu.

Tương tự với cùng một bài toán là lấy ra những đơn có inbound_time vào tháng 7, ta có nhiều cách viết khác nhau nhưng lại cho ra cùng 1 kết quả như sau:

select (inbound_date||' '||inbound_time)::timestamp as inbound_datetime from order_details

0

Tôi của ngày trước hay dùng cách 1, giờ thì tôi chỉ dùng cách 3, không xử lý gì đối với cột dữ liệu là tốt nhất