Skip to content

Instantly share code, notes, and snippets.

@NJohnson9402
Created October 31, 2019 20:37
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
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
/* 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
@NJohnson9402
Copy link
Author

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment