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

Question

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

Abradant

Do you observe DST in your timezone? If so, would you really want to introduce the possibility of ambiguous history? – Pyrology

@Damien_The_Unbeliever: I see what you meant, but is not the case. There is only one server and all users are in the same zone. If all history tables records changes using current time instead of UTC I don't see any problem. – Abradant

Do you or do you not observe daylight savings time in that time zone? – Habiliment

@Abradant - my point was that in many timezones, about once a year they experience the "same" hour twice. (In the UK, it's 1am - 2pm) – Pyrology

Because if you do, when you get to the time where you move one hour back, you're going to get "duplicate" or "overlapping" data, and you cannot distinguish the records happening before time was adjusted from those happening after. With UTC, time just moves forward. – Habiliment

That make sense now, thank you both, guys, for the explanation. I have to adapt my queries. Cheers. – Abradant

Answer 1

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.

Pyrology

Thank you Damien, I didn't notice that. My mistake. – Abradant

Answer 2

I created a udf to handle this for me

CREATE FUNCTION [dbo].[udfGetLocalDateTime](@StartDate datetime)
RETURNS datetime
AS
BEGIN
RETURN (dateadd(hour,(cast(datepart(hour,sysdatetime()) as int) - 
cast(datepart(hour,sysutcdatetime()) as int)),@startdate))
END

It takes your server date and subtracts the utc time to get the local time offset then applies that to the source date. You can use this for either start or end date and it should support Daylight savings time as well since it is based on your current server time.

Skirret

I like this solution because it does not require hard-coded time-zone names, but works it out. Thanks. – Jeffiejeffrey