Avoid datetime changes due to saving daylight

Good morning,

I have a timeserie from a .xlsx file. I import dates as datetime variable. All data are collected as solar time.

D=readtable(fullfile(path,file),opt,"UseExcel",false); %import a matrix
D.Properties.VariableNames{1}='Date'; %the first column are dates and read as datetime
D.Date.Format='dd/MM/yyyy HH:mm'; 
D.Date.TimeZone='Europe/Rome';

Unfortunatly, on March, 25th 2007 there was the shift from solar time (maybe in english is more correct refers to this as 'winter time') to legal time (maybe 'saving daylight time'). MATLAB, shift hours as follow:

Input datetime (original) datetime shifted by matlab

24/03/2007 22:00 24/03/2007 22:00

24/03/2007 23:00 24/03/2007 23:00

25/03/2007 00:00 25/03/2007 00:00

25/03/2007 01:00 25/03/2007 01:00

25/03/2007 02:00 25/03/2007 03:00

25/03/2007 03:00 25/03/2007 03:00

25/03/2007 04:00 25/03/2007 04:00

25/03/2007 05:00 25/03/2007 05:00

How could I avoid it and keep the original datetime?

[Luca D'Angelo]

The opposite happens when time returns to 'winter time'. With the same Time.Zone ('Europe/Rome') happens on October, 10th 2007. In this situation, data don't look changed but if I calculate the difference:

28/10/2007 01:00 - 28/10/2007 02:00= 02:00

[Walter Roberson]

The input entries you show on the left: What time UTC do each of them represent? What time local do you want them changed to?

Answer 1

[Eric Sofen]

Do you need to use time zones in your app? In MATLAB, you can work with "unzoned" datetimes, where Daylight Saving Time doesn't show up at all.

[Luca D'Angelo]

Thank you! I introduced it to be sure that it is possible the comparison among datetime data.

I try without specify Time.Zone.

Thank you again,

Luca

Answer 2

[Walter Roberson]

See https://www.mathworks.com/matlabcentral/answers/642250-daylight-saving-time-conversion-with-datetime-is-1-hour-too-early?s_tid=srchtitle

[Luca D'Angelo]

Thank you for your fast link attachment. Anyway, I'm not sure to well undestand it.

Since I'm developing an app with appdesigner, I cannot modify the input data before load it. Thus, any correction should be done once data are stored.

The suggested solution could be the third you linked me:

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.

I create a vector

for i=1:length(D.Date)
    if i+1<=length(D.Date)
        Diff(i,1)=D.Date(i+1)-D.Date(i,1);
    end
end

In this way I obtain a duration vector which contains mostly '01:00:00' but when there some '00:00:00' and '02:00:00'.

I try to act on those rows forcing +hours(1) or -hours(1) but it didn't work.

What am I doing wrong?

Even if it's off topic, do you have any suggestion to force the first day of the week as Monday? I wrote this but it looks silly and there are problems with the end and beginning of years.

Weeks(:,1)=year(D.Date); %year
Weeks(:,2)=week(D.Date);%number of week
Weeks(:,3)=weekday(D.Date);%day of week: modified as follows
Weeks(find(Weeks(:,3)==1),4)=7; %Sun-->Mon
Weeks(:,5)=Weeks(:,2);
Weeks(find(Weeks(:,3)==1),5)=Weeks(find(Weeks(:,3)==1),2)-1; %this causes some days as week 0
for i=2:7
    Weeks(find(Weeks(:,3)==i),4)=i-1;%shift days of week
end

Can you help me please?

Thanks!