Ambiguity in daylight saving time

Demo scripts below.

Background

Most data professionals will have to deal with dates at one point or another. Most often, we don’t have the luxury of choosing the data types in the source systems we work with, so some interesting challenges may come up.

One such challenge is daylight saving time (or summer time, in Europe), when people turn their clocks back or forward an hour to have more hours of sunlight during our daily life (this is not universal, but is observed throughout most of Europe and North America, among other places).

In Europe, summer time starts on the last Sunday of March, at 01:00 UTC, when the Central European Time (+01:00) is replaced by Central European Summer Time (+02:00) and ends on the last Sunday of October at 01:00 UTC, when this is reversed.

From a data perspective, this is generally not an issue when changing to summer time, as the 2nd hour of the day is simply “lost” – the minute after 01:59 is 03:00. However, things can get more tricky when reverting to standard time. When reverting to standard time we repeat the 2nd hour of the day – the minute after 02:59 is 02:00.

The problem

Let us say we wish to convert our time-stamped data to UTC, so we don’t have to worry about this potential ambiguity. In Microsoft SQL Server, one way to fairly easily handle the conversion is using the AT TIME ZONE syntax.

We must be vigilant, however. If we are not careful with the time settings on our servers, these two times of the day can easily become indistinguishable, if we don’t keep track of which row was written first.

Suppose we have some data generation that writes a new row of data to a table every hour, using local time. In this example, I include the actual universal time only for reference, in the real case, this was not available.

The data looks something like this (assuming Datetime_Local is of type DATETIME2(0)):

On Microsoft SQL Server, we can use the AT TIME ZONE syntax to convert this:

SELECT Datetime_Local,
    CAST(Datetime_Local AT TIME ZONE 'Central European Standard Time' AT TIME ZONE 'UTC' AS DATETIME2(0)) AS Datetime_UTC
FROM DateTime_Table

The first AT TIME ZONE converts the DATETIME2 column to DATETIMEOFFSET, at the specified time zone (Central European Standard Time), and the second AT TIME ZONE converts the time zone to UTC.

Notice that every hour is still represented in UTC.

If we look at the switch back to normal time, we run in to problems:

Using AT TIME ZONE, we now end up with two rows yielding identical DateTime_UTC values

The solution

There are a few ways to solve this issue, but most of them require configurations that might not always be available, if we don’t have control over the data generation environment.

I personally prefer to just use data types that include time zone information, like DATETIMEOFFSET, whenever possible, as this removes any ambiguity.

Other solutions include:

• Using UTC functions like SYSUTCDATETIME() instead of SYSDATETIME() to make sure the timestamp is not localized
• Being certain that your server is configured to use UTC and does NOT observe daylight saving time when generating timestamps – I have had customers insist their server was using UTC, when in fact it was just localized to +00:00, but still changed time zone to +01:00 in the spring (and back to +00:00 in fall). This may or may not be what prompted this rant 😄
• Using other indicators like an identity column on the original table, assuming it is ever increasing and the time stamp is well defined when written

Demo scripts

If you want to try to play around with the scenario yourself, I’ve included some scripts to reproduce the behaviour here:

Prepare environment

DROP TABLE IF EXISTS DateTime_Table;

CREATE TABLE DateTime_Table (
    DateTime_Original DATETIMEOFFSET(0),
    Datetime_Local DATETIME2(0)
);

WITH CTE AS (
    /* Spring */
    SELECT CAST('2025-03-29 23:00:00 +00:00' AS DATETIMEOFFSET(0)) AS DateTime_Original UNION ALL
    SELECT CAST('2025-03-30 00:00:00 +00:00' AS DATETIMEOFFSET(0)) UNION ALL
    SELECT CAST('2025-03-30 01:00:00 +00:00' AS DATETIMEOFFSET(0)) UNION ALL
    SELECT CAST('2025-03-30 02:00:00 +00:00' AS DATETIMEOFFSET(0)) UNION ALL
    /* Autumn */
    SELECT CAST('2025-10-25 23:00:00 +00:00' AS DATETIMEOFFSET(0)) UNION ALL
    SELECT CAST('2025-10-26 00:00:00 +00:00' AS DATETIMEOFFSET(0)) UNION ALL
    SELECT CAST('2025-10-26 01:00:00 +00:00' AS DATETIMEOFFSET(0)) UNION ALL
    SELECT CAST('2025-10-26 02:00:00 +00:00' AS DATETIMEOFFSET(0))
)
INSERT INTO DateTime_Table (DateTime_Original, Datetime_Local)
SELECT DateTime_Original, CAST(DateTime_Original AT TIME ZONE 'Central European Standard Time' AS DATETIME2(0)) AS DateTime_Local
FROM CTE;
GO

Results in spring:

SELECT DateTime_Original,
    Datetime_Local,
    CAST(Datetime_Local AT TIME ZONE 'Central European Standard Time' AT TIME ZONE 'UTC' AS DATETIME2(0)) AS Datetime_UTC
FROM DateTime_Table
WHERE DateTime_Original BETWEEN '2025-03-29' AND '2025-03-31'
ORDER BY DateTime_Original;

Results in autumn:

SELECT DateTime_Original,
    Datetime_Local,
    CAST(Datetime_Local AT TIME ZONE 'Central European Standard Time' AT TIME ZONE 'UTC' AS DATETIME2(0)) AS Datetime_UTC
FROM DateTime_Table
WHERE DateTime_Original BETWEEN '2025-10-25' AND '2025-10-27'
ORDER BY DateTime_Original;