So sánh kiểu ngày tháng trong sql

Question

Để 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"

Nội dung chính Show

Các hàm xử lý date/time
Các kiểu dữ liệu ngày tháng trong SQL
Theo dõi tạo hàng hoặc thời gian chỉnh sửa

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

Hình 1. Kết quả 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

from raw

Hình 2. Sử dụng extract kết hợp epoch để lấy thời gian ở dạ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

Datetime trong SQL là gì? Cách dùng hàm Date trong SQL như thế nào? Hãy cùng Quantrimang.com tìm hiểu nhé!

SQL là ngôn ngữ lập trình không còn xa lạ với nhiều người. Học và thực hành nó không khó một khi bạn đã nắm được các kiến thức cơ bản.

Nhìn chung, làm việc với cơ sở dữ liệu khá đơn giản vì người dùng cần biết các truy vấn và từ khóa cơ bản nên được sử dụng để truy xuất, cập nhật và xóa dữ liệu trong database. SQL cho phép người dùng lưu trữ dữ liệu có cấu trúc (dữ liệu ở dạng hàng và cột) và cung cấp các truy vấn đơn giản để làm việc trên đó. Ở bài viết này, chúng ta sẽ thảo luận về Hàm Ngày Tháng trong SQL.

Hầu hết lập trình viên đều cần làm việc với kiểu dữ liệu date/time trong SQL. Đôi khi, đây có thể là một vấn đề phức tạp, khó giải quyết với lập trình viên SQL còn thiếu kinh nghiệm. Giả sử bạn có bảng Sản phẩm với một cột thời gian (timestamp). Nó tạo ra từng mốc thời gian cho từng đơn hàng của khách. Khi xử lý bảng này, có thể bạn phải đối mặt với những vấn đề sau:

Không chèn được dữ liệu vào bảng Sản phẩm vì ứng dụng cố gắng chèn dữ liệu ở định dạng ngày tháng khác.
Giả sử bạn có dữ liệu trong bảng ở định dạng YYYY-MM-DD hh:mm: ss. Bạn có báo cáo doanh thu hàng ngày và trong đó, bạn muốn nhóm dữ liệu theo ngày tháng. Bạn muốn có dữ liệu trong báo cáo ở định dạng YYYY-MM-DD.

Đây chỉ là một trong số nhiều trường hợp phổ biến khi lập trình bằng SQL nhưng không có định dạng ngày tháng theo yêu cầu. Lúc này, thật khó thay đổi thuộc tính bảng để đáp ứng từng điều kiện. Trong trường hợp này bạn cần biết cách dùng hàm thời gian trong SQL.

Ngày tháng hay Date là một trong số chức năng quan trọng nhất được sử dụng ở SQL, nhưng đối với những người mới tiếp cận ngôn ngữ lập trình này thì hơi khó hiểu vì có nhiều định dạng ngày tháng có thể được lưu trữ trong cơ sở dữ liệu và ở các định dạng khác nhau mà người dùng muốn truy xuất nó theo yêu cầu. Trong SQL, DateTime thường được sử dụng để lưu trữ cả giá trị ngày và giờ tại một thời điểm trong một cột.

Dưới đây là chi tiết các hàm ngày tháng hay date trong SQL.

Các hàm xử lý date/time

STTHÀMMÔ TẢ1Thêm một khoảng thời gian vào date2Thêm một khoảng thời gian vào time3Chuyển đổi múi giờ4Trả về ngày hiện tại5Trả về ngày hiện tại6Trả về thời gian hiện tại7Trả về ngày và thời gian hiện tại8Trả về thời gian hiện tại9Cộng thời gian10Định dạng giá trị thời gian11Trừ thời gian12Trả về phần ngày của biểu thức thời gian13Trả về chênh lệch giữa hai giá trị thời gian14Trả về thứ tự ngày trong tháng (từ 0 đến 31)15Trả về tên của ngày trong tuần16Trả về thứ tự ngày trong tháng (từ 0 đến 31)17Trả về chỉ số ngày trong tuần (1 = Sunday, 2 = Monday... 7 = Saturday)18Trả về thứ tự ngày trong năm (từ 1 đến 366)19Trích giá trị thời gian từ biểu thức ban đầu20Chuyển đổi một số thành giá trị ngày21Trả về ngày đại diện cho tham số Unixtime ban đầu22Trả về phần giờ từ biểu thức thời gian23Trả về giá trị tương ứng cho ngày cuối cùng của tháng24Trả về ngày giờ hiện tại25Trả về ngày giờ hiện tại26Trả về một ngày với các tham số truyền vào27Trả về giá trị thời gian với các tham số truyền vào28Trả về microsecond từ biểu thức ban đầu29Trả về giá trị phút từ biểu thức thời gian30Trả về giá trị tháng từ biểu thức thời gian31Trả về tên tháng từ biểu thức thời gian32Trả về ngày giờ hiện tại33Thêm một khoảng thời gian cho tháng trong năm34Trả về số tháng giữa các khoảng thời gian35Trả về giá trị quý từ biểu thức thời gian36Chuyển đổi số giây sang định dạng 'HH: MM: SS'37Trả về giá trị giây từ biểu thức thời gian38Chuyển đổi một chuỗi thành một ngày39Trừ một khoảng thời gian từ date truyền vào40Trừ hai khoảng thời gian41Trả về ngày giờ hiện tại42Định dạng giá trị thời gian43Trả về số giây được chuyển đổi từ tham số ban đầu44Trả về giá trị thời gian từ biểu thức ban đầu45Trả về chênh lệch giữa hai giá trị thời gian46Trả về biểu thức datetime47Cộng khoảng thời gian được chỉ định vào tham số ban đầu48Trả về một số nguyên thể hiện sự chênh lệch thời gian giữa hai biểu thức49Trả về số ngày giữa giá trị thời gian date và năm 050Trả về số giây theo Unix timestamp từ biểu thức ban đầu51Trả về ngày UTC hiện tại52Trả về giờ UTC hiện tại53Trả về ngày giờ UTC hiện tại54Trả về chỉ số ngày trong tuần (0 = Thứ Hai, 1 = Thứ Ba... 6 = Chủ Nhật)55Trả về chỉ số tuần trong năm của biểu thức thời gian56Trả về năm tương ứng của tham số ban đầu

Các kiểu dữ liệu ngày tháng trong SQL

MySQL đi kèm với các kiểu dữ liệu để lưu trữ giá trị ngày hoặc ngày tháng/thời gian trong cơ sở dữ liệu:

DATE – định dạng YYYY-MM-DD
DATETIME – định dạng: YYYY-MM-DD HH:MI:SS
TIMESTAMP – định dạng: YYYY-MM-DD HH:MI:SS
YEAR – định dạng YYYY hoặc YY

Với:

Y là năm
M là tháng
D là ngày
H là giờ
MM là phút
SS là giây

YYYY là năm được lưu dưới dạng bốn chữ số, YY là năm được lưu dưới dạng hai chữ số; MM là lưu tháng dưới dạng hai chữ số, ví dụ tháng 7 sẽ là 07):

Theo dõi tạo hàng hoặc thời gian chỉnh sửa

Trong khi làm việc với database của một ứng dụng lớn, bạn thường cần phải lưu trữ thời gian tạo bản ghi hoặc lần chỉnh sửa cuối cùng trong database, ví dụ, lưu ngày & giờ khi người dùng đăng ký, hoặc khi người dùng update mật khẩu gần nhất…

Trong MySQL, bạn có thể dùng hàm NOW() để chèn dấu thời gian hiện tại như sau:

-- Syntax for MySQL Database 
INSERT INTO users (name, birth_date, created_at)
VALUES ('Bilbo Baggins', '1998-04-16', NOW());

Tuy nhiên, nếu không muốn tự tay chèn ngày và giờ hiện tại, bạn có thể chỉ dùng thuộc tính auto-initialization và auto-update của TIMESTAMP và DATETIME.

Để gắn thuộc tính tự động, xác định mệnh đề DEFAULT CURRENT_TIMESTAMP và ON UPDATE CURRENT_TIMESTAMP trong định nghĩa cột như sau:

-- Syntax for MySQL Database 
CREATE TABLE users (
    id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    birth_date DATE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Trong phần tiếp theo, Quantrimang sẽ cùng bạn tìm hiểu cách sử dụng tất cả các hàm nói trên với cú pháp và những ví dụ cụ thể, các bạn nhớ theo dõi nhé.

đánh giá So Sánh So sánh Datetime trong SQL