Demo script for AT TIME ZONE usage with reporting on a transaction table
/* The system table that contains TZ info | |
SELECT tzi.name, tzi.current_utc_offset, tzi.is_currently_dst | |
FROM master.sys.time_zone_info tzi | |
*/ | |
--Drop & create tables | |
IF (OBJECT_ID('dbo.OfficeLocation') IS NOT NULL) | |
DROP TABLE dbo.OfficeLocation; | |
IF (OBJECT_ID('dbo.MyTrans') IS NOT NULL) | |
DROP TABLE dbo.MyTrans; | |
GO | |
CREATE TABLE dbo.OfficeLocation | |
( | |
OfficeCode varchar(2) | |
, TimeZone sysname | |
); | |
CREATE TABLE dbo.MyTrans | |
( | |
TranDate datetime | |
, Description varchar(MAX) | |
, TypeCode varchar(2) | |
, OfficeCode varchar(2) | |
); | |
--These are the offices; HK is merely included to show that it needs to use the same TZ string as China. | |
INSERT INTO dbo.OfficeLocation (OfficeCode, TimeZone) | |
SELECT 'FR', 'Central Europe Standard Time' | |
UNION ALL | |
SELECT 'CN', 'China Standard Time' | |
UNION ALL | |
SELECT 'HK', 'China Standard Time' | |
; | |
--Note the start of each calendar day, and the start & close of business. | |
INSERT INTO dbo.MyTrans (TranDate, Description, TypeCode, OfficeCode) | |
SELECT '2019-10-31T09:00:00', 'Start of 2019-11-01 in China', 'SD', 'CN' | |
UNION ALL | |
SELECT '2019-10-31T16:00:00', 'Start of 2019-11-01 in Paris', 'SD', 'FR' | |
UNION ALL | |
SELECT '2019-10-30T09:00:00', 'Start of 2019-10-31 in China', 'SD', 'CN' | |
UNION ALL | |
SELECT '2019-10-30T16:00:00', 'Start of 2019-10-31 in Paris', 'SD', 'FR' | |
UNION ALL | |
SELECT '2019-10-29T09:00:00', 'Start of 2019-10-30 in China', 'SD', 'CN' | |
UNION ALL | |
SELECT '2019-10-29T16:00:00', 'Start of 2019-10-30 in Paris', 'SD', 'FR' | |
UNION ALL | |
SELECT '2019-10-30T08:00:00', 'Start of business 2019-10-30 in US', 'SB', 'US' | |
UNION ALL | |
SELECT '2019-10-30T17:00:00', 'Close of business 2019-10-30 in US', 'CB', 'US' | |
UNION ALL | |
SELECT '2019-10-31T08:00:00', 'Start of business 2019-10-31 in US', 'SB', 'US' | |
UNION ALL | |
SELECT '2019-10-31T17:00:00', 'Close of business 2019-10-31 in US', 'CB', 'US' | |
UNION ALL | |
SELECT '2019-10-29T17:00:00', 'Start of business 2019-10-30 in China', 'SB', 'CN' | |
UNION ALL | |
SELECT '2019-10-30T02:00:00', 'Close of business 2019-10-30 in China', 'CB', 'CN' | |
UNION ALL | |
SELECT '2019-10-30T17:00:00', 'Start of business 2019-10-31 in China', 'SB', 'CN' | |
UNION ALL | |
SELECT '2019-10-31T02:00:00', 'Close of business 2019-10-31 in China', 'CB', 'CN' | |
UNION ALL | |
SELECT '2019-10-30T00:00:00', 'Start of business 2019-10-30 in Paris', 'SB', 'FR' | |
UNION ALL | |
SELECT '2019-10-30T09:00:00', 'Close of business 2019-10-30 in Paris', 'CB', 'FR' | |
UNION ALL | |
SELECT '2019-10-31T00:00:00', 'Start of business 2019-10-31 in Paris', 'SB', 'FR' | |
UNION ALL | |
SELECT '2019-10-31T09:00:00', 'Close of business 2019-10-31 in Paris', 'CB', 'FR' | |
; | |
--Demo a few inserts using the reverse logic -- "lunch" (12 noon) in each office | |
INSERT INTO dbo.MyTrans (TranDate, Description, TypeCode, OfficeCode) | |
SELECT CONVERT(datetime, '2019-10-30T12:00:00') AT TIME ZONE ol.TimeZone AT TIME ZONE 'Pacific Standard Time' | |
, 'Lunch time 2019-10-30 in China', 'LU', 'CN' | |
FROM dbo.OfficeLocation ol | |
WHERE ol.OfficeCode = 'CN' | |
UNION ALL | |
SELECT CONVERT(datetime, '2019-10-30T12:00:00') AT TIME ZONE ol.TimeZone AT TIME ZONE 'Pacific Standard Time' | |
, 'Lunch time 2019-10-30 in Paris', 'LU', 'FR' | |
FROM dbo.OfficeLocation ol | |
WHERE ol.OfficeCode = 'FR' | |
UNION ALL | |
SELECT CONVERT(datetime, '2019-10-31T12:00:00') AT TIME ZONE ol.TimeZone AT TIME ZONE 'Pacific Standard Time' | |
, 'Lunch time 2019-10-31 in China', 'LU', 'CN' | |
FROM dbo.OfficeLocation ol | |
WHERE ol.OfficeCode = 'CN' | |
UNION ALL | |
SELECT CONVERT(datetime, '2019-10-31T12:00:00') AT TIME ZONE ol.TimeZone AT TIME ZONE 'Pacific Standard Time' | |
, 'Lunch time 2019-10-31 in Paris', 'LU', 'FR' | |
FROM dbo.OfficeLocation ol | |
WHERE ol.OfficeCode = 'FR' | |
; | |
--Show the conversions | |
SELECT [PacificTime] = mt.TranDate, mt.Description, mt.TypeCode, mt.OfficeCode | |
, LocalTime = CONVERT(datetime, mt.TranDate AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE ol.TimeZone) | |
, LocalDay = CONVERT(date, mt.TranDate AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE ol.TimeZone) | |
FROM dbo.MyTrans mt | |
JOIN dbo.OfficeLocation ol | |
ON mt.OfficeCode = ol.OfficeCode | |
--Show a count of events by LOCAL DAY | |
SELECT LocalDay = CONVERT(date, mt.TranDate AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE ol.TimeZone) | |
, mt.OfficeCode, N = COUNT(1) | |
FROM dbo.MyTrans mt | |
JOIN dbo.OfficeLocation ol | |
ON mt.OfficeCode = ol.OfficeCode | |
GROUP BY mt.OfficeCode, CONVERT(date, mt.TranDate AT TIME ZONE 'Pacific Standard Time' AT TIME ZONE ol.TimeZone) | |
ORDER BY LocalDay, mt.OfficeCode |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
For use with https://natethedba.wordpress.com/dealing-with-time-zones-in-sql/