Mysql có lưu trữ datetime với múi giờ không?

Trong bài viết này, tôi sẽ hướng dẫn bạn một số cân nhắc cơ bản để làm việc với dữ liệu liên quan đến ngày và giờ trong MySQL. Chúng tôi cũng sẽ xem xét cách xử lý nhiều múi giờ và các thay đổi về thời gian tiết kiệm ánh sáng ban ngày

Trước tiên, hãy giải quyết một số khái niệm cốt lõi sẽ giúp chúng ta hiểu được sự phức tạp cơ bản của dữ liệu liên quan đến thời gian. Điều quan trọng cần lưu ý là các khái niệm này được áp dụng khi biểu thị một thời điểm thay vì một khoảng thời gian tuyệt đối

Giới thiệu

Trước khi chúng ta nói về việc sử dụng dữ liệu ngày và giờ trong MySQL, hãy xem lại cách các múi giờ hoạt động. Chúng tôi sẽ bắt đầu với tham chiếu thời gian tiêu chuẩn

Tham khảo Giờ chuẩn. UTC

Vì thời gian cần được biết và phối hợp trên toàn thế giới, Giờ phối hợp quốc tế (UTC) được sử dụng làm tham chiếu thời gian tuyệt đối tiêu chuẩn. UTC được phát triển từ Giờ trung bình Greenwich (GMT) để sử dụng làm tiêu chuẩn và được duy trì bởi Văn phòng quốc tế des Poids et Mesures ở Paris

UTC thu được bằng cách áp dụng một bộ thuật toán cho khoảng 250 đồng hồ cesium và maze hydro trong khoảng 65 phòng thí nghiệm khác nhau trên toàn thế giới. Ước tính thời gian thực của UTC được tính toán và cung cấp bởi các trung tâm khác nhau, chẳng hạn như Đài quan sát Hải quân Hoa Kỳ và Viện Tiêu chuẩn và Công nghệ Quốc gia

Từ điểm tham chiếu này, mỗi múi giờ lấy được giờ địa phương được biểu thị dưới dạng phần bù từ UTC. Chẳng hạn, Múi giờ Trung Âu được định nghĩa là UTC+1. 00. Vì vậy, giờ địa phương cho khu vực này sẽ là giờ UTC hiện tại cộng thêm một giờ

UTC có giống với GMT không?

Khi quy chiếu thời gian ban đầu phải được xác định, người ta đã đồng ý xác định nó tương đối với một điểm nhất định trên trái đất. Greenwich, một quận của London, nơi có kinh tuyến gốc (kinh độ 0o) được chọn làm điểm này. Vì vậy, trưa GMT là thời điểm mặt trời ở điểm cao nhất trên Kinh tuyến Greenwich. Ngày nay, GMT hiện được sử dụng làm múi giờ không có phần bù từ UTC. UTC là một tham chiếu thời gian; . Không nơi nào trên trái đất sử dụng UTC làm múi giờ chính thức;

Thực tế mà nói, giờ GMT tương đương với giờ UTC (GMT = UTC+0). Tuy nhiên, nói một cách chính xác, tham chiếu thời gian hiện tại không phải là GMT mà là UTC

Múi giờ và Giờ địa phương

Múi giờ là một khu vực trên địa cầu tuân theo một thời gian tiêu chuẩn thống nhất. Múi giờ về cơ bản là phần bù của UTC, nhưng nó cũng có thể chịu sự thay đổi bổ sung trong Giờ tiết kiệm ánh sáng ban ngày

Giờ tiết kiệm ánh sáng ban ngày (DST), còn được gọi là giờ mùa hè, được quan sát bằng cách tăng đồng hồ lên một giờ vào mùa xuân và sau đó trở lại giờ tiêu chuẩn vào mùa thu. Ban đầu nó được dùng để tận dụng tốt hơn ánh sáng ban ngày tự nhiên và giảm mức tiêu thụ điện

Mỗi khu vực, quốc gia hoặc khu vực có thể có múi giờ thay đổi theo thời gian (e. g. vì lý do địa chính trị) và IANA theo dõi điều này trong cơ sở dữ liệu công khai được sử dụng trong nhiều hệ thống phần mềm. Trong trường hợp này, múi giờ thường được đặt tên theo mẫu khu vực/vị trí như Mỹ/Indiana/Knox, Úc/Melbourne, v.v.

Giờ địa phương được tính bằng cách áp dụng chênh lệch múi giờ khu vực cho giờ UTC. Chẳng hạn, để tính giờ địa phương ở Buenos Aires, Argentina, tôi áp dụng phần bù cho Argentina cho giờ UTC hiện tại. Nếu UTC là 16. 19 và America/Buenos_Aires là UTC-3, giờ địa phương của tôi là 13. 19

Cần lưu ý rằng vì UTC là tham chiếu thời gian tuyệt đối nên nó không bị ảnh hưởng bởi DST. Điều bị ảnh hưởng bởi DST là phần bù cục bộ

Múi giờ và thời gian bù đắp không giống nhau

Điều này là do độ lệch cho một múi giờ nhất định có thể thay đổi. Hãy lấy Los Angeles, California làm ví dụ. Los Angeles nằm trong múi giờ IANA của Mỹ/Los_Angeles. Nó có phần bù UTC-7 trong DST, nhưng phần bù UTC-8 cho thời gian tiêu chuẩn. Phần bù thay đổi, nhưng ranh giới múi giờ giống nhau. Tuy nhiên, những nơi khác, chẳng hạn như bang Arizona, không tuân theo DST;

Vậy thỏa thuận lớn nào?

Khi lưu trữ dữ liệu thời gian và ngày tháng, mức độ phức tạp phụ thuộc rất nhiều vào những gì bạn cần làm với dữ liệu đó về mặt truy xuất, thao tác và hiển thị giao diện người dùng. Trước khi bạn bắt đầu thiết kế bất cứ thứ gì, hãy tự hỏi bản thân

Dữ liệu này đại diện cho một thời điểm trong quá khứ hay trong tương lai?

Nếu nó đại diện cho một thời điểm trong quá khứ, thì bất kỳ sự bù trừ nào cho múi giờ đã xảy ra và đã được biết đến. Đối với các điểm trong tương lai, phần bù này có thể thay đổi – DST có thể có hiệu lực hoặc thậm chí thay đổi

Giả sử bạn muốn lưu ngày và giờ mùa hè trong tương lai cho Los Angeles, chẳng hạn như trưa ngày 13 tháng 4 năm 2060 (2060-04-13 19. 00 UTC). Theo dự báo hiện tại, DST bắt đầu từ 02. 00 ngày hôm đó, vì vậy ngày giờ này có độ lệch -7h do DTS

Nhưng, hãy tưởng tượng rằng năm tới (2017), họ quyết định bắt đầu DST một ngày sau đó ở California (vâng, bản thân DST không cố định và có thể thay đổi theo thời gian). Vì vậy, nếu năm sau tôi phải chọn thời điểm tương tự (2060-04-13 19. 00 UTC), giờ địa phương thực tế sẽ là 2060-04-13 11. 00 vì DST chưa khởi động

Tóm lại, nếu bạn đã lưu trữ trưa ngày 13 tháng 4 năm 2060 làm ngày địa phương, thì ngày đó sẽ không thể hiện thời điểm dự định trong tương lai vì ban đầu nó được tạo với độ lệch -7 giờ thay vì -8 giờ

Dữ liệu này sẽ được hiển thị như thế nào?

Đôi khi bạn cần hiển thị ngày và giờ theo giờ địa phương của người dùng. Hoặc bạn có thể cần hiển thị nó theo giờ địa phương ban đầu hoặc theo giờ UTC. Bạn cũng có thể cần nêu rõ múi giờ thực tế cho ngày cụ thể đó. Vì vậy, điều này sẽ ảnh hưởng đến cách bạn lưu trữ dữ liệu của mình, tùy thuộc vào yêu cầu, bạn có thể cần biết múi giờ thích hợp

Thông tin sẽ được sắp xếp hoặc lọc theo giờ địa phương hay theo biểu diễn UTC của nó?

Tùy thuộc vào trường hợp sử dụng của bạn, bạn có thể cần sắp xếp và hiển thị thông tin dựa trên thứ tự thời gian của các sự kiện. Do đó, biểu diễn UTC, là thời điểm thực tế, sẽ là cách thích hợp để sắp xếp dữ liệu này

Nếu yêu cầu là sắp xếp dữ liệu dựa trên giờ địa phương ban đầu thì sao? . Điều này cũng phù hợp để sắp xếp theo giờ địa phương, chẳng hạn như hiển thị các bộ phim phát vào một ngày địa phương cụ thể như 2016-12-01. Nếu bạn đã lọc kết quả theo nguyên trạng của ngày này và so sánh nó với thời gian UTC thay vì giờ địa phương, thì một bộ phim bắt đầu ở Los Angeles lúc 2016-12-01 23. 00 giờ địa phương sẽ bị lọc ra, vì biểu diễn UTC là 2016-12-02 07. 00 UTC

"hôm nay" có nghĩa là gì?

Giống như điểm trước, câu hỏi này (và tất cả các mối quan hệ của nó, như tuần tới, ngày mai, năm nay, v.v. ) sẽ phụ thuộc vào ứng dụng của bạn. Giả sử người dùng hiện tại muốn nhật ký máy từ các máy trên toàn cầu và giao diện có một nút để “lấy nhật ký từ. hôm nay". Giờ địa phương là 2016-10-07 16. 00 Mỹ/Buenos_Aires (UTC-3). “hôm nay” đối với người dùng này là gì?

“Dựa trên giờ địa phương của người dùng”

Đây sẽ là từ 2016-10-07 00. 00 đến 2016-10-07 23. 59. 59 theo giờ địa phương của anh ấy, được dịch sang giờ UTC, bao gồm tất cả các bản ghi từ 2016-10-07 03. 00 đến 2016-10-08 02. 59. 59 UTC. (Lưu ý sự thay đổi ngày và giờ trong biểu diễn UTC)

"UTC dựa trên phân đoạn ngày của người dùng"

Trong ví dụ, ngày của người dùng hiện tại sẽ là 2016-10-07 theo giờ địa phương của anh ấy. Nếu chúng tôi sử dụng phần đó làm phần ngày UTC, thì “hôm nay” sẽ bao gồm tất cả các bản ghi nằm trong khoảng từ 2016-10-07 00. 00 đến 2016-10-07 23. 59. 59 trong UTC. (So ​​với ví dụ trước, phạm vi này không có sự thay đổi ngày. )

"Bản ghi có phân đoạn ngày địa phương bằng với phân đoạn ngày của người dùng"

Trong ví dụ của chúng tôi, ngày của người dùng hiện tại sẽ là 2016-10-07 theo giờ địa phương của anh ấy. Vì vậy, chúng tôi chỉ sử dụng phần đó và lấy bất kỳ nhật ký nào đã được tạo trong khoảng 2016-10-07 00. 00 đến 2016-10-07 23. 59. 59 nhưng theo giờ địa phương của kỷ lục

Vì vậy, nếu một bản ghi ở một nơi khác trên thế giới được tạo vào 2016-10-07 23. 00. 00 (UTC-11), là 2016-10-08 10. 00. 00 trong UTC, nó sẽ được truy xuất. (Lưu ý rằng bản ghi này sẽ không được truy xuất trong hai ví dụ khác. )

Thao tác múi giờ trong MySQL

Tất cả điều này đòi hỏi phải thao tác dữ liệu ngày tháng để phù hợp với nhu cầu của ứng dụng. Có thể thao tác các múi giờ trong MySQL bằng cách sử dụng hàm

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

0. Từ tài liệu của MySQL

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

1 chuyển đổi giá trị ngày giờ

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

2 từ múi giờ do

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

3 cung cấp sang múi giờ do

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

4 cung cấp và trả về giá trị kết quả (. ) Hàm này trả về

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

5 nếu đối số không hợp lệ

Điều này có thể được sử dụng theo hai cách – bằng cách chỉ ra múi giờ hoặc bằng cách chuyển rõ ràng độ lệch thời gian làm tham số, như bạn có thể thấy trong các ví dụ sau

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','Europe/Moscow');
        -> '2004-01-01 13:00:00'

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

Để sử dụng chức năng này với các múi giờ như “Châu Âu/Moscow”, bạn cần tự mình thiết lập và duy trì các bản ghi múi giờ, như đã nêu trong tài liệu MySQL

Quy trình cài đặt MySQL tạo các bảng múi giờ, nhưng không tải chúng. Bạn phải làm như vậy theo cách thủ công bằng cách sử dụng các hướng dẫn sau. Tải thông tin múi giờ không nhất thiết phải là thao tác một lần vì thông tin thỉnh thoảng thay đổi. Khi những thay đổi như vậy xảy ra, các ứng dụng sử dụng các quy tắc cũ sẽ trở nên lỗi thời và bạn có thể thấy cần phải tải lại các bảng múi giờ để giữ cho thông tin hiện tại được sử dụng bởi máy chủ MySQL của bạn

Đối với các nhóm nhỏ, nhà phát triển cá nhân và trên một số dịch vụ lưu trữ được chia sẻ, việc duy trì hoạt động bình thường có thể là một thách thức hoặc thậm chí là không thể. Vì lý do này, tôi đề xuất một cách tiếp cận thay thế

Lấy thời gian hiện tại trong MySQL

Trước khi xem xét cách tiếp cận thay thế, hãy xem xét cách chúng tôi lấy thời gian hiện tại trong MySQL

Một số chức năng ngày địa chỉ thời gian. Theo mặc định, MySQL sử dụng múi giờ hệ thống cục bộ. Do đó, việc gọi hàm NOW() sẽ lấy thời gian hiện tại ở bất kỳ múi giờ nào mà máy chủ đã đặt. Nhưng nếu bạn muốn chắc chắn và nhất quán về kết quả, bạn nên sử dụng

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

0 để thay thế. Từ các tài liệu chính thức

UTC_TIMESTAMP Trả về ngày và giờ UTC hiện tại dưới dạng giá trị 'YYYY-MM-DD HH. MM. Định dạng SS' hoặc YYYYMMDDHHMMSS, tùy thuộc vào việc hàm được sử dụng trong ngữ cảnh chuỗi hay số. Nếu đối số fsp được cung cấp để chỉ định độ chính xác của giây phân số từ 0 đến 6, thì giá trị trả về bao gồm một phần giây phân số của nhiều chữ số đó

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
        -> '2003-08-14 18:08:04', 20030814180804.000000

Phương pháp được đề xuất

Tôi khuyên bạn nên lưu trữ ngày và giờ của MySQL dưới dạng cột DATETIME (hãy đọc phần này để hiểu lý do tại sao) và lưu trữ múi giờ (như “America/Los_Angeles”) trong một cột VARCHAR khác. Lưu ý rằng tôi KHÔNG khuyên bạn lưu trữ phần bù UTC;

Trên hết, lưu trữ thời gian UTC trong cơ sở dữ liệu. Đây là đại diện chính xác nhất của một thời điểm bạn có thể có. Vì bạn cũng có cột múi giờ, nên bạn sẽ luôn có khả năng “xây dựng lại” ngày/giờ địa phương ban đầu, thông qua hàm CONVERT_TZ() của MySQL hoặc từ phần cuối ứng dụng của bạn, như được minh họa bằng đoạn mã PHP sau

$utc_time = time retrieved from the database
$timezone = timezone retrieved from the database
$date_obj = new DateTime($utc_time, new DateTimeZone("UTC"));
$date_obj->setTimeZone($timezone);
$display_date = $date_obj->format("Y-m-d H:i:s");

Điều này cũng sẽ mang lại các truy vấn hiệu quả có thể sử dụng phạm vi UTC và sắp xếp

Một cảnh báo quan trọng là nếu bạn không thể sử dụng CONVERT_TZ để so sánh hoặc sắp xếp theo giờ địa phương của bản ghi đã lưu (như được giải thích trong phân đoạn), bạn cũng có thể lưu trữ ngày và giờ theo giờ địa phương ban đầu. Bạn cũng có thể muốn làm điều này để tiết kiệm chuyển đổi thời gian UTC trở lại giờ địa phương. Tuy nhiên, nếu bạn chọn phương pháp này, hãy nhớ rằng thời gian địa phương được lưu trữ thuộc về tương lai có thể sai, như đã giải thích ở trên

Một cách tiếp cận chung hoàn chỉnh sẽ là có hai cột DATETIME để lưu trữ

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

1 và

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

2, trong khi cột VARCHAR lưu trữ chuỗi IANA

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

3 (dài nhất tại thời điểm này là “America/Argentina/ComodRivadavia”, có 32 ký tự)

Chẳng hạn, một bảng nhật ký cơ bản lưu trữ dữ liệu từ các máy chủ trên khắp hành tinh có thể được mô hình hóa như thế này

Hai trường

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

4 lưu trữ giờ UTC và giờ địa phương khi nhật ký được tạo và trường

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

5 lưu trữ múi giờ địa phương

Cách tiếp cận này giải quyết nhiều vấn đề có thể phát sinh từ tất cả các trường hợp sử dụng khác nhau mà chúng tôi đã đề cập. Nó cũng giúp dễ dàng đối phó với những thay đổi có thể xảy ra trong tương lai. Nếu bạn có một số kinh nghiệm trong lĩnh vực này để chia sẻ, xin vui lòng gửi chúng trong phần bình luận bên dưới

Làm cách nào để lưu ngày giờ với múi giờ trong MySQL?

MySQL có lưu trữ dữ liệu thời gian trong UTC không?

MySQL xử lý các múi giờ như thế nào?

Ngày giờ có được lưu trữ mà không có múi giờ không?