Chuyển đổi UTC DateTime thành các múi giờ khác nhau trong SQL Server – sql server utc datetime

Xử lý UTC DateTime trong cơ sở dữ liệu SQL Server là rất quan trọng đối với các ứng dụng đa múi giờ. Tại đây, bạn sẽ tìm hiểu về các cách lưu trữ ngày giờ trong UTC và chuyển đổi DateTime UTC sang múi giờ khác.

Bạn đang xem : sql server utc datetime

Khi làm việc trên cơ sở dữ liệu SQL Server với người dùng nhiều múi giờ, việc lưu trữ ngày và giờ trong UTC (Phối hợp theo giờ quốc tế). Tuy nhiên, khi hiển thị ngày và giờ cho người dùng, việc hiển thị ngày và giờ theo UTC có thể không phù hợp. Vì vậy, chúng tôi phải chuyển đổi nó sang múi giờ của người dùng. Ở đây, chúng ta sẽ xem cách lưu trữ dữ liệu và thời gian ở định dạng ngày giờ UTC và chuyển đổi nó sang các múi giờ khác nhau khi truy xuất ngày giờ đã lưu trữ.

Lưu trữ ngày và giờ theo ngày giờ UTC

Luôn luôn là một phương pháp hay để lưu trữ ngày-giờ ở UTC. Bạn có thể làm điều này theo một số cách khác nhau. Một cách là lấy trực tiếp thời gian UTC và lưu trữ nó trong cột kiểu dữ liệu ngày-giờ. Cách tiếp theo là chuyển đổi giờ địa phương đã cho thành giờ UTC và sau đó lưu trữ nó trong cột thời gian ngày. Một cách khác là sử dụng bộ datetimeoffset để thêm phần bù vào UTC và lưu trữ nó trong cột datetimeoffset. Hãy xem từng cái một với các ví dụ.

1. Sử dụng GETUTCDATE ()

Sử dụng GETUTCDATE () thay vì GETDATE () sẽ giúp bạn biết ngày và giờ UTC hiện tại. Vì vậy, bất cứ nơi nào bạn muốn lấy ngày giờ máy chủ hiện tại trong khi chèn hoặc cập nhật, hãy sử dụng GETUTCDATE (). Đây là một ví dụ:

/ * Tạo bảng * /
TẠO BẢNG [dbo]. [MTB_TABLE_UTC] (
[ID] [int] IDENTITY (1,1) KHÔNG ĐỦ,
[Đặt hàng] [nvarchar] (20) NULL,
[Đã đặt hàngOn_LocalTime] [datetime] NULL,
[Đã đặt hàngOn_UTC] [datetime] NULL
) BẬT [CHÍNH]
ĐI

/ * Chèn bản ghi * /
CHÈN VÀO [dbo]. [MTB_TABLE_UTC]
VALUES ('Xe đạp', GETDATE (), GETUTCDATE ())
ĐI

/ * Tìm nạp và xem bản ghi đã chèn * /
CHỌN * TỪ [dbo]. [MTB_TABLE_UTC]
ĐI

/ *
KẾT QUẢ:
ID thứ tự đã đặt hàngOn_LocalThời gian đã đặt hàngOn_UTC
--------------------------------- ---- -----------------------
1 Xe đạp 2021-08-18 06: 34: 32.207 2021-08-18 10: 34: 32.207

(1 hàng bị ảnh hưởng)
* /

Sử dụng UTC DateTime - GETUTCDATE ()  Sử dụng UTC DateTime - GETUTCDATE ()

Trong ví dụ trên, tôi đã tạo một bảng có một cột để lưu trữ ngày giờ của máy chủ cục bộ (là Giờ Miền Đông Hoa Kỳ & amp; Canada) bằng cách sử dụng GETDATE () và một cột khác để lưu trữ ngày và giờ UTC hiện tại bằng GETUTCDATE (). Nếu bạn nhận thấy giờ UTC trong cột OrderOn_UTC trước Giờ miền Đông 4 giờ.

Xem Thêm  Phân tách các phần tử bằng dấu phân cách - dấu phân cách nghĩa trong python

2. Chuyển đổi DateTime sang UTC

Đôi khi, bạn có thể cần lưu trữ ngày và giờ người dùng đã nhập. Người dùng có thể nhập ngày và giờ theo giờ địa phương của mình. Tuy nhiên, bạn có thể cần phải lưu trữ nó ở UTC. Trong trường hợp như vậy, bạn có thể chuyển đổi ngày và giờ do người dùng nhập thành UTC bằng cách sử dụng các hàm DATEDIFF DATEADD . Đây là một ví dụ:

Nếu người dùng nhập ngày tháng, bạn có thể tìm thấy sự khác biệt giữa giờ địa phương hiện tại và giờ UTC tính bằng giây và thêm nó vào thời gian mà người dùng đã nhập.

Tuyên bố
@OrderDate AS DATETIME,
@OrderDateUTC As DATETIME;

SET @OrderDate = '2021-08-15 18:32:15';
SET @OrderDateUTC = DATEADD (thứ hai,
    DATEDIFF (thứ hai, GETDATE (), GETUTCDATE ()), @OrderDate);

CHÈN VÀO [dbo]. [MTB_TABLE_UTC]
GIÁ TRỊ ('Bike', @OrderDate, @OrderDateUTC)
ĐI

/ * Tìm nạp và xem bản ghi đã chèn * /
CHỌN * TỪ [dbo]. [MTB_TABLE_UTC] ID ĐÂU & gt; 1
ĐI

/ *
KẾT QUẢ:
ID thứ tự đã đặt hàngOn_LocalThời gian đã đặt hàngOn_UTC
--------------------------------- ---- -----------------------
2 Xe đạp 2021-08-15 18: 32: 15.000 2021-08-15 22: 32: 15.000

(1 hàng bị ảnh hưởng)
* /

Sử dụng UTC DateTime - Chuyển đổi sang UTC  Sử dụng UTC DateTime - Chuyển đổi sang UTC

Trong ví dụ trên, người dùng đã nhập thời gian là “2021-08-15 18:32:15 ″. Để thực hiện điều này và thời gian UTC, tôi đã có sự khác biệt giữa GETDATE () và GETUTCDATE () trong vài giây và sau đó thêm nó vào thời gian người dùng đã nhập. Do đó, thời gian đã nhập được chuyển đổi thành UTC là “2021-08-15 22:32:15 ″.

3. Sử dụng DATETIMEOFFSET

Trong các kỹ thuật trước, chúng tôi đã sử dụng GETUTCDATE () để lấy ngày-giờ UTC hiện tại và lưu trữ nó trong cột DATETIME. Bây giờ chúng ta sẽ xem cách sử dụng SYSDATETIMEOFFSET () để lấy ngày-giờ cục bộ với độ lệch thành UTC và lưu trữ nó trong cột DATETIMEOFFSET . Không giống như GETUTCDATE (). SYSDATETIMEOFFSET () trả về bộ datetimeoffset (7) trong đó bù múi giờ được bao gồm trong ngày và giờ hệ thống. Hãy xem một ví dụ:

/ * Tạo bảng với datetimeoffset (7) là một trong các cột * /
TẠO BẢNG [dbo]. [MTB_TABLE_UTC] (
[ID] [int] IDENTITY (1,1) KHÔNG ĐỦ,
[Đặt hàng] [nvarchar] (20) NULL,
[Đã đặt hàngOn_LocalTime] [datetime] NULL,
[Đã đặt hàngOn_DTO] [datetimeoffset] (7) NULL
) BẬT [CHÍNH]
ĐI

/ * Chèn bản ghi * /
CHÈN VÀO [dbo]. [MTB_TABLE_UTC]
VALUES ('Xe đạp', GETDATE (), SYSDATETIMEOFFSET ())
ĐI

/ * Tìm nạp và xem bản ghi đã chèn * /
CHỌN * TỪ [dbo]. [MTB_TABLE_UTC]
ĐI

/ *
KẾT QUẢ:
ID thứ tự đã đặt hàngOn_LocalThời gian đã đặt hàngOn_DTO
--------------------------------- ---- ----------------------------------
1 Xe đạp 2021-08-18 06: 21: 18.410 2021-08-18 06: 21: 18.4219099 -04: 00

(1 hàng bị ảnh hưởng)
* /

Sử dụng UTC DateTime - DATETIMEOFFSET  Sử dụng UTC DateTime - DATETIMEOFFSET

Chuyển đổi ngày giờ UTC thành múi giờ địa phương

Bây giờ chúng ta sẽ xem cách chuyển đổi ngày-giờ được lưu trữ ở định dạng UTC sang múi giờ địa phương. Từ SQL Server 2016 và các phiên bản cao hơn, chúng tôi có một số kỹ thuật để chuyển đổi múi giờ. Hãy để chúng tôi xem từng người một.

Xem Thêm  Cách làm việc với JSON trong JavaScript - đọc dữ liệu json trong javascript

1. Chuyển đổi ngày-giờ UTC bằng AT TIME ZONE

Sử dụng AT TIME ZONE là một cách dễ dàng để chuyển đổi ngày-giờ UTC hoặc DATETIMEOFFSET sang múi giờ mong muốn của bạn ở định dạng datetimeoffset . Trong khi sử dụng AT TIME ZONE, bạn phải chỉ định múi giờ đích trong các tên tương ứng của chúng như Giờ chuẩn miền Đông , Giờ chuẩn Thái Bình Dương , v.v … Để nhận được tên múi giờ tương ứng , bạn có thể sử dụng hàm T-SQL của hệ thống sys.time_zone_info .

system T-SQL function sys.time_zone_info  system T-SQL function sys.time_zone_info

Bây giờ, hãy để chúng tôi xem cách sử dụng KHU VỰC THỜI GIAN trong một truy vấn đã chọn để tìm nạp giờ UTC và chuyển đổi sang các múi giờ khác nhau.

/ * Tạo bảng và chèn giờ UTC * /
TẠO BẢNG [dbo]. [MTB_TABLE_UTC] (
[ID] [int] IDENTITY (1,1) KHÔNG ĐỦ,
[Đặt hàng] [nvarchar] (20) NULL,
[Đã đặt hàngOn_LocalTime] [datetime] NULL,
[Đã đặt hàngOn_UTC] [datetime] NULL
) BẬT [CHÍNH]
ĐI

CHÈN VÀO [dbo]. [MTB_TABLE_UTC]
VALUES ('Xe đạp', GETDATE (), GETUTCDATE ())
ĐI

/ * Bây giờ tìm nạp và chuyển đổi ngày giờ UTC sang các múi giờ khác nhau * /
LỰA CHỌN *,
Đã đặt hàngOn_UTC TẠI TIME ZONE 'Giờ chuẩn miền Đông' NHƯ 'EST'
TỪ [dbo]. [MTB_TABLE_UTC];
LỰA CHỌN *,
Đã đặt hàngOn_UTC TẠI TIME ZONE 'Giờ chuẩn miền Trung' AS 'CST'
TỪ [dbo]. [MTB_TABLE_UTC];
LỰA CHỌN *,
Đã đặt hàngOn_UTC TẠI TIME ZONE 'Giờ chuẩn Thái Bình Dương' NHƯ 'PST'
TỪ [dbo]. [MTB_TABLE_UTC];
ĐI

Sử dụng AT TIME ZONE  Sử dụng AT TIME ZONE

2. Sử dụng SWITCHOFFSET

Bạn có thể sử dụng hàm SWITCHOFFSET để chuyển đổi kiểu dữ liệu datetimeoffset từ múi giờ này sang múi giờ khác. Dưới đây là một ví dụ để chuyển đổi bộ datetimeoffset với ‘Giờ chuẩn miền Đông’ thành ‘Giờ chuẩn miền Trung’

/ * Tạo bảng với datetimeoffset (7) là một trong các cột * /
TẠO BẢNG [dbo]. [MTB_TABLE_UTC] (
[ID] [int] IDENTITY (1,1) KHÔNG ĐỦ,
[Đặt hàng] [nvarchar] (20) NULL,
[Đã đặt hàngOn_LocalTime] [datetime] NULL,
[Đã đặt hàngOn_DTO] [datetimeoffset] (7) NULL
) BẬT [CHÍNH]
ĐI

/ * Chèn bản ghi * /
CHÈN VÀO [dbo]. [MTB_TABLE_UTC]
GIÁ TRỊ ('Xe đạp', '2021-08-19 06: 35: 15.00', '2021-08-19 06: 35: 15.00 -04: 00')
ĐI

/ * Bây giờ chuyển đổi ngày giờ UTC sang các múi giờ khác nhau * /
LỰA CHỌN *,
SWITCHOFFSET (Đã đặt hàngOn_DTO, '-05: 00') NHƯ 'CST'
TỪ [dbo]. [MTB_TABLE_UTC];
ĐI

Sử dụng SWITCHOFFSET  Sử dụng SWITCHOFFSET

Tham khảo

  • Tìm hiểu thêm về DATETIMEOFFSET tại Microsoft Documents .
  • Đọc thêm về AT TIME ZONE tại Microsoft Documents .
  • Tìm hiểu thêm về SWITCHOFFSET tại Microsoft Documents . < / li>


Xem thêm những thông tin liên quan đến chủ đề sql server utc datetime

Time Zones and the DATETIMEOFFSET data type in SQL

alt

  • Tác giả: Database by Doug
  • Ngày đăng: 2018-10-20
  • Đánh giá: 4 ⭐ ( 6776 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Get the code here: https://douglaskline.blogspot.com/2018/10/time-zones-and-datetimeoffset-data-type.html

Conversion of UTC time to LOCAL in SQL Query output

  • Tác giả: stackoverflow.com
  • Đánh giá: 4 ⭐ ( 9288 lượt đánh giá )
  • Khớp với kết quả tìm kiếm:

Converting a Datetime to UTC

  • Tác giả: sqlkover.com
  • Đánh giá: 5 ⭐ ( 5606 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: I was in a need of converting some datetime values (of which I know the actual timezone) to UTC dates. A quick Google search showed me that most results on the first page were simply wrong. Most of them used this trick: DECLARE @dt DATETIME = ‘2020-09-22 22:23:13.920’; SELECT DATEADD(MI, (DATEDIFF(MI, SYSDATETIME(), SYSUTCDATETIME())), @dt); Seems

SQL Server Date and Time Functions with Examples

  • Tác giả: www.mssqltips.com
  • Đánh giá: 4 ⭐ ( 1920 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Learn SQL Server date and time functions SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME, CURRENT_TIMESTAMP, GETDATE(), DATENAME, DATEPART with examples.

GETUTCDATE (Transact-SQL) – SQL Server

  • Tác giả: docs.microsoft.com
  • Đánh giá: 4 ⭐ ( 7454 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: GETUTCDATE (Transact-SQL)

SQL Server – Getting and storing date/time

  • Tác giả: makolyte.com
  • Đánh giá: 5 ⭐ ( 9252 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Shows 1) how to get the current datetime – local/UTC/with or without timezone offset 2) how to use the date/time data types – date, time, datetime2, and datetimeoffset.

SQL Server GETUTCDATE() Function

  • Tác giả: www.w3schools.com
  • Đánh giá: 5 ⭐ ( 6472 lượt đánh giá )
  • Khớp với kết quả tìm kiếm:

Xem thêm các bài viết khác thuộc chuyên mục: Kiến thức lập trình

Xem Thêm  300 câu thần chú trong Harry Potter - Cách đọc và ý nghĩa - cách chữa mụn nhọt nhanh nhất