Import datetime with MESZ format

Hi,

I want to import an csv file with the following datetime format:

24.Sep 2021 14:09:00 MESZ

I had another csv with a different datetime format, where I used

Size_Datum_in_char = size(Datum_in_char,1);
Datum_full = [];
for i = 1:Size_Datum_in_char
    T = datetime(Datum_in_char(i,1),'TimeZone','local','InputFormat','yyyy-MM-dd''T''HH:mm:ssX');
    Datum_full = [Datum_full; T];
end

Where Datum_in_char is the imported matrix of the csv's first row with the datetime stored as char.

Now I tried:

T = datetime(Datum_in_char(i,1),'TimeZone','local','InputFormat','dd.MM yyyy hh:mm:ss XXXX');

but It seams, that I cannot import the "MESZ" with Xes. Any ideas, what I could do?

Thank you.

Answer 1

[Cris LaPierre]

I don't believe MATLAB is able to automatically recognize the timezone code. Importing might be a two step process where the dates are first read in as strings, then the codes are replaced with UTC offsets, which then allows the data to be converted to datetimes.

d='24.Sep 2021 14:09:00 MESZ';
dd = replace(d,'MESZ','+02:00');
D = datetime(dd,'InputFormat','dd.MMM yyyy HH:mm:ss xxxxx','TimeZone','UTC')

See the datetime documentation page to learn more about the Format and TimeZone properties.

[Tamara Szecsey]

Replacing the MESZ is brilliant. Never thought of that. But it's still not working. I used Z instead of xxxxx and then the compiler says

Unable to convert '1.Okt 2021 00:00:00 +02:00' to datetime using the format 'dd.MMM yyyy HH:mm:ss Z'. If the date/time text
contains day, month, or time zone names in a language foreign to the 'en_US' locale, those might not be recognized. You can
specify a different locale using the 'Locale' parameter.

I then added 'Locale','de_DE' into datetime. But now the error message is again:

Unable to convert '24.Sep 2021 10:00:00 +02:00' to datetime using the format 'dd.MMM yyyy HH:mm:ss Z' and locale 'de_DE'.

[Cris LaPierre]

Using Z should work as well.

d='24.Sep 2021 14:09:00 MESZ';
dd = replace(d,'MESZ','+02:00');
D = datetime(dd,'InputFormat','dd.MMM yyyy HH:mm:ss Z','TimeZone','UTC')

D = datetime
   24-Sep-2021 12:09:00

Would it be possible to share your file? You can attach it using the paperclip icon.

[Tamara Szecsey]

Yes, this works until the for loop reaches October, which is in german format.

I attached the csv with the dates. Should I upload my code too?

[Cris LaPierre]

There appears to be a discrepancy is what the offical/expected German abbreviations for the months are and what is being used in your csv file. Namely, the expected German abbreviation for 'Septempber' is 'Sept' instead of 'Sep' (reference).

So it reads September fine using the default (English) locale, and it reads October fine is using the de_DE locale, but it can't do both without some tweaking. See this example:

% English locale
d=["24.Sep 2021 14:09:00";"30.Okt 2021 16:33:00"];
D_eng = datetime(d,'InputFormat','dd.MMM yyyy HH:mm:ss')

D_eng = 2×1 datetime array
   24-Sep-2021 14:09:00
   NaT                 

% German locale
D_de = datetime(d,'InputFormat','dd.MMM yyyy HH:mm:ss','Locale','de_DE')

D_de = 2×1 datetime array
   NaT                 
   30-Oct-2021 16:33:00

% Change 'Sep' to 'Sept'
dd=replace(d,'Sep','Sept');
D3 = datetime(dd,'InputFormat','dd.MMM yyyy HH:mm:ss','Locale','de_DE')

D3 = 2×1 datetime array
   24-Sep-2021 14:09:00
   30-Oct-2021 16:33:00

With that discovery, I would probably read your file doing the following. I am assuming all the data is in German timezone.

% Each line is contained in double quotes, so define multiple delimiters
opts = detectImportOptions("TS_input.csv","Delimiter",{'"',','},'VariableNamingRule','preserve');
opts.ConsecutiveDelimitersRule = "join";
opts.LeadingDelimitersRule = "ignore";
opts.TrailingDelimitersRule = "ignore";
% read in Timestamps as strings
opts = setvartype(opts,"Timestamp","string");
% Set columns 2-4 to double
opts = setvartype(opts,2:4,"double");
% Import table
T = readtable("TS_input.csv",opts);

% Reformat Timestamp data to have no timezone, use expected abbreviations
T.Timestamp = erase(T.Timestamp," MESZ");
T.Timestamp = replace(T.Timestamp,'Sep','Sept');

% Convert Timestampt to a datetime
T.Timestamp = datetime(T.Timestamp,'InputFormat','dd.MMM yyyy HH:mm:ss','Locale','de_DE');
% Set Timezone (optional)
T.Timestamp.TimeZone = "Europe/Berlin";

% view 
head(T)

ans = 8×4 table
         Timestamp          Trend Flags    Status    Value (W-hr)
    ____________________    ___________    ______    ____________

    24-Sep-2021 10:00:00         1           0        8.8946e+08 
    24-Sep-2021 10:01:00         0           0        8.8946e+08 
    24-Sep-2021 10:02:00         0           0        8.8946e+08 
    24-Sep-2021 10:03:00         0           0        8.8946e+08 
    24-Sep-2021 10:04:00         0           0        8.8946e+08 
    24-Sep-2021 10:05:00         0           0        8.8946e+08 
    24-Sep-2021 10:06:00         0           0        8.8946e+08 
    24-Sep-2021 10:07:00         0           0        8.8946e+08 

tail(T)

ans = 8×4 table
         Timestamp          Trend Flags    Status    Value (W-hr)
    ____________________    ___________    ______    ____________

    30-Oct-2021 16:26:00         0           0        9.0412e+08 
    30-Oct-2021 16:27:00         0           0        9.0412e+08 
    30-Oct-2021 16:28:00         0           0        9.0412e+08 
    30-Oct-2021 16:29:00         0           0        9.0412e+08 
    30-Oct-2021 16:30:00         0           0        9.0412e+08 
    30-Oct-2021 16:31:00         0           0        9.0412e+08 
    30-Oct-2021 16:32:00         0           0        9.0412e+08 
    30-Oct-2021 16:33:00         0           0        9.0412e+08 

[Tamara Szecsey]

Oh, that's weird, because the csv was generated from other sensors. Good to know though.

So you would recomment to import this as a table? I imported the data I need as two matrices.

This is helping me a lot, thank you.

[Cris LaPierre]

Ultimately it depends on what you need to do, but my default approach is to create tables. If they are new to you, the most helpful page for me was How to Access Data in Tables.