Ngày và giờ rất quan trọng, chúng giúp sắp xếp mọi thứ ngăn nắp và là một khía cạnh không thể thiếu của bất kỳ hoạt động phần mềm nào
Làm việc hiệu quả với chúng trong cơ sở dữ liệu đôi khi có vẻ khó hiểu, cho dù nó hoạt động trên các múi giờ khác nhau, cộng/trừ ngày và các hoạt động khác
Tìm hiểu các hàm MySQL khác nhau có sẵn để dễ dàng xử lý và quản lý ngày/giờ trong cơ sở dữ liệu của bạn
Làm việc với múi giờ
Để giúp mọi thứ được chuẩn hóa, bạn chỉ nên làm việc với ngày/giờ theo múi giờ UTC. Mỗi khi bạn thiết lập kết nối với cơ sở dữ liệu MySQL, bạn nên chuyển múi giờ sang UTC, điều này có thể được thực hiện bằng câu lệnh SQL sau
SET TIME_ZONE = '+0:00' Vì tất cả các ngày giờ sẽ được lưu ở dạng UTC, nên bạn luôn biết mình đang làm việc với cái gì, khiến mọi thứ trở nên đơn giản và dễ dàng hơn
Khi cần, bạn có thể dễ dàng chuyển đổi múi giờ của bất kỳ giá trị ngày giờ/dấu thời gian nào bằng hàm CONVERT_TZ[] tiện dụng của MySQL. Trước tiên, bạn cần biết phần bù, ví dụ: PST ở bờ biển phía tây của Bắc Mỹ là UTC -08. 00, vì vậy bạn có thể sử dụng
SELECT CONVERT_TZ['2021-02-04 21:47:23', '+0:00', '-8:00']; Điều này dẫn đến 2021-02-04 13. 47. 23 chính xác. Ba đối số được chuyển đến CONVERT_TZ[] đầu tiên là ngày giờ/dấu thời gian bạn đang bắt đầu [sử dụng now[] cho thời gian hiện tại], đối số thứ hai sẽ luôn là '+0. 00' vì tất cả các ngày đều buộc phải UTC trong cơ sở dữ liệu và ngày cuối cùng là phần bù mà chúng tôi muốn chuyển đổi ngày thành
Cộng/Trừ Ngày
Nhiều khi bạn cần cộng hoặc trừ ngày tháng, chẳng hạn như nếu bạn cần truy xuất hồ sơ từ một tuần trước hoặc lên lịch cho một tháng nào đó kể từ bây giờ
Rất may, MySQL có các hàm DATE_ADD[] và DATE_SUB[] tuyệt vời khiến nhiệm vụ này trở nên cực kỳ dễ dàng. Ví dụ: bạn có thể trừ hai tuần kể từ ngày hiện tại bằng câu lệnh SQL
SELECT DATE_SUB[now[], interval 2 week]; Thay vào đó, nếu bạn muốn thêm ba ngày vào dấu thời gian hiện có, bạn sẽ sử dụng
SELECT DATE_ADD['2021-02-07 11:52:06', interval 3 day]; Cả hai chức năng đều hoạt động như nhau, đối số đầu tiên là dấu thời gian bạn đang bắt đầu và đối số thứ hai là khoảng thời gian để cộng hoặc trừ. Đối số thứ hai luôn được định dạng giống nhau, bắt đầu bằng khoảng từ theo sau là một giá trị số và chính khoảng đó, có thể là bất kỳ giá trị nào sau đây. giây, phút, giờ, ngày, tuần, tháng, quý, năm
Ví dụ khác, nếu bạn muốn truy xuất tất cả các lần đăng nhập đã xảy ra trong 34 phút qua, bạn có thể sử dụng câu lệnh SQL chẳng hạn như
SELECT * FROM logins WHERE login_date >= DATE_SUB[now[], interval 45 minute]; Như bạn có thể thấy, thao tác này sẽ truy xuất tất cả các bản ghi từ bảng đăng nhập có ngày đăng nhập lớn hơn thời gian hiện tại trừ đi 45 phút hoặc nói cách khác là 45 phút trước
Nhận sự khác biệt giữa các ngày
Đôi khi bạn cần biết khoảng thời gian đã trôi qua giữa hai ngày. Bạn có thể dễ dàng lấy số ngày giữa hai ngày khác nhau bằng hàm DATEDIFF, chẳng hạn như câu lệnh SQL bên dưới
SELECT DATEDIFF[now[], '2020-12-15']; Hàm DATEDIFF nhận hai đối số, cả hai đều là dấu ngày/thời gian và đưa ra số ngày giữa chúng. Ví dụ trên sẽ hiển thị số ngày đã trôi qua từ ngày 15 tháng 12 năm 2020 cho đến ngày hôm nay
Ví dụ, để lấy số giây giữa hai ngày, hàm TO_SECONDS[] có thể hữu ích
SELECT TO_SECONDS[now[]] - TO_SECONDS['2021-02-05 11:56:41']; Điều này sẽ dẫn đến số giây giữa hai ngày được cung cấp
Trích xuất các phân đoạn từ ngày
Có nhiều hàm MySQL khác nhau cho phép bạn dễ dàng trích xuất các phân đoạn cụ thể từ các ngày, chẳng hạn như nếu bạn chỉ muốn tháng, ngày trong năm hoặc giờ. Dưới đây là một vài ví dụ về các chức năng như vậy
SELECT MONTH['2021-02-11 15:27:52']; SELECT HOUR[now[]]; SELECT DAYOFYEAR['2021-07-15 12:00:00']; Các câu lệnh SQL trên sẽ cho kết quả là 02, giờ hiện tại và 196 vì ngày 15 tháng 9 là ngày thứ 196 của năm. Đây là danh sách tất cả các hàm trích xuất ngày có sẵn, mỗi hàm chỉ lấy một đối số, ngày được trích xuất từ
SELECT CONVERT_TZ['2021-02-04 21:47:23', '+0:00', '-8:00']; 0Ví dụ: nếu có thể bạn chỉ muốn truy xuất tháng và năm mà tất cả người dùng đã được tạo, bạn có thể sử dụng câu lệnh SQL chẳng hạn như
SELECT CONVERT_TZ['2021-02-04 21:47:23', '+0:00', '-8:00']; 1Thao tác này sẽ truy xuất tất cả các bản ghi trong bảng người dùng và hiển thị id#, tháng và năm mà mỗi người dùng được tạo trong
Nhóm các bản ghi theo khoảng thời gian ngày
Một cách sử dụng tuyệt vời các hàm ngày tháng là khả năng nhóm các bản ghi theo khoảng thời gian bằng cách sử dụng GROUP BY trong các câu lệnh SQL của bạn. Ví dụ: có thể bạn muốn kéo tổng số tiền của tất cả các đơn đặt hàng vào năm 2020 được nhóm theo tháng. Bạn có thể sử dụng một câu lệnh SQL như
SELECT CONVERT_TZ['2021-02-04 21:47:23', '+0:00', '-8:00']; 2Thao tác này sẽ truy xuất tất cả các đơn đặt hàng đã đặt trong năm 2020, nhóm chúng theo tháng chúng được tạo và trả về 12 bản ghi hiển thị tổng số lượng đặt hàng mỗi tháng trong năm
Xin lưu ý, để có hiệu suất lập chỉ mục tốt hơn, tốt nhất bạn nên tránh sử dụng các hàm ngày tháng chẳng hạn như YEAR[] trong mệnh đề WHERE của câu lệnh SQL và thay vào đó hãy sử dụng toán tử BETWEEN như minh họa trong ví dụ trên
Không bao giờ nhầm lẫn với ngày nữa
Sử dụng kiến thức trên, giờ đây bạn có thể làm việc, dịch và thực hiện các thao tác theo ngày và giờ một cách hiệu quả trong nhiều trường hợp sử dụng
Hãy nhớ luôn sử dụng UTC khi làm việc với ngày để đơn giản và sử dụng các mẹo trên để quản lý ngày hiệu quả trong phần mềm của bạn, cho dù đó là để hoàn thành các phép tính đơn giản hay dễ dàng lấy báo cáo được nhóm theo khoảng thời gian ngày
Nếu bạn chưa quen với SQL, hãy đảm bảo kiểm tra các lệnh SQL thiết yếu này để giúp cải thiện việc sử dụng SQL của bạn