In this post we’ll look at the behaviour of a SQL Server application that needs to deal with events that are logged over the period of a UK clock change where the clocks change from GMT (Greenwich Mean Time) to BST (British Summer Time).
The Problem
Let’s say we have an events table with a timestamp column, the timestamp is the UTC time at which the event happened:
CREATE DATABASE EventManagement;
GO
ALTER DATABASE EventManagement SET COMPATIBILITY_LEVEL = 160;
GO
USE EventManagement;
GO
CREATE TABLE dbo.[Event]
(
EventId INT,
EventType INT,
EventTimeUTC DATETIME2
);
INSERT INTO dbo.[Event] VALUES
(1,3,'2024-03-31T00:59:59Z'),
(2,2,'2024-03-31T01:00:00Z'),
(3,4,'2024-03-31T02:12:13Z'),
(4,2,'2024-03-31T00:59:59Z');
SELECT *
FROM dbo.[Event];
Our data looks like this – four events logged, each with an event type and an event time:
If we have an application which is used across multiple timezones, how would we represent these dates in the user’s local timezone rather than in UTC?
One option is to specify the timezone we wish to see the time represented in (which requires the date to be converted to a time type). An example using a hardcoded date is below, for simplicity:
SELECT CONVERT(DATETIME,'2024-03-31T00:59:59Z') AT TIME ZONE 'GMT Standard Time';
We can make this dynamic, converting to the time zone of the machine on which SQL Server is installed using the following solution. Note that CURRENT_TIMEZONE_ID() requires SQL Server 2022 or above (there are ways to achieve this pre-2022 using undocumented procedures but I won’t cover that here):
SELECT CONVERT(DATETIME,'2024-03-31T00:59:59Z') AT TIME ZONE CURRENT_TIMEZONE_ID();
Both return the same value on my machine since my timezone is GMT Standard Time:
SELECT CONVERT(DATETIME,'2024-03-31T00:59:59Z') AT TIME ZONE 'GMT Standard Time';
SELECT CONVERT(DATETIME,'2024-03-31T00:59:59Z') AT TIME ZONE CURRENT_TIMEZONE_ID();
SELECT CURRENT_TIMEZONE_ID();
AT TIME ZONE will return the specified input time as a value in the DATETIMEOFFSET data type, which I think of as a DATETIME value that also stores the offset from UTC (the +XX:XX part). The time 2024-03-31 00:59:59 +00:00 means “This happened at 2024-03-31 00:59:59 in the timezone you have specified which was +00:00 hours ahead of UTC at this time”.
To return the data to the user in our application, in the local timezone, our query would be:
SELECT EventId,
EventType,
EventTimeUTC,
EventTimeUTC AT TIME ZONE CURRENT_TIMEZONE_ID() AS EventTimeLocal
FROM dbo.[Event];
This gives us the result:
Houston, we have a problem
We can see from the dates in this output that events 2 and 3 have a different offset from the other records as they occurred after the daylight saving time clock change, which occurred at 1AM UTC on the 31st March 2024. The offset represents the 1 hour that BST is ahead of UTC.
We can also see from our EventTimeUTC column that Event 3 is 72 minutes after event 2 but only 12 minutes after event 2 according to the EventTimeLocal aliased column.
To clarify what is happening, we can run:
SELECT
CONVERT(DATETIME2,'2024-03-31T00:00:00Z') AT TIME ZONE 'GMT Standard Time',
CONVERT(DATETIME2,'2024-03-31T01:00:00Z') AT TIME ZONE 'GMT Standard Time',
CONVERT(DATETIME2,'2024-03-31T02:00:00Z') AT TIME ZONE 'GMT Standard Time',
CONVERT(DATETIME2,'2024-03-31T03:00:00Z') AT TIME ZONE 'GMT Standard Time';
Note that columns two and three both return the same time. The reason for this is that 1AM on the 31st March 2024 didn’t exist as a valid time in this timezone – it was skipped completely (00:59:59 became 02:00:00 a second later) so 01:00:00 and 02:00:00 on the 31st March 2024 are the same local time.
What I would want to see (at least, what makes sense to me personally) when presenting these events in the local time zone, would be the following times for events 1-4 respectively:
00:59:59
02:00:00
03:12:13
00:59:59
I found that to get the result I wanted, I needed to convert the EventTimeUTC column to a DATETIMEOFFSET using AT TIME ZONE and then convert that to a DATETIME type (to remove the offset information):
SELECT EventId,
EventType,
EventTimeUTC,
CONVERT(DATETIME2,CONVERT(DATETIMEOFFSET, EventTimeUTC) AT TIME ZONE CURRENT_TIMEZONE_ID()) AS EventTimeLocal
FROM dbo.[Event];
With that, I get the result I want:
This approach raises some issues worth considering:
- When storing dates as date type (DATETIME, DATETIME2 etc) it needs to be apparent which timezone the dates relate to (either in the column name itself or in documentation) usually, for timezone-aware applications, this is UTC. Alternatively, we could have an additional column which stores the timezone.
- If the dates are indeed stored as a DATETIME in a specified timezone, we can hit an issue in the winter when daylight saving time ends. The clocks go back from 01:59 to 01:00 – if an event is recorded at 1AM, is this 1AM +00:00 or 1AM +01:00? If we don’t store the offset, we will never be able to know
Conclusion
What I learned from this exercise is that handling dates and times is open to error if you are assuming a time zone and if you are not using UTC. When writing an application from scratch, consider using UTC or explicitly storing the the timezone along with the date / time so the time is explicit. Even if you are not expecting your application to be used outside a given timezone, without an explicit offset you will never be able to determine with certainty when events that occurred during the clock-change hour actually took place.
Finally, during my research for this topic, I came across this video, which I found humorous and made me feel less bad for finding this confusing!
References / Further Reading
Computerphile – The Problem with Time & Timezones
Erik Darling – AT TIME ZONE Performance
Erik Darling – Time Zones and Offsets