Created
October 31, 2019 20:37
-
-
Save NJohnson9402/b984f237bd4cbf05b29eea832b759b7e to your computer and use it in GitHub Desktop.
Demo script for AT TIME ZONE usage with reporting on a transaction table
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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
For use with https://natethedba.wordpress.com/dealing-with-time-zones-in-sql/