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

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.

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.