Hướng dẫn ngày và giờ MySQL: Kiểu dữ liệu, hàm, định dạng và truy vấn phạm vi

1. Giới thiệu

MySQL là một hệ quản trị cơ sở dữ liệu được sử dụng rộng rãi để xây dựng ứng dụng web và cơ sở dữ liệu, và việc xử lý dữ liệu ngày tháng một cách đúng đắn đặc biệt quan trọng. Ví dụ, dữ liệu liên quan đến ngày tháng xuất hiện trong nhiều tình huống: quản lý bài đăng blog, theo dõi lịch sử bán hàng sản phẩm, và lưu trữ lịch sử đăng nhập người dùng. Bằng cách quản lý dữ liệu ngày tháng đúng cách, bạn có thể xử lý dữ liệu hiệu quả và cung cấp thông tin chính xác cho người dùng.

Trong hướng dẫn này, chúng tôi sẽ giải thích toàn diện mọi thứ từ các kiểu dữ liệu ngày/giờ cơ bản và cách sử dụng các hàm ngày trong MySQL đến các phép tính dựa trên ngày và truy vấn phạm vi. Nội dung này được thiết kế cho người dùng từ sơ cấp đến trung cấp, và chúng tôi sẽ bao gồm các ví dụ truy vấn thực tế dựa trên các trường hợp sử dụng thực tế—làm cho nó hữu ích cho công việc hàng ngày.

Bằng cách đọc bài viết này, bạn sẽ có thể thực hiện những điều sau:

• Hiểu đặc điểm của các kiểu dữ liệu ngày/giờ MySQL và chọn kiểu phù hợp cho dữ liệu của bạn.
• Sử dụng các hàm ngày để dễ dàng lấy và thao tác ngày hiện tại, ngày quá khứ và ngày tương lai.
• Trích xuất dữ liệu cho các khoảng thời gian cụ thể bằng cách thực hiện tìm kiếm và so sánh phạm vi ngày.

Bây giờ, hãy xem xét các nguyên tắc cơ bản của ngày MySQL bắt đầu từ phần tiếp theo.

2. Tổng quan về các Kiểu Dữ liệu Ngày/Giờ MySQL

Khi quản lý ngày trong MySQL, việc chọn kiểu dữ liệu ngày/giờ phù hợp tùy thuộc vào dữ liệu và trường hợp sử dụng là rất quan trọng. MySQL cung cấp nhiều kiểu dữ liệu để xử lý ngày và giờ, mỗi kiểu có đặc điểm và cách sử dụng khác nhau. Trong phần này, chúng ta sẽ giải thích chi tiết các kiểu ngày/giờ chính và các trường hợp sử dụng điển hình của chúng.

DATE

Kiểu DATE chỉ lưu trữ ngày (năm, tháng, ngày) và không bao gồm thành phần thời gian. Phạm vi hỗ trợ từ “1000-01-01” đến “9999-12-31,” vì vậy nó có thể xử lý ngày từ năm 1000 đến 9999. Nó phù hợp cho thông tin chỉ ngày nơi thời gian không liên quan, chẳng hạn như ngày sinh hoặc kỷ niệm.

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    birth_date DATE
);

TIME

Kiểu TIME lưu trữ thời gian (giờ, phút, giây). Phạm vi hỗ trợ từ “-838:59:59” đến “838:59:59.” Vì nó có thể biểu diễn thời gian âm, nó cũng có thể được sử dụng để biểu thị sự khác biệt thời gian. Nó hữu ích cho việc ghi lại giờ làm việc hoặc thời lượng nhiệm vụ.

CREATE TABLE work_log (
    id INT,
    task_name VARCHAR(50),
    duration TIME
);

DATETIME

Kiểu DATETIME lưu trữ cả ngày và thời gian. Phạm vi hỗ trợ từ “1000-01-01 00:00:00” đến “9999-12-31 23:59:59.” Nó không phụ thuộc vào múi giờ, và bạn có thể lấy giá trị đã lưu chính xác như đã lưu. Nó phù hợp cho việc ghi lại dấu thời gian của các sự kiện quá khứ hoặc lịch trình tương lai.

CREATE TABLE appointments (
    id INT,
    description VARCHAR(50),
    appointment_datetime DATETIME
);

TIMESTAMP

Kiểu TIMESTAMP lưu trữ ngày và thời gian và tự động chuyển đổi giá trị dựa trên múi giờ máy chủ. Phạm vi hỗ trợ từ “1970-01-01 00:00:01 UTC” đến “2038-01-19 03:14:07 UTC.” Nó tương thích với thời gian epoch Unix và phù hợp cho việc ghi lại dấu thời gian và lịch sử thay đổi. Ngoài ra, vì TIMESTAMP có thể đặt thời gian hiện tại làm mặc định, nó tiện lợi cho việc tự động ghi lại thời gian tạo và cập nhật.

CREATE TABLE posts (
    id INT,
    title VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

YEAR

Kiểu YEAR chỉ lưu trữ năm. Phạm vi hỗ trợ từ “1901” đến “2155.” Nó hữu ích khi bạn muốn biểu thị một năm cụ thể, chẳng hạn như năm sản xuất hoặc năm thành lập.

CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    manufactured_year YEAR
);

So sánh và Các Trường hợp Sử dụng của Mỗi Kiểu Ngày/Giờ

Tóm tắt

MySQL date/time data types should be chosen based on the characteristics of your data and how you plan to use it. In the next section, we’ll take a closer look at the differences between DATETIME and TIMESTAMP, which are especially easy to confuse.

3. Sự Khác Biệt Giữa DATETIME và TIMESTAMP

Khi làm việc với ngày và giờ trong MySQL, DATETIME và TIMESTAMP được sử dụng phổ biến, nhưng có những sự khác biệt quan trọng giữa chúng. Cả hai đều lưu trữ năm, tháng, ngày, giờ, phút và giây, nhưng chúng khác nhau về cách xử lý múi giờ và phạm vi thời gian mà chúng có thể lưu trữ. Bạn nên chọn dựa trên trường hợp sử dụng của mình. Trong phần này, chúng ta sẽ giải thích chi tiết sự khác biệt và đặc điểm của DATETIME và TIMESTAMP.

Đặc Điểm Của DATETIME

• Không phụ thuộc vào múi giờ : Kiểu DATETIME không bị ảnh hưởng bởi cài đặt múi giờ của máy chủ. Bạn có thể lấy lại giá trị đã lưu chính xác như đã lưu.
• Phạm vi : Nó hỗ trợ từ 1000-01-01 00:00:00 đến 9999-12-31 23:59:59.
• Trường hợp sử dụng : Phù hợp cho dữ liệu bạn muốn lưu trữ mà không phụ thuộc vào múi giờ cụ thể nào, chẳng hạn như sự kiện lịch sử hoặc lịch trình tương lai.

Ví Dụ: Lưu Trữ Ngày/Giờ Sự Kiện

Ví dụ, nếu bạn muốn giữ giá trị ngày/giờ “phổ quát” như hiện tại, DATETIME là lựa chọn tốt. Ví dụ sau ghi lại một sự kiện được lên lịch tại một ngày và giờ cụ thể.

CREATE TABLE events (
    id INT,
    event_name VARCHAR(50),
    event_datetime DATETIME
);

Trong bảng này, ngày/giờ được lưu trữ trong event_datetime không bị ảnh hưởng bởi múi giờ, và giá trị được lấy lại chính xác như đã lưu.

Đặc Điểm Của TIMESTAMP

• Phụ thuộc vào múi giờ : Kiểu TIMESTAMP được lưu trữ và lấy lại dựa trên múi giờ của máy chủ. Khi di chuyển dữ liệu giữa các máy chủ ở múi giờ khác nhau, chuyển đổi sẽ xảy ra tương ứng.
• Phạm vi : Nó hỗ trợ từ 1970-01-01 00:00:01 UTC đến 2038-01-19 03:14:07 UTC (dựa trên phạm vi thời gian Unix).
• Hỗ trợ tự động cập nhật : Sử dụng DEFAULT CURRENT_TIMESTAMP và ON UPDATE CURRENT_TIMESTAMP, MySQL có thể tự động ghi lại dấu thời gian hiện tại khi chèn hoặc cập nhật dữ liệu.
• Trường hợp sử dụng : Tốt nhất cho việc theo dõi tự động thời gian tạo hoặc cập nhật, chẳng hạn như khi bạn muốn dấu thời gian thay đổi mỗi khi một bản ghi được cập nhật.

Ví Dụ: Lưu Trữ Thời Gian Tạo Và Cập Nhật Cho Các Bài Đăng

Ví dụ này sử dụng tính năng tự động cập nhật của TIMESTAMP để ghi lại thời gian tạo và cập nhật cho các bài đăng blog.

CREATE TABLE blog_posts (
    id INT,
    title VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Với cài đặt này, created_at được ghi lại khi bài đăng được tạo lần đầu, và updated_at được tự động cập nhật mỗi khi bài đăng được chỉnh sửa.

Bảng So Sánh: DATETIME so Với TIMESTAMP

Cách Chọn

• Chọn DATETIME nếu bạn không muốn ảnh hưởng của múi giờ : Ví dụ, nếu bạn muốn lưu trữ thời gian sự kiện như một thời gian địa phương cố định cho một quốc gia/khu vực cụ thể, DATETIME có thể phù hợp.
• Chọn TIMESTAMP nếu bạn cần tự động cập nhật hoặc xử lý múi giờ : Ví dụ, nếu bạn muốn tự động ghi lại khi các hàng cơ sở dữ liệu được cập nhật, TIMESTAMP rất tiện lợi.

Tóm Tắt

DATETIME và TIMESTAMP có những đặc điểm khác nhau. Bằng cách chọn cái phù hợp với mục đích của bạn, bạn có thể quản lý dữ liệu ngày/giờ hiệu quả hơn. Trong phần tiếp theo, chúng ta sẽ xem xét các hàm ngày thiết yếu trong MySQL.

4. Cơ Bản Về Các Hàm Ngày/Giờ MySQL

MySQL cung cấp nhiều hàm để làm việc với ngày và giờ—từ việc lấy ngày/giờ hiện tại đến việc cộng hoặc trừ khoảng thời gian. Những hàm này hữu ích cho việc quản trị và phân tích cơ sở dữ liệu. Trong phần này, chúng ta sẽ giải thích cơ bản về các hàm ngày/giờ MySQL và các trường hợp sử dụng điển hình của chúng.

Các Hàm Để Lấy Ngày/Giờ Hiện Tại

Ba hàm phổ biến để lấy ngày và giờ hiện tại trong MySQL là NOW(), CURDATE(), và CURTIME().

NOW()

Hàm NOW() trả về ngày và giờ hiện tại theo định dạng YYYY-MM-DD HH:MM:SS. Nó hữu ích cho việc ghi log và đánh dấu thời gian cho các bản ghi.

SELECT NOW(); -- Get the current date and time

CURDATE()

Hàm CURDATE() trả về ngày hiện tại theo định dạng YYYY-MM-DD. Nó không bao gồm thời gian, vì vậy phù hợp khi bạn chỉ cần ngày.

SELECT CURDATE(); -- Get the current date

CURTIME()

Hàm CURTIME() trả về thời gian hiện tại theo định dạng HH:MM:SS. Sử dụng nó khi bạn chỉ cần thời gian mà không có ngày.

SELECT CURTIME(); -- Get the current time

Các hàm để cộng/trừ ngày

Để cộng hoặc trừ khoảng thời gian từ một ngày, sử dụng DATE_ADD() và DATE_SUB(). Các hàm này giúp dễ dàng tính toán ngày trong tương lai hoặc quá khứ.

DATE_ADD()

Hàm DATE_ADD() cộng một khoảng thời gian được chỉ định vào một ngày. Ví dụ, nó hữu ích để lấy ngày 7 ngày sau hoặc 1 tháng sau.

SELECT DATE_ADD('2023-01-01', INTERVAL 7 DAY); -- Returns 2023-01-08

DATE_SUB()

Hàm DATE_SUB() trừ một khoảng thời gian được chỉ định từ một ngày. Sử dụng nó để tính toán ngày trong quá khứ.

SELECT DATE_SUB('2023-01-01', INTERVAL 1 MONTH); -- Returns 2022-12-01

Hàm để tính toán sự khác biệt giữa các ngày

Để tính toán sự khác biệt giữa hai ngày, DATEDIFF() rất tiện lợi. Ví dụ, bạn có thể tính toán số ngày đã trôi qua kể từ một ngày cụ thể, hoặc số ngày giữa hai ngày.

DATEDIFF()

Hàm DATEDIFF() trả về sự khác biệt giữa hai ngày theo đơn vị ngày. Nó hữu ích khi bạn muốn xác nhận số ngày từ ngày bắt đầu đến ngày kết thúc.

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

Các hàm ngày khác hữu ích

MySQL cung cấp các hàm ngày bổ sung hữu ích.

EXTRACT()

Hàm EXTRACT() trích xuất một phần cụ thể (năm, tháng, ngày, v.v.) từ một ngày. Nó hữu ích khi bạn chỉ cần một phần của ngày.

SELECT EXTRACT(YEAR FROM '2023-01-01'); -- Extract year (2023)
SELECT EXTRACT(MONTH FROM '2023-01-01'); -- Extract month (1)
SELECT EXTRACT(DAY FROM '2023-01-01'); -- Extract day (1)

DATE_FORMAT()

Hàm DATE_FORMAT() hiển thị một ngày theo định dạng được chỉ định. Nó hữu ích khi bạn muốn hiển thị ngày theo định dạng cụ thể (ví dụ, định dạng kiểu Nhật Bản).

SELECT DATE_FORMAT('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01

5. Các phương pháp định dạng và chuyển đổi ngày

Trong MySQL, bạn có thể thay đổi cách hiển thị ngày thành định dạng dễ đọc hơn, hoặc chuyển đổi chuỗi ngày thành kiểu ngày. Điều này cho phép bạn kiểm soát linh hoạt định dạng hiển thị và quản lý dữ liệu nhất quán ngay cả khi đầu vào có các định dạng khác nhau. Trong phần này, chúng ta sẽ giải thích các hàm chính được sử dụng cho định dạng và chuyển đổi ngày.

Định dạng ngày với hàm DATE_FORMAT()

Sử dụng DATE_FORMAT(), bạn có thể hiển thị giá trị ngày theo định dạng được chỉ định. Điều này đặc biệt hữu ích khi bạn cần định dạng hiển thị khác với định dạng chuẩn, chẳng hạn như định dạng “YYYY年MM月DD日” kiểu Nhật Bản.

Các tùy chọn định dạng

Với DATE_FORMAT(), bạn có thể sử dụng các tùy chọn định dạng như sau:

• %Y : năm 4 chữ số
• %y : năm 2 chữ số
• %m : tháng 2 chữ số (01–12)
• %d : ngày 2 chữ số (01–31)
• %H : giờ 2 chữ số (00–23)
• %i : phút 2 chữ số (00–59)
• %s : giây 2 chữ số (00–59)

Ví dụ sử dụng

Ví dụ, để hiển thị ngày 2023-01-01 dưới dạng “2023年01月01日,” bạn có thể viết như sau:

SELECT DATE_FORMAT('2023-01-01', '%Y年%m月%d日'); -- Returns 2023年01月01日

Bạn cũng có thể hiển thị ngày và giờ với dấu gạch chéo, như “2023/01/01 12:30:45.”

SELECT DATE_FORMAT('2023-01-01 12:30:45', '%Y/%m/%d %H:%i:%s'); -- Returns 2023/01/01 12:30:45

Chuyển đổi chuỗi thành ngày với STR_TO_DATE()

Hàm STR_TO_DATE() chuyển đổi một chuỗi ngày thành kiểu ngày của MySQL. Ví dụ, nó hữu ích khi bạn muốn xử lý một chuỗi như “2023年01月01日” thành giá trị DATE.

Ví dụ sử dụng

Để chuyển đổi chuỗi “2023-01-01” thành giá trị DATE, viết như sau:

SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01 as a DATE

Để chuyển đổi một chuỗi kiểu Nhật như “2023年01月01日” thành ngày, chỉ định định dạng tương ứng:

SELECT STR_TO_DATE('2023年01月01日', '%Y年%m月%d日'); -- Returns 2023-01-01 as a DATE

Ví dụ chuyển đổi ngày từ các định dạng khác nhau

Trong thực tế, các định dạng nhập ngày có thể đa dạng, vì vậy bạn có thể cần chuyển đổi từ các định dạng khác nhau. Dưới đây là một vài ví dụ.

1. Chuyển “YYYY/MM/DD” thành giá trị DATE

   SELECT STR_TO_DATE('2023/01/01', '%Y/%m/%d'); -- Returns 2023-01-01 as a DATE
   

2. Chuyển “MM-DD-YYYY” thành giá trị DATE

   SELECT STR_TO_DATE('01-01-2023', '%m-%d-%Y'); -- Returns 2023-01-01 as a DATE
   

Bằng cách này, ngay cả khi dữ liệu được nhập ở các định dạng khác nhau, bạn vẫn có thể lưu và quản lý chúng một cách nhất quán trong một định dạng ngày thống nhất.

Sự khác nhau giữa DATE_FORMAT và STR_TO_DATE, và khi nào nên sử dụng mỗi cái

• DATE_FORMAT() : Được dùng để thay đổi định dạng hiển thị của các giá trị ngày hiện có. Vì sự thay đổi chỉ ảnh hưởng đến việc hiển thị, dữ liệu được lưu trữ không bị thay đổi.
• STR_TO_DATE() : Được dùng để chuyển đổi một chuỗi ngày thành giá trị DATE hoặc DATETIME của MySQL. Điều này thay đổi kiểu dữ liệu được lưu và giúp dễ dàng xử lý ngày với các hàm và truy vấn MySQL khác.

Tóm tắt

Bằng cách sử dụng DATE_FORMAT() và STR_TO_DATE(), bạn có thể linh hoạt quản lý cách dữ liệu ngày được hiển thị và lưu trữ. Điều này cho phép xử lý đầu vào của người dùng hoặc hệ thống một cách linh hoạt hơn trong khi duy trì quản lý ngày nhất quán. Trong phần tiếp theo, chúng tôi sẽ giải thích các phép tính và so sánh ngày, và xem xét chi tiết cách tính và so sánh các khoảng thời gian bằng dữ liệu ngày.

6. Các phép tính và so sánh ngày

MySQL cung cấp một số hàm tiện lợi cho các phép tính và so sánh ngày. Những hàm này cho phép trích xuất dữ liệu cho các khoảng thời gian cụ thể hoặc tính toán sự chênh lệch ngày cho việc phân tích. Trong phần này, chúng tôi sẽ giới thiệu các hàm chính được sử dụng cho các phép tính và so sánh ngày, cùng cách sử dụng chúng.

Hàm tính chênh lệch ngày: DATEDIFF()

Hàm DATEDIFF() trả về sự chênh lệch giữa hai ngày tính bằng “ngày”. Nó hữu ích khi bạn muốn kiểm tra số ngày đã trôi qua giữa các ngày hoặc xác định bao nhiêu ngày đã qua từ một sự kiện trong quá khứ đến hôm nay.

Ví dụ sử dụng

Ví dụ, để tính số ngày từ ngày 1 tháng 1 năm 2023 đến ngày 10 tháng 1 năm 2023, viết như sau:

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

Trong ví dụ này, sự chênh lệch từ ngày bắt đầu đến ngày kết thúc là 9 ngày, vì vậy kết quả là 9.

Tính chênh lệch theo đơn vị với TIMESTAMPDIFF()

Hàm TIMESTAMPDIFF() tính toán sự chênh lệch giữa hai ngày hoặc datetime theo một đơn vị được chỉ định (năm, tháng, ngày, giờ, phút, giây). Ví dụ, bạn có thể tính sự chênh lệch theo “tháng” hoặc “giờ” tùy nhu cầu.

Ví dụ sử dụng

1. Tính sự chênh lệch theo tháng

   SELECT TIMESTAMPDIFF(MONTH, '2022-01-01', '2023-01-01'); -- Returns 12
   

2. Tính sự chênh lệch theo giờ

   SELECT TIMESTAMPDIFF(HOUR, '2023-01-01 00:00:00', '2023-01-02 12:00:00'); -- Returns 36
   

So sánh ngày

So sánh ngày được thực hiện bằng các toán tử so sánh tiêu chuẩn của MySQL (<, >, <=, >=, =). Điều này cho phép bạn trích xuất dữ liệu trong một khoảng thời gian cụ thể hoặc bao gồm các ngày trong quá khứ/tương lai như các điều kiện.

Ví dụ sử dụng

Ví dụ, để trích xuất dữ liệu “từ ngày 1 tháng 1 năm 2023 trở đi”, viết như sau:

SELECT * FROM events WHERE event_date >= '2023-01-01';

Xác định phạm vi với BETWEEN

.The BETWEEN operator cho phép bạn chỉ định một khoảng thời gian để truy xuất dữ liệu. Nó hữu ích khi bạn muốn trích xuất các bản ghi nằm trong một khoảng thời gian cụ thể.

Example Usage

Ví dụ, để truy xuất dữ liệu từ ngày 1 tháng 1 năm 2023 đến ngày 31 tháng 1 năm 2023, viết như sau:

SELECT * FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31';

Tính toán ngày với ADDDATE() và SUBDATE()

Bạn có thể sử dụng ADDDATE() và SUBDATE() để cộng hoặc trừ một số ngày từ một ngày nhất định. Chúng rất tiện lợi cho việc tính toán các ngày trong tương lai hoặc quá khứ.

Example Usage

1. Thêm 10 ngày vào một ngày

   SELECT ADDDATE('2023-01-01', 10); -- Returns 2023-01-11
   

2. Trừ 10 ngày khỏi một ngày

   SELECT SUBDATE('2023-01-01', 10); -- Returns 2022-12-22
   

Tóm tắt

Bằng cách sử dụng các tính năng tính toán và so sánh ngày của MySQL, bạn có thể hiệu quả trong việc trích xuất dữ liệu cho các khoảng thời gian cụ thể và phân tích dữ liệu theo các dải thời gian. Trong phần tiếp theo, chúng ta sẽ xem xét kỹ hơn về chủ đề nâng cao hơn của “tìm kiếm theo khoảng ngày”.