# script: scripts/11_temporal_tables.sql | database: lab-cst | started (UTC): 2026-10-16 01:50:10 /* 11 - System-versioned temporal tables (CST database) Article claim: period columns (ValidFrom/ValidTo) are always UTC; FOR SYSTEM_TIME AS OF must be given UTC. */ SET NOCOUNT ON; PRINT '=== 11 Temporal tables on ' + DB_NAME() + ' (effective tz: ' + CURRENT_TIMEZONE_ID() + ')'; === 11 Temporal tables on lab-cst (effective tz: Central Standard Time) IF OBJECT_ID('tzlab.ProductPrice') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('tzlab.ProductPrice'), 'TableTemporalType') = 2 ALTER TABLE tzlab.ProductPrice SET (SYSTEM_VERSIONING = OFF); DROP TABLE IF EXISTS tzlab.ProductPrice, tzlab.ProductPriceHistory; CREATE TABLE tzlab.ProductPrice ( ProductID int NOT NULL CONSTRAINT PK_tzlab_ProductPrice PRIMARY KEY, ListPrice money NOT NULL, ChangedAtLocal datetime2(3) NOT NULL DEFAULT (GETDATE()), ValidFrom datetime2(3) GENERATED ALWAYS AS ROW START NOT NULL, ValidTo datetime2(3) GENERATED ALWAYS AS ROW END NOT NULL, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = tzlab.ProductPriceHistory)); INSERT tzlab.ProductPrice (ProductID, ListPrice) SELECT TOP (3) ProductID, ListPrice FROM SalesLT.Product ORDER BY ProductID; PRINT '--- 11.1 Period column vs GETDATE() default on the same row'; SELECT ProductID, ListPrice, ChangedAtLocal, ValidFrom, GETDATE() AS local_now, GETUTCDATE() AS utc_now, DATEDIFF(MINUTE, ChangedAtLocal, ValidFrom) AS validfrom_minus_local_min FROM tzlab.ProductPrice; --- 11.1 Period column vs GETDATE() default on the same row ProductID|ListPrice|ChangedAtLocal|ValidFrom|local_now|utc_now|validfrom_minus_local_min ---------|---------|--------------|---------|---------|-------|------------------------- 680|1431.5000|2026-10-15 20:50:11.080|2026-10-16 01:50:11.083|2026-10-15 20:50:11.130|2026-10-16 01:50:11.130|300 706|1431.5000|2026-10-15 20:50:11.080|2026-10-16 01:50:11.083|2026-10-15 20:50:11.130|2026-10-16 01:50:11.130|300 707|34.9900|2026-10-15 20:50:11.080|2026-10-16 01:50:11.083|2026-10-15 20:50:11.130|2026-10-16 01:50:11.130|300 -- Capture "the moment before the price change" in three ways, then change the price. WAITFOR DELAY '00:00:02'; DROP TABLE IF EXISTS #t; SELECT SYSDATETIME() AS t_local, SYSUTCDATETIME() AS t_utc, SYSDATETIMEOFFSET() AS t_offset INTO #t; WAITFOR DELAY '00:00:02'; UPDATE tzlab.ProductPrice SET ListPrice = ListPrice * 1.10, ChangedAtLocal = GETDATE(); SELECT * FROM #t; t_local|t_utc|t_offset -------|-----|-------- 2026-10-15 20:50:13.1854850|2026-10-16 01:50:13.1854850|2026-10-15 20:50:13.1854850 -05:00 PRINT '--- 11.2 AS OF (expect: 0 rows - local time is 5h before the rows existed)'; DECLARE @t datetime2(7) = (SELECT t_local FROM #t); SELECT 'AS OF local' AS q, ProductID, ListPrice, ValidFrom, ValidTo FROM tzlab.ProductPrice FOR SYSTEM_TIME AS OF @t; --- 11.2 AS OF (expect: 0 rows - local time is 5h before the rows existed) q|ProductID|ListPrice|ValidFrom|ValidTo -|---------|---------|---------|------- PRINT '--- 11.3 AS OF (expect: the ORIGINAL prices)'; DECLARE @t datetime2(7) = (SELECT t_utc FROM #t); SELECT 'AS OF utc' AS q, ProductID, ListPrice, ValidFrom, ValidTo FROM tzlab.ProductPrice FOR SYSTEM_TIME AS OF @t; --- 11.3 AS OF (expect: the ORIGINAL prices) q|ProductID|ListPrice|ValidFrom|ValidTo -|---------|---------|---------|------- AS OF utc|680|1431.5000|2026-10-16 01:50:11.083|2026-10-16 01:50:15.192 AS OF utc|706|1431.5000|2026-10-16 01:50:11.083|2026-10-16 01:50:15.192 AS OF utc|707|34.9900|2026-10-16 01:50:11.083|2026-10-16 01:50:15.192 PRINT '--- 11.4 AS OF passed directly (implicit conversion keeps local clock, drops offset?)'; DECLARE @t datetimeoffset(7) = (SELECT t_offset FROM #t); SELECT 'AS OF datetimeoffset' AS q, ProductID, ListPrice, ValidFrom, ValidTo FROM tzlab.ProductPrice FOR SYSTEM_TIME AS OF @t; --- 11.4 AS OF passed directly (implicit conversion keeps local clock, drops offset?) q|ProductID|ListPrice|ValidFrom|ValidTo -|---------|---------|---------|------- AS OF datetimeoffset|680|1431.5000|2026-10-16 01:50:11.083|2026-10-16 01:50:15.192 AS OF datetimeoffset|706|1431.5000|2026-10-16 01:50:11.083|2026-10-16 01:50:15.192 AS OF datetimeoffset|707|34.9900|2026-10-16 01:50:11.083|2026-10-16 01:50:15.192 PRINT '--- 11.5 AS OF (correct pattern)'; DECLARE @t datetime2(7) = (SELECT CAST(t_offset AT TIME ZONE 'UTC' AS datetime2(7)) FROM #t); SELECT 'AS OF offset->UTC' AS q, ProductID, ListPrice, ValidFrom, ValidTo FROM tzlab.ProductPrice FOR SYSTEM_TIME AS OF @t; --- 11.5 AS OF (correct pattern) q|ProductID|ListPrice|ValidFrom|ValidTo -|---------|---------|---------|------- AS OF offset->UTC|680|1431.5000|2026-10-16 01:50:11.083|2026-10-16 01:50:15.192 AS OF offset->UTC|706|1431.5000|2026-10-16 01:50:11.083|2026-10-16 01:50:15.192 AS OF offset->UTC|707|34.9900|2026-10-16 01:50:11.083|2026-10-16 01:50:15.192 PRINT '--- 11.6 History rows: ValidFrom/ValidTo in UTC vs ChangedAtLocal in Central'; SELECT 'history' AS src, ProductID, ListPrice, ChangedAtLocal, ValidFrom, ValidTo FROM tzlab.ProductPriceHistory UNION ALL SELECT 'current', ProductID, ListPrice, ChangedAtLocal, ValidFrom, ValidTo FROM tzlab.ProductPrice ORDER BY ProductID, ValidFrom; --- 11.6 History rows: ValidFrom/ValidTo in UTC vs ChangedAtLocal in Central src|ProductID|ListPrice|ChangedAtLocal|ValidFrom|ValidTo ---|---------|---------|--------------|---------|------- history|680|1431.5000|2026-10-15 20:50:11.080|2026-10-16 01:50:11.083|2026-10-16 01:50:15.192 current|680|1574.6500|2026-10-15 20:50:15.190|2026-10-16 01:50:15.192|9999-12-31 23:59:59.999 history|706|1431.5000|2026-10-15 20:50:11.080|2026-10-16 01:50:11.083|2026-10-16 01:50:15.192 current|706|1574.6500|2026-10-15 20:50:15.190|2026-10-16 01:50:15.192|9999-12-31 23:59:59.999 history|707|34.9900|2026-10-15 20:50:11.080|2026-10-16 01:50:11.083|2026-10-16 01:50:15.192 current|707|38.4890|2026-10-15 20:50:15.190|2026-10-16 01:50:15.192|9999-12-31 23:59:59.999 PRINT '--- 11.7 Session override to Tokyo does not move ValidFrom either'; SET TIME ZONE 'Tokyo Standard Time'; UPDATE tzlab.ProductPrice SET ListPrice = ListPrice + 1, ChangedAtLocal = GETDATE() WHERE ProductID = (SELECT MIN(ProductID) FROM tzlab.ProductPrice); SELECT ProductID, ChangedAtLocal AS changed_at_tokyo, ValidFrom, GETUTCDATE() AS utc_now FROM tzlab.ProductPrice WHERE ProductID = (SELECT MIN(ProductID) FROM tzlab.ProductPrice); SET TIME ZONE LOCAL; --- 11.7 Session override to Tokyo does not move ValidFrom either ProductID|changed_at_tokyo|ValidFrom|utc_now ---------|----------------|---------|------- 680|2026-10-16 10:50:15.427|2026-10-16 01:50:15.427|2026-10-16 01:50:15.430 # sqlcmd exit code: 0