Temporal Table Time Zones

Louis Davidson talks about how to translate temporal table start and end dates to your local time zone:

In my first 5 blogs on Temporal, I failed to note something pretty important about their usage. The start and end times for the row (and it follows, the historical rows) are likely not in your local time zone. They are stored in UTC time zone. This is obviously good because of that pesky daylight saving time issue where 1:30 AM occurs twice each year (here in the US, the time occurs on the first Sunday of November).

Unless you live in London, England or someplace where the offset from UTC is 0, it can make working with these tables confusing, because most people rarely work in UTC time, and even rarer is to think in UTC time when most of your data is likely in your local time zone. So you write your query and use local time…and then, most likely, data is returned…but not necessarily the data you actually desired.

Click through to see ways of translating those values.

Related Posts

DateTime Arithmetic

Claudio Silva points out a difference between the DATETIME and DATETIME2 data types: I’m currently working on a SQL code migration from Firebird to SQL Server and I hit an error that I haven’t seen for some time. The error message is the following: Msg 206, Level 16, State 2, Line 4 Operand type clash: datetime2 is […]

Read More

Helper Predicates And Multi-Column Filters

Rob Farley has an interesting post on optimizing a lookup when you have separate date and time columns: Here we see a Seek Predicate that looks for OrderDate values between two values that have been worked out elsewhere in the plan, but creating a range in which the right values must exist. This isn’t >= […]

Read More

Categories

September 2017
MTWTFSS
« Aug Oct »
 123
45678910
11121314151617
18192021222324
252627282930