MATLAB: Daylight saving time Conversion with datetime is 1 hour too early

datetimedaylight saving timeMATLABtimezone

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

Best Answer

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.
Related Question