SysStartTime & SysEndTime on SQL Temporal Table

Question

Post reply

SysStartTime & SysEndTime on SQL Temporal Table

LLSQL42

SSC Enthusiast

Points: 199
More actions
July 1, 2020 at 10:00 am

#3766643
Hi,

I having a table called tableA, is a main table
I had enable temporal for the table, thus automatic temporal table is tableA_Histories
Example
tableA (System-Versioned)
tableA_Histories (History)
When i update or delete record from tableA, temporal auto reflect and move my existing record
as history into tableA_Histories.
SysStartTime & SysEndTime auto insert as UTC.

My question is below:-
1. Can i customize the script, so i can insert my specify datetime into temporal history column?
2. Can i customize my column value before temporal take effect?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736128 More actions · Answer 1

I don't believe you can change from UTC, which is expected. You should use the AT TIME ZONE to convert to local time.

What customization for the time did you have in mind?

LLSQL42 SSC Enthusiast Points: 199 More actions · Answer 2

Hi , thanks replied.

Example

tableA - Main, my record as below

[Name] [Age] [Updated_Date_Time]

"Benny" "30" "2020-07-01 10:00:00.000"

When the record being updated, it will auto reflect in temporal history , tableA_Histories as below,

Example, i update main table record , from Age "30" to Age "31", it will auto captured the current datetime (UTC) in temporal table

[Name] [Age] [Updated_Date_Time] [SysDateTime] [SysEndTime]

"Benny" "30" "2020-07-01 10:00:00.000" <Current Date Time UTC> <Current Date Time UTC>

Can i define the value for <Current Date Time UTC>?

dkultasev SSC Rookie Points: 34 More actions · Answer 3

It seems that you are doing something wrong. Normally you don't need to update the history records. If you need to get the update time not in UTC, you just need to convert it to the proper timezone as was suggested, for example (note offset value):

SELECT SYSUTCDATETIME() AT TIME ZONE 'Eastern Standard Time', SYSUTCDATETIME()

But, sometimes you do need that, for example I change these values when writing unit tests. To do that, you need to turn off temporal table functionality, do all needed updates and enable it again. This is the procedure I use for that. Note, that normally you do not need that! All temporal tables functionality is based on UTC, so if you'll use FOR SYSTEM_TIME ... syntax you'll get wrong results

CREATE PROCEDURE tSQLt.p_perform_temporal_table_dml
    @temporal_table_name sysname
  , @dml_query           NVARCHAR(MAX)
AS
    BEGIN
        DECLARE @query NVARCHAR(MAX);

        SET @query = FORMATMESSAGE(
                         '
ALTER TABLE %s SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE %s DROP PERIOD FOR SYSTEM_TIME;
'
                       , @temporal_table_name
                       , @temporal_table_name);

        EXEC (@query);

        EXEC (@dml_query);

        SET @query = FORMATMESSAGE(
                         '
ALTER TABLE %s
ADD PERIOD FOR SYSTEM_TIME(sysstart, sysend);
ALTER TABLE %s SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=%s_history));
'
                       , @temporal_table_name
                       , @temporal_table_name
                       , @temporal_table_name);

        EXEC (@query);
    END;
GO

This reply was modified 4 years, 4 months ago by dkultasev.

Steve Jones - SSC Editor SSC Guru Points: 736128 More actions · Answer 4

I agree. You shouldn't be updating these. The reason is that if you move the server/db to a new time zone, or clients move, they can always determine the actual time of the update. UTC provides a baseline that you can easily convert.