adiw adiw - 2 months ago 32
SQL Question

SQL temporal table: It is possible to change logging time from UTC to current time?

I like temporal tables in SQL Server 2016 and I want to use this in a new project.
Unfortunately, it seems that sql is logging UTC time in history table, not the current time. It is possible to change this ?

To see what I mean, please run the following code and notice the difference from CreatedIn and SysStartTime columns.

CREATE TABLE dbo.tblTest
(
ID int NOT NULL PRIMARY KEY CLUSTERED IDENTITY (1,1)
,SomeColumn varchar(50) NULL
,CreatedIn datetime2(2) NOT NULL DEFAULT GETDATE()
,SysStartTime datetime2(2) GENERATED ALWAYS AS ROW START NOT NULL DEFAULT GETDATE()
,SysEndTime datetime2(2) GENERATED ALWAYS AS ROW END NOT NULL
,PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.tblTestHistory));
GO

INSERT INTO dbo.tblTest (SomeColumn) VALUES ('Some value');
GO

SELECT * FROM dbo.tblTest

Answer

It is not. From Temporal Table Usage Scenarios:

System-versioned temporal tables store values for period columns in UTC time zone, while it is always more convenient to work with local time zone both for filtering data and displaying results. The following code example shows how to apply filtering condition that is originally specified in the local time zone ...

You'll notice that they don't say that it defaults to storing UTC. They state that it does store UTC. But they do provide an example of how queries may be adapted, using the new AT TIME ZONE feature.