THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Temporal Tables - Part 6 - Start and End Times in UTC Time Zone

In my first 5 blogs on Temporal, I failed to note something pretty important about their usage. The start and end times for the row (and it follows, the historical rows) are likely not in your local time zone. They are stored in UTC time zone. This is obviously good because of that pesky daylight saving time issue where 1:30 AM occurs twice each year (here in the US, the time occurs on the first Sunday of November).

Unless you live in London, England or someplace where the offset from UTC is 0, it can make working with these tables confusing, because most people rarely work in UTC time, and even rarer is to think in UTC time when most of your data is likely in your local time zone. So you write your query and use local time...and then, most likely, data is returned…but not necessarily the data you actually desired.

The place you will probably notice the issue is right after you create a table, especially if your server is in a time zone that has a negative offset from UTC. What will happen is that you insert a row, and immediately try to fetch a row using the FOR SYSTEM_TIME AS OF the current time in your query, and nothing will be returned. What you will have done is ask for rows that existed before your table even existed. For example, consider the following structure (basically the same table structure used in part 5):

CREATE DATABASE TemporalExample
GO
USE TemporalExample
GO
CREATE SCHEMA Sales;
GO
CREATE SCHEMA SalesHistory; --Unlike earlier examples, I put the history in its own schema for granting security purposes.
GO
CREATE SEQUENCE Sales.SalesOrderData_SEQUENCE MINVALUE 1 START WITH 1;
GO
CREATE TABLE Sales.SalesOrder

     SalesOrderId int NOT NULL CONSTRAINT PKSalesOrder PRIMARY KEY,
                              --default to a text hex value, so we can see changes...
    Data varchar(30) NOT NULL DEFAULT (SUBSTRING(CONVERT(varchar(20),
                     CAST(NEXT VALUE FOR Sales.SalesOrderData_SEQUENCE as varbinary(5)), 1),3,12)),
    ValidStartTime datetime2 (0) GENERATED ALWAYS AS ROW START, --(0) to keep the output short. Typically (7)
    ValidEndTime datetime2 (0) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidStartTime, ValidEndTime)

WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = SalesHistory.SalesOrder));

Now, I will create a couple of new rows. Nothing too interesting, just needs to have a start and end time, and 2 rows seems cleaner than 1:

INSERT INTO Sales.SalesOrder(SalesOrderId, Data)
VALUES (1, DEFAULT), (2, DEFAULT);
GO

Check the rows of the table:

SELECT *
FROM Sales.SalesOrder;

This returns data just as expected, but I am not writing this blog after midnight in the Eastern Time Zone, rather it was just after 8:00 PM:

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------
1            0000000001                     2017-09-18 00:07:25         9999-12-31 23:59:59
2            0000000002                     2017-09-18 00:07:25         9999-12-31 23:59:59

So if I write my query to get the data as of now, as I did when I was building my new temporal table, using SYSDATETIME();

DECLARE @AsOf datetime2(0) = SYSDATETIME(); --This time will be just after 8:00 PM unless I waited 4+ hours.

SELECT *
FROM  Sales.SalesOrder FOR SYSTEM_TIME AS OF @AsOf;
GO

Nothing at all:

SalesOrderId Data                           ValidStartTime              ValidEndTime
------------ ------------------------------ --------------------------- ---------------------------

Change SYSDATETIME() to SYSUTCDATETIME(), and you will get back the same data as you will without the FOR SYSTEM_TIME AS OF. Of course, it is a VERY unlikely usage of the FOR SYSTEM_TIME clause to only get back the current data in the table when you can just not have the clause, unless you are building a tool and you do something like:

SET @asOfParameterValue = ISNULL(@asOfParameterValue,SYSUTCDATETIME())

As most people are going to want to work in their specific time zone, we can change our queries to convert the time zone on the parameter.  So we can change the data using the AT TIME ZONE function as I covered in my previous blog on that subject:

DECLARE @TimeLocal datetime2(0) = SYSDATETIME(); --AT TIME ZONE will not work with a literal
DECLARE @AsOf datetime2(0) = @TimeLocal AT TIME ZONE 'Eastern Standard Time' AT TIME ZONE 'UTC';

SELECT SalesOrder.SalesOrderId, SalesOrder.Data,
       SalesOrder.ValidStartTime AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS ValidStartTime,
       SalesOrder.ValidEndTime AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS ValidStartTime
FROM  Sales.SalesOrder FOR SYSTEM_TIME AS OF @AsOf;
GO

This returns:

SalesOrderId Data                           ValidStartTime                     ValidStartTime
------------ ------------------------------ ---------------------------------- ----------------------------------
1            0000000001                     2017-09-17 20:07:25 -04:00         9999-12-31 18:59:59 -05:00
2            0000000002                     2017-09-17 20:07:25 -04:00         9999-12-31 18:59:59 -05:00

Then you can enter any time in place of SYSDATETIME() in Eastern Standard Time, and it will work as desired. Note that the end time actually ends up in a different time zone offset than the start time. It is an impossibly large time in any case.

If you are building an application that needs to run in any time zone, you could change this to parameterize the time zones:

DECLARE @TimeZone sysname = 'Eastern Standard Time'; --This could even be a parameter to a stored procedure, or value in a table
DECLARE @TimeLocal datetime2(0) = SYSDATETIME() --AT TIME ZONE will not work with a literal
DECLARE @AsOf datetime2(0) = @TimeLocal AT TIME ZONE @TimeZone AT TIME ZONE 'UTC';

SELECT SalesOrder.SalesOrderId, SalesOrder.Data,
       SalesOrder.ValidStartTime AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone AS ValidStartTime,
       SalesOrder.ValidEndTime AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone AS ValidStartTime
FROM  Sales.SalesOrder FOR SYSTEM_TIME AS OF @AsOf;
GO

This works great, and interestingly, if you are working with a timezone that is + hours, it also works. The end time is always 9999-12-31 23:59:59 (plus a fractional part corresponding the precision you set on the datetime2 datatype of the start and end times). Removing the SYSTEM_TIME AS OF, so we get back data (note I also tested this with datetime2(7) datatypes for the start and end times):

DECLARE @TimeZone sysname = 'Romance Standard Time'; --This could even be a parameter to a stored procedure

SELECT SalesOrder.SalesOrderId, SalesOrder.Data,
      SalesOrder.ValidStartTime,
       SalesOrder.ValidStartTime AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone AS ValidStartTime,
       SalesOrder.ValidEndTime,
       SalesOrder.ValidEndTime AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone AS ValidStartTime
FROM  Sales.SalesOrder;
GO

This returns:

SalesOrderId Data        ValidStartTime       ValidStartTime              ValidEndTime         ValidStartTime
------------ ----------- -------------------- --------------------------- -------------------- ---------------------------
1            0000000001  2017-09-18 00:07:25  2017-09-18 02:07:25 +02:00  9999-12-31 23:59:59  9999-12-31 23:59:59 +00:00
2            0000000002  2017-09-18 00:07:25  2017-09-18 02:07:25 +02:00  9999-12-31 23:59:59  9999-12-31 23:59:59 +00:00

The end time has no offset. So it is technically the same time as it was before the AT TIME ZONE functions were applied, while the start time works fine.

Published Sunday, September 17, 2017 7:56 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Archives

Privacy Statement