Đôi khi bạn có thể cần chuyển đổi giá trị thời gian của MySQL từ múi giờ này sang múi giờ khác. Đây là cách thay đổi múi giờ MySQL trong truy vấn bằng hàm convert_tz[]
Chuyển đổi múi giờ trong truy vấn MySQL
Chúng tôi sẽ sử dụng chức năng CONVERT_TZ để thay đổi múi giờ MySQL trong truy vấn. Đây là cú pháp của hàm CONVERT_TZ
convert_tz[value, from_timezone, to_timezone]
Trong hàm trên, bạn cần cung cấp giá trị thời gian cần chuyển đổi, múi giờ mà bạn muốn chuyển đổi giá trị này và múi giờ mà bạn muốn chuyển đổi thành
Bạn có thể chỉ định múi giờ dưới dạng hiệu số hoặc tên múi giờ
Phần thưởng đọc. Cách thay đổi múi giờ của máy chủ MySQL
Đây là một ví dụ để chuyển đổi giá trị thời gian theo nghĩa đen từ múi giờ UTC sang múi giờ EST, sử dụng độ lệch múi giờ là '+00. 00’ và ‘-05. 00’ cho UTC và EST tương ứng
mysql> select convert_tz['2020-09-17 03:00:00','+00:00','-05:00']; +-----------------------------------------------------+ | convert_tz['2020-09-17 03:00:00','+00:00','-05:00'] | +-----------------------------------------------------+ | 2020-09-16 22:00:00 | +-----------------------------------------------------+
Bạn cũng có thể chỉ định múi giờ thay vì hiệu số. Tuy nhiên, trong trường hợp này, bạn sẽ cần tải xuống và cài đặt múi giờ MySQL trên máy chủ của mình. Đây là một ví dụ để chuyển đổi EST sang múi giờ Paris bằng cách chỉ định tên múi giờ thay vì giá trị bù
mysql> select convert_tz['2020-09-17 03:00:00','US/Eastern','Europe/Paris'];
Phần thưởng đọc. MySQL Hiển thị các chỉ mục trong cơ sở dữ liệu
Bạn cũng có thể chuyển đổi các hàm ngày, giờ và ngày giờ của MySQL trong hàm CONVERT_TZ. Đây là một ví dụ để thay đổi múi giờ của thời gian hiện tại thu được bằng cách sử dụng hàm NOW[], từ UTC sang EST
mysql> select convert_tz[now[],'+00:00','-05:00']; +-------------------------------------+ | convert_tz[now[],'+00:00','-05:00'] | +-------------------------------------+ | 2020-09-17 04:45:07 | +-------------------------------------+
Phần thưởng đọc. Cách so sánh cơ sở dữ liệu MySQL
Tương tự, bạn cũng có thể chuyển đổi các cột ngày, giờ, ngày giờ bằng convert_tz. Đây là một ví dụ để thay đổi múi giờ của cột order_date trong bảng bán hàng, từ UTC sang EST
mysql> select convert_tz[order_date,'+00:00','-05:00'] from sales; +------------------------------------------+ | convert_tz[order_date,'+00:00','-05:00'] | +------------------------------------------+ | 2020-05-03 19:00:00 | | 2020-05-04 19:00:00 | | 2020-05-05 19:00:00 | | .. | | 2020-05-13 19:00:00 | +------------------------------------------+
Hy vọng, bây giờ bạn có thể thay đổi múi giờ MySQL trong truy vấn và chuyển đổi giá trị ngày, giờ, ngày giờ từ múi giờ này sang múi giờ khác
Chúng tôi thường xử lý thông tin ngày và giờ trong các ứng dụng của mình và đôi khi cần lưu trữ thông tin này và chuyển đổi giữa các múi giờ khác nhau. Vì vậy, ở đây tôi đang thảo luận về một số trường hợp sử dụng thông tin múi giờ và cách xử lý đúng các tình huống đó. Bài viết này sẽ thảo luận về các điểm dưới đây,
- Giờ UTC & giờ GMT là gì?
- DẤU THỜI GIAN so với DATETIME trong MySQL
- Chuyển đổi thông tin thời gian giữa các vùng khác nhau
- Làm cách nào để lưu trữ thông tin múi giờ trong MySQL?
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 phát triển từ Giờ trung bình Greenwich [GMT] để sử dụng làm tiêu chuẩn
GMT hiện được sử dụng làm múi giờ không có phần bù từ UTC. UTC là tham chiếu thời gian và GMT là múi giờ. Không có 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]. Tóm lại, tham chiếu thời gian hiện tại không phải là GMT mà là UTC
DẤU THỜI GIAN so với DATETIME trong MySQLNGÀY GIỜ. Nó được sử dụng cho các giá trị chứa cả phần ngày và giờ. MySQL truy xuất và hiển thị giá trị DATETIME trong YYYY-MM-DD HH. MM. định dạng SS. Phạm vi được hỗ trợ là 1000–01–01 00. 00. 00 đến 9999–12–31 23. 59. 59
DẤU THỜI GIAN. Nó được sử dụng cho các giá trị chứa cả phần ngày và giờ. DẤU THỜI GIAN có phạm vi từ 1970–01–01 00. 00. 01 UTC đến 2038–01–19 03. 14. 07 UTC
- Trong MySQL5+, các giá trị DẤU THỜI GIAN được chuyển đổi từ múi giờ phiên thành UTC để lưu trữ và từ UTC thành múi giờ phiên để truy xuất. Nhưng DATETIME không thực hiện bất kỳ chuyển đổi nào
- Kết quả là TIMESTAMP khác với cài đặt múi giờ hiện tại trong khi DATETIME không đổi
- Các truy vấn có DATETIME sẽ không được lưu vào bộ đệm nhưng các truy vấn có DẤU THỜI GIAN sẽ được lưu vào bộ đệm
- Dữ liệu DẤU THỜI GIAN có thể được lập chỉ mục trong khi dữ liệu DATETIME không thể
Khi máy chủ khởi động, nó sẽ cố gắng tự động xác định múi giờ của máy chủ và sử dụng nó để đặt biến hệ thống system_time_zone. Giá trị không thay đổi sau đó
Các lệnh MySQL bên dưới có thể được sử dụng để xem các thông tin múi giờ đó và thay đổi chúng theo cách thủ công
mysql> CHỌN @@ toàn cầu. time_zone, @@ phiên. Múi giờ;
mysql> SET phiên time_zone =’+5. 00';
Chuyển đổi thông tin thời gian giữa các vùng khác nhauKhi chúng tôi xử lý các thông tin múi giờ này, đôi khi chúng tôi cần chuyển đổi giữa các múi giờ khác nhau. Chúng ta có thể sử dụng các thư viện java 8 để dễ dàng triển khai trình chuyển đổi múi giờ đơn giản để chuyển đổi thời gian giữa các múi giờ khác nhau. Các phiên bản đối tượng từ các lớp Java 8 bên dưới sẽ lưu trữ tức thời trên dòng thời gian với độ chính xác nano giây
- OffsetDateTime -Lớp này lưu trữ tất cả các trường ngày và giờ, với độ chính xác là nano giây, cũng như phần bù từ UTC/Greenwich.
ví dụ. 13. 45. 30. 123456789 +02. 00 - ZonedDateTime -Lớp này lưu trữ tất cả các trường ngày và giờ, với độ chính xác là nano giây và múi giờ, với phần bù múi giờ được sử dụng để xử lý ngày giờ địa phương không rõ ràng.
ví dụ. 13. 45. 30. 123456789 +02. 00 - Instant -Lớp này mô hình hóa một điểm tức thời duy nhất trên dòng thời gian. Điều này có thể được sử dụng để ghi lại dấu thời gian sự kiện trong ứng dụng
Instant là đơn giản nhất, chỉ đơn giản là đại diện cho epoch-giây [không có thông tin bù]. OffsetDateTime thêm vào phần bù ngay lập tức từ UTC, cho phép thu được thời gian ngày địa phương. ZonedDateTime sử dụng quy tắc múi giờ đầy đủ trong khi xử lý ngày
Để biết thêm thông tin về điều này, bạn có thể đọc tài liệu tiên tri chính thức
Dưới đây là một ví dụ về cách thực hiện chuyển đổi múi giờ này với Java. Giả sử một dấu thời gian được đưa ra ở định dạng ISO 8601 là 2019-10-10T10. 00. 00+04. 00Z và chúng tôi cần chuyển đổi giá trị này sang dấu thời gian Colombo của UTC+5. 30 giờ. Trong biểu diễn thời gian ở trên, T chỉ là một nghĩa đen để phân tách ngày với thời gian và Z có nghĩa là “độ lệch 0 giờ” còn được gọi là “thời gian Zulu” [UTC]
1] Trước tiên, hãy xóa ký tự 'Z' khỏi chuỗi đầu vào và xác định 'DateTimeFormatter' để ánh xạ chuỗi đầu vào
Xóa Z khỏi chuỗi đầu vào đại diện cho tham chiếu thời gian UTC
2] Sau đó, hãy đọc thông tin dấu thời gian cho một phiên bản OffsetDateTime bằng cách chuyển chuỗi dấu thời gian đầu vào với DateTimeFormatter được xác định cho dấu thời gian đầu vào
3] Bây giờ chuyển đổi thời gian thu được trong múi giờ đầu vào thành một phiên bản OffsetDateTime của múi giờ yêu cầu
4] Cuối cùng, bạn có thể chuyển đổi phiên bản OffsetDateTime thành bất kỳ định dạng thời gian bắt buộc nào
Chuyển đổi thời gian mẫu từ định dạng ISO sang múi giờ bắt buộc
5] Bạn có thể làm theo quy trình tương tự để lấy thời gian theo múi giờ của máy chủ bằng cách xác định ZoneOffset để lấy phần bù múi giờ địa phương
Chuyển đổi thời gian mẫu từ định dạng ISO sang múi giờ của máy chủ cục bộ
Kiểm tra mã nguồn và đơn vị có sẵn trong kho lưu trữ GitHub bên dưới
Cách lưu trữ thông tin múi giờ trong MySQLLưu trữ chi tiết múi giờ trong MySQL
Không có bất kỳ tính năng sẵn có nào trong MySQL để lưu trữ các giá trị thời gian khác nhau với nhiều thông tin múi giờ. Vì vậy, nếu bạn cần lưu trữ chi tiết múi giờ cùng với thời gian, thì bạn sẽ cần lưu trữ múi giờ [chẳng hạn như “Colombo”] trong một cột VARCHAR khác. Và giá trị thời gian có thể được lưu trữ trong cột DATETIME hoặc TIMESTAMP trong MySQL. Bạn có thể lưu trữ giá trị số của thời gian theo thời gian UTC hoặc thời gian máy chủ bằng cách sử dụng phương pháp chuyển đổi thời gian ở trên. Sau đó, bạn có thể chuyển đổi nó trở lại múi giờ được lưu trữ hoặc bất kỳ múi giờ bắt buộc nào khác khi lấy lại dấu thời gian
Nhưng không nên lưu trữ thời gian với độ lệch do ảnh hưởng của DST
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. 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ộ