Go from Local Time to UTC with respect to the dates I have!

Hi guys, I need your expertise on that.

I have a column with dates and time (see attached) which is in Local Time Zone (Europe/Athens) and I want to convert it to UTC. I know this is pretty straight forward but the problem lies here:

If you notice on the attached .xlsx, arrows between 25103 and 25104 are missing the date 29-Mar-2015 03:00:00. That is because the weather station was using Daylight Saving Time for the entire year (UTC+0300), and I tried to change that and keep UTC+0200 for wintertime. Therefore I missed 29-Mar-2015 04.00 (which was the time that Greece changed from UTC+0200 to UTC+0300) and I have a double entry for 25-Oct-2015 03.00 ( (which was the time that Greece changed from UTC+0300 to UTC+0200).

When I try to go from LT to UTC with

LT.TimeZone = 'UTC';

I am losing information about the two errorous dates I mentioned (29-Mar-2015 03:00:00 and 25-Oct-2015 03.00). Which means that I am creating a column with all dates starting from 31/12/2014 21.00 to 31/12/2015 22.00.

Is there a solution to my problem? I would appreciate your thoughts...

Answer 1

[Star Strider]

The data in your Excel file need a bit of preprocessing, then the conversion is straightforward:

D = readtable('Date-Time.xlsx','ReadVariableNames',0);
D.Var1 = datetime(strrep(D.Var1, '''', ''), 'TimeZone','Europe/Athens');

then to do the conversion:

Original = D.Var1(1:5,:)                                                    % Information Only (Delete)
D.Var1.TimeZone = 'UTC';
New = D.Var1(1:5,:)                                                         % Information Only (Delete)

producing:

Original = 

  5×1 datetime array

   31-Dec-2014 23:00:00
   31-Dec-2014 23:00:00
   31-Dec-2014 23:00:00
   31-Dec-2014 23:00:00
   31-Dec-2014 23:00:00


New = 

  5×1 datetime array

   31-Dec-2014 21:00:00
   31-Dec-2014 21:00:00
   31-Dec-2014 21:00:00
   31-Dec-2014 21:00:00
   31-Dec-2014 21:00:00

[Daphne PARLIARI]

OK but the New table still creates the date 29-Mar-2015 03:00:00, for which I don't have data therefore I don't want it.....

[Star Strider]

Then either delete it or assign NaN to the variables associated with it. The time lost ot the transitions from Standard Time to Daylight Time are lost forever. The opposite transition creates duplicated times.

If you simply want to subtract two hours from the weather station time (since it was on Daylight Time the entire year), just subtract two hours (or whatever the correct conversion is) from the weather station time:

D = readtable('Date-Time.xlsx','ReadVariableNames',0);
D.Var1 = datetime(strrep(D.Var1, '''', ''), 'TimeZone','Europe/Athens');
Original = D.Var1(1:5,:)                                                    % Information Only (Delete)
D.Var1 = D.Var1 - hours(2);
New = D.Var1(1:5,:)                                                         % Information Only (Delete)

With that, there is no transition:

Check = D.Var1(25122:25132)

produces:

Check = 

  11×1 datetime array

   29-Mar-2015 02:00:00
   29-Mar-2015 02:00:00
   29-Mar-2015 02:00:00
   29-Mar-2015 02:00:00
   29-Mar-2015 02:00:00
   29-Mar-2015 02:00:00
   29-Mar-2015 04:00:00
   29-Mar-2015 04:00:00
   29-Mar-2015 04:00:00
   29-Mar-2015 04:00:00
   29-Mar-2015 04:00:00

[Daphne PARLIARI]

Therefore you are suggesting to assign NaT to the double entry (25-Oct-2015 03.00) at Original table and assign NaT to the "extra" entry (29-Mar-2015 03:00:00) at New table?

[Daphne PARLIARI]

It worked!! Thank you so much!!!

[Star Strider]

As always, my pleasure!

It took a bit to figure out what you actually wanted as a result. After that, devising an alternative proposed solution was straightforward.

[Daphne PARLIARI]

You saved me, I was thinking on that for straight 10 hours. Thank you! <3

[Star Strider]

As always, my pleasure!

Have fun with your research!