Daylight saving time Conversion with datetime is 1 hour too early?

Hello everyone,

I am facing a problem with DST/local time zone conversion. My Timestamp Data imported from xlsx file looks like this:

'25.10.2020 01:58:32'
'25.10.2020 01:59:32'
'25.10.2020 02:00:32'
'25.10.2020 02:01:32'
'25.10.2020 02:02:32'
'25.10.2020 02:03:32'
...
'25.10.2020 02:57:32'
'25.10.2020 02:58:32'
'25.10.2020 02:59:32'
'25.10.2020 02:00:32'
'25.10.2020 02:01:32'
...
'25.10.2020 02:58:32'
'25.10.2020 02:59:32'
'25.10.2020 03:00:32'
'25.10.2020 03:01:32'

The time vector form the xlsx file is within a cell array and I try to convert the time vector from European timezone to UTC time (to avoid local time differences). Due to the switch in Daylight saving time in Europe (which takes place from 2 to 3 am on 25th of october) the timestamp contains the same 2.00 am hour twice.

My Code to convert the vector is the following, with "time" representing the time vector shown above :

       t_help = datetime(time,'InputFormat','dd.MM.yyyy HH:mm:SS','TimeZone','Europe/Amsterdam');
        t_UTC  = datetime(t_help, 'TimeZone', 'UTC');

The resulting time vector now looks like this:

'24-Oct-2020 23:58:00'
'24-Oct-2020 23:59:00'
'25-Oct-2020 01:00:00'
'25-Oct-2020 01:01:00'
'25-Oct-2020 01:02:00'
...
'25-Oct-2020 01:58:00'
'25-Oct-2020 01:59:00'
'25-Oct-2020 01:00:00'
'25-Oct-2020 01:01:00'
...
'25-Oct-2020 01:58:00'
'25-Oct-2020 01:59:00'
'25-Oct-2020 02:00:00'
'25-Oct-2020 02:01:00'
'25-Oct-2020 02:02:00'
...
'25-Oct-2020 02:58:00'
'25-Oct-2020 02:59:00'
'25-Oct-2020 03:00:00'
'25-Oct-2020 03:01:00'
'25-Oct-2020 03:02:00'

There is 1 hour missing in the UTC time Vector and the 1 am hour appears 2 times...To get additional information, I used the Format Input at datetime function like this:

t_help_add_infos = datetime(time(idx_time),'InputFormat','dd.MM.yyyy HH:mm:SS','TimeZone','Europe/Amsterdam','Format','dd.MM HH:mm z');

With the result:

'25.10 01:58 UTC+2'
'25.10 01:59 UTC+2'
'25.10 02:00 UTC+1'
'25.10 02:01 UTC+1'
'25.10 02:02 UTC+1'
'25.10 02:03 UTC+1'
...
'25.10 02:58 UTC+1'
'25.10 02:59 UTC+1'
'25.10 02:00 UTC+1'
'25.10 02:01 UTC+1'
'25.10 02:02 UTC+1'
...
'25.10 02:59 UTC+1'
'25.10 03:00 UTC+1'
'25.10 03:01 UTC+1'
'25.10 03:02 UTC+1'

Am I doing something wrong or is the original timestamp "wrong"? In my opinion the DST switch in datetime happens 1 hour too early, but I guess Im doing sth wrong here.

Thanks for any help in advance!

Best Regards

David

[Walter Roberson]

'24-Oct-2020 23:58:00'
'24-Oct-2020 23:59:00'
'25-Oct-2020 01:00:00'

What happened to hour 00 ?

[David Albus]

Thats what i meant with "missing" hour. The converted utc timestamp (t_utc) does not contain the 00 hour

Answer 1

[Walter Roberson]

When you datetime() those initial values with TimeZone set to Europe/Amsterdam, then it assumes the times are in local time in Amsterdam, with the time change already made. There are two different 01:15 local time that day, and MATLAB picks the later of the two, which one could argue is wrong. However, if it were to pick the earlier of the two, then you could also argue that is wrong.

To get around this, you have to attach timezone information to your inputs to resolve which of the two 01:15 you are talking about.

[David Albus]

Allright, then I will choose this Workaround, thanks for the fast reply :-)

[Peter Perkins]

Walter is dead on. With no UTC offset info in the timestamp, there's no way to know if '25.10.2020 02:01:32' is supposed to mean the first (DST) one or the second (ST) one. There are a few things you could do:

1) These data are coming from a spreadsheet. If you read them in as text, that suggests that they are text in the spreadsheet. Format those cells of the spreadsheet as time, not as text. I confess that I don't remember if Excel even supports timezones, so this may be a non-starter.

2) Modify whatever is creating the text to append UTC+2 or UTC+1 to each timestamp. No ambiguity, but likely this is not under your control.

3) Modify the datetimes after converting. The trick is to find the second duplicate block in each day and add an hour to those. You can use diff and look for negative differences to find the start of each block, and 1 hour differences to find the end.

4) If the timestamps are regular, maybe you don't even need to convert. Maybe you can generate them from scratch as something like datetime(2020,10,25,1,0:360,32).

5) If the data only span a year or two, you can hoke up some brute force code to adjust where needed by an hour.