Posts Tagged dst

DATETIMEOFFSET in T-SQL

Posted by sqlpete in coding on July 31, 2021

In my experience, most developers will shudder when you mention DATETIMEOFFSETs to them. I’ve just started working on an application where (most of) the date/time data is stored as DATETIMEOFFSET, and it’s been so long since I’ve had to deal with them, I’ve forgotten most of what I knew! Hence, this blog post to remind myself of the key points, and hopefully it’ll be of use to someone else. (This isn’t supposed to be an exhaustive look at everything to do with DATETIMEOFFSET, just some pointers to the issues I’ve encountered.)

The basics: T-SQL (the variant of SQL used by Microsoft SQL Server) has an extra date/time data type, DATETIMEOFFSET, that stores an offset relating to a timezone. For example, you could represent the time 07:30 (using the 24-hour clock) in Algeria on the 26th July as:

2021-07-26 07:30:00.000 +01:00

And 12:00 (12pm midday) in India as:

2021-07-26 12:00:00.000 +05:30

The datatype stores an offset in minutes; in the string representations shown above, that’s the +01:00 for Algeria, and +05:30 for India.

Coordinated Universal Time, known as UTC, is the ‘base’ timezone, for which the offset is zero minutes, +00:00. In some senses, it’s a successor to Greenwich Mean Time, aka GMT. For our purposes, they’re identical, and interchangeable.

We can see what a DATETIMEOFFSET looks like converted to UTC, using the AT TIME ZONE syntax:

DECLARE @dto_Algeria DATETIMEOFFSET = '2021-07-26 07:30:00.000 +01:00'
DECLARE @dto_India DATETIMEOFFSET   = '2021-07-26 12:00:00.000 +05:30'

SELECT @dto_Algeria AT TIME ZONE 'UTC'
SELECT @dto_India AT TIME ZONE 'UTC'


2021-07-26 06:30:00.0000000 +00:00
2021-07-26 06:30:00.0000000 +00:00

(For all the possible timezones, see e.g. Get a List of Supported Time Zones in SQL Server (T-SQL).)

The times are the same in UTC — if something happens at 7:30am local time in Algeria, and 12pm local time in India, it’s happening simultaneously. We can use the standard DATENAME and DATEPART functions to get the offsets, both the string representation and the value in minutes: 

SELECT DATENAME(tzoffset, @dto_Algeria), DATEPART(tzoffset, @dto_Algeria)
SELECT DATENAME(tzoffset, @dto_India), DATEPART(tzoffset, @dto_India)


+01:00    60
+05:30   330

All the usual date/time functions (DATENAME, DATEPART, DATEADD, DATEDIFF) work with DATETIMEOFFSET in the same way they do with the DATETIME and DATETIME2 datatypes, although you might need to be careful when doing a DATEDIFF between DATETIMEOFFSETs with different offsets. So far, so straightforward — why would anyone have issues working with DATETIMEOFFSETs?

The problems

1. Nomenclature

People mix up offset and timezone, all the time. However, they aren’t synonyms.

SQL Server uses the timezone — e.g. ‘UTC’, ‘Central European Standard Time’, ‘India Standard Time’ — to determine the offset: +00:00, +01:00, +05:30. You can’t get back to the timezone by just knowing the offset. The DATETIMEOFFSET datatype doesn’t store the timezone, just the offset.

Annoyingly, to get the offset using DATENAME/DATEPART, SQL Server lets you abbreviate tzoffset to tz, which leads people to refer to timezone and offset interchangeably.

How do we get the current timezone that SQL Server is using? In modern versions, it’s simply:

SELECT CURRENT_TIMEZONE()

but in older versions, you have to retrieve it from the registry:

DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread
	'HKEY_LOCAL_MACHINE'
	,'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
	,'TimeZoneKeyName',@TimeZone OUT
SELECT @TimeZone

GMT Standard Time

(taken from here: Get the Current Time Zone of the Server in SQL Server (T-SQL))

2. DST – Daylight Saving Time

The timezone isn’t the only thing that affects the offset — it’s whether DST, Daylight Saving Time, is being applied as well. I chose India and Algeria in the examples above because neither country uses DST. Here in the UK, we ‘gain’ an hour in March and ‘lose’ an hour in October, but not on fixed dates. For example, you can look here When do the clocks change? to see when the next relevant date is. (This period in the UK summer is called British Summer Time, BST). Whether the DATETIMEOFFSET has been determined using DST is not stored in the datatype. If this is relevant to you, you’ll have to keep track of this yourself.

NB: As far as I can tell, all countries in Europe observe DST.

3. SQL Server names the timezones incorrectly

For me, this is the biggest irritant: in SQL Server, the timezone ‘GMT Standard Time’ is not GMT, it is the timezone of the British Isles, i.e. it includes BST. We can show this quite easily: 

;WITH cte_Numbers AS
(
	-- Alternative to using a 'Numbers' table:
	SELECT
		TOP 10
		N = ROW_NUMBER() OVER (ORDER BY v1.number)
	FROM master..spt_values v1
	CROSS JOIN master..spt_values v2

), cte_Date AS
(
	SELECT
		N
		,dt = DATEADD(second, N-1, '2021-03-28 00:59:55')
	FROM cte_Numbers

), cte_AtTimeZone AS
(
	SELECT
		*
		,dto = dt AT TIME ZONE 'GMT Standard Time'
	FROM cte_Date
)
SELECT
	N
	,dt
	,dto
	,[tzoffset] = DATEPART(tzoffset, dto)
FROM cte_AtTimeZone
GO

N    dt                      dto                                tzoffset
---- ----------------------- ---------------------------------- -----------
1    2021-03-28 00:59:55.000 2021-03-28 00:59:55.000 +00:00     0
2    2021-03-28 00:59:56.000 2021-03-28 00:59:56.000 +00:00     0
3    2021-03-28 00:59:57.000 2021-03-28 00:59:57.000 +00:00     0
4    2021-03-28 00:59:58.000 2021-03-28 00:59:58.000 +00:00     0
5    2021-03-28 00:59:59.000 2021-03-28 00:59:59.000 +00:00     0
6    2021-03-28 01:00:00.000 2021-03-28 02:00:00.000 +01:00     60
7    2021-03-28 01:00:01.000 2021-03-28 02:00:01.000 +01:00     60
8    2021-03-28 01:00:02.000 2021-03-28 02:00:02.000 +01:00     60
9    2021-03-28 01:00:03.000 2021-03-28 02:00:03.000 +01:00     60
10   2021-03-28 01:00:04.000 2021-03-28 02:00:04.000 +01:00     60

(March 28th was when we entered BST this year — it’s always a Sunday at 1am.) As you can clearly see, GMT in SQL Server terms isn’t proper GMT, because it has taken BST into account. If you want real GMT, you have to convert to the timezone ‘UTC’.

Note that you can convert a DATETIME to a DATETIMEOFFSET using ‘AT TIME ZONE’, but the implicit conversion to DATETIMEOFFSET gives you the version with millisecond resolution, i.e. 3 decimal places; but you can have up to 7 decimal places (see the documentation here datetimeoffset (Transact-SQL) for more information).

It’s not just ‘GMT Standard Time’, other timezones are affected: e.g. “Central European Time (CET) is a standard time which is 1 hour ahead of Coordinated Universal Time (UTC)” (as detailed here), but in fact, SQL Server incorporates ‘Central European Summer Time’ into this timezone to put it an hour ahead in the summer. I wouldn’t be surprised if other timezones are treated in the same way — please check before you use them!

NB: I can see that someone has raised a similar issue here: W. Europe Standard time appears to be incorrect by 1 hour

4. Comparing with DATETIME / DATETIME2

Here’s the biggest issue I’ve come across: comparing DATETIME and DATETIME2 with DATETIMEOFFSET. You have to be very careful, you cannot just mix OFFSET data with non-OFFSET and hope everything’ll be alright – it won’t. This catches people out: 

SELECT GETDATE()
SELECT SYSDATETIMEOFFSET()
SELECT CAST(GETDATE() AS DATETIMEOFFSET)
SELECT GETDATE() AT TIME ZONE 'GMT Standard Time'

2021-07-31 18:27:24.187
2021-07-31 18:27:24.1871686 +01:00
2021-07-31 18:27:24.1866667 +00:00
2021-07-31 18:27:24.187 +01:00

Look at the third result: we’ve CAST the current date/time to a DATETIMEOFFSET, but the offset it’s returning us is ‘+00:00’, not the ‘+01:00’ we were expecting (seeing as the server is set to ‘GMT Standard Time’, which is currently an hour ahead of UTC). If we want the correct offset, we either use the syntax from the fourth SELECT statement, ‘AT TIME ZONE…’ or we can add the offset ourselves manually:

SELECT TODATETIMEOFFSET(CAST(GETDATE() AS DATETIMEOFFSET), 60)
2021-07-31 18:27:24.1866667 +01:00

If you see SQL code comparing a DATETIMEOFFSET with a function of GETDATE() or SYSDATETIME(), then it needs thoroughly checking – there’s a good chance it’s wrong!

Summary

Using offsets and timezones takes some getting used to, but I’ve found that as long as I remember not to mix OFFSET and non-OFFSET data and types, then everything’s generally ok. There’s a blog post that could be written about the wisdom of using the DATETIMEOFFSET datatype in the first place — in the few places I’ve seen it used, it was definitely a case of ‘premature globalisation’, assuming the same database was going to be used if/when the company expanded overseas. In fact, for the application I’m working on, the UK and US databases have completely separate databases and code, so it’s a non-issue.

, , , , , , , ,

Leave a comment