Sql-server – Why is AT TIME ZONE nondeterministic

sql serversql-server-2016timezone

SQL Server 2016's AT TIME ZONE appears to be nondeterministic. However, I haven't been able to find documentation officially stating this or giving a rationale as to the reasoning behind it.

Why is AT TIME ZONE nondeterministic?

Example Showing Non-Determinism

Executing:

CREATE TABLE Test (
    LegacyTimestamp DATETIME,
    Timestamp AS LegacyTimestamp AT TIME ZONE 'Eastern Standard Time' PERSISTED
);

Returns the following error:

Msg 4936, Level 16, State 1, Line 1
Computed column 'Timestamp' in table 'Test' cannot be persisted because the column is non-deterministic.

Best Answer

AT TIME ZONE employs some logic to calculate Daylight Savings Time. DST offset values are not immutable (they are subject to change via windows updates) and are contained externally in the Windows registry, so therefor the AT TIME ZONE function cannot be deterministic since it is relying on external data.

Similarly, this is why sys.time_zone_info is a view and not a static reference table, it needs to be calculated depending on the registry values which have the most up to date timezone information.

Related Solutions

PostgreSQL – AT TIME ZONE with Zone Name Bug?

Actually, the documentation says clearly that the time zone name and abbreviation will behave differently.

In short, this is the difference between abbreviations and full names: abbreviations always represent a fixed offset from UTC, whereas most of the full names imply a local daylight-savings time rule, and so have two possible UTC offsets. Reference

FWIW, that same reference also says

We do not recommend using the type time with time zone (though it is supported by PostgreSQL for legacy applications and for compliance with the SQL standard).

Mysql – How to get SQL of single time zone

Try this:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo/America/Sao_Paulo America/Sao_Paulo

Here a sample output:

INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
SET @time_zone_id= LAST_INSERT_ID();
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('America/Sao_Paulo', @time_zone_id);
INSERT INTO time_zone_transition (Time_zone_id, Transition_time, Transition_type_id) VALUES
 (@time_zone_id, -1767214412, 2)
,(@time_zone_id, -1206957600, 1)
,(@time_zone_id, -1191362400, 2)
,(@time_zone_id, -1175374800, 1)
,(@time_zone_id, -1159826400, 2)
,(@time_zone_id, -633819600, 1)
,(@time_zone_id, -622069200, 2)
,(@time_zone_id, -602283600, 1)
,(@time_zone_id, -591832800, 2)
,(@time_zone_id, -570747600, 1)
,(@time_zone_id, -560210400, 2)
,(@time_zone_id, -539125200, 1)
...
;
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
 (@time_zone_id, 0, -11188, 0, 'LMT')
,(@time_zone_id, 1, -7200, 1, 'BRST')
,(@time_zone_id, 2, -10800, 0, 'BRT')
;

From MySQL documentation

mysql_tzinfo_to_sql tz_file tz_name

This syntax causes mysql_tzinfo_to_sql to load a single time zone file tz_file that corresponds to a time zone name tz_name

Example Showing Non-Determinism

Best Answer

Related Solutions

PostgreSQL – AT TIME ZONE with Zone Name Bug?

Mysql – How to get SQL of single time zone

Related Question