MATLAB: Readtable and readtimetable can’t load timezone properly

datetimereadtableThingSpeaktimetime zone

I have a CSV generated from ThingSpeak, which contains a datetime containing a time zone identifier (CEST in my case). It looks like this:
created_at,entry_id,field1,field2,field3,latitude,longitude,elevation,status
2020-06-06 18:36:17 CEST,1,434,2.12121212121,30.3487192708,,,,
2020-06-06 18:36:33 CEST,2,433,2.11632453568,30.2823285934,,,,
2020-06-06 18:36:48 CEST,3,436,2.13098729228,30.4815006257,,,,
2020-06-06 18:37:03 CEST,4,433,2.11632453568,30.2823285934,,,,
Now, when I edit the file and remove " CEST" from all rows, readtimetable imports the file properly, with no issues. But when I leave it as is, readtable imports the field as text, while readtimetable refuses altogether.
I tried to specify the format manually, as so:
opts.VariableNames = ["created_at", "Var2", "field1", "Var4", "Var5", "Var6", "Var7", "Var8", "Var9"];
opts.SelectedVariableNames = ["created_at", "field1"];
opts.VariableTypes = ["datetime", "string", "double", "string", "string", "string", "string", "string", "string"];
opts = setvaropts(opts, "created_at", "InputFormat", "dd-MM-yyyy HH:mm:ss z", "TimeZone", 'Europe/Warsaw');
With this, readtimetable works but… all fields are turned into NaT.
Now, this is some weird behavior, because using the datetime function with one of those fields copied works flawlessly:
>> date = datetime("2020-06-06 18:36:17 CEST","InputFormat","yyyy-MM-dd HH:mm:ss z","TimeZone","Europe/Warsaw")
date = 
  datetime
   06-Jun-2020 18:36:17
Matlab's importer also can't handle it, when I use the CSV file as the input it wrongly detects the delimiter, once that's corrected and field type set to datetime, any way I tried configuring the input format just fails and results in NaTs. I even attempted to set the format to "yyyy-MM-dd HH:mm:ss 'CEST'", so the time zone gets treated as a string, but it still fails to recognize it as a correct datetime.
Using Matlab R2020a

Best Answer

What worked here for R2019b
>> opt=detectImportOptions('thing.csv','delimiter',',')
opt = 
  DelimitedTextImportOptions with properties:
   Format Properties:
                    Delimiter: {','}
                   Whitespace: '\b\t '
                   LineEnding: {'\n'  '\r'  '\r\n'}
                 CommentStyle: {}
    ConsecutiveDelimitersRule: 'split'
        LeadingDelimitersRule: 'keep'
                EmptyLineRule: 'skip'
                     Encoding: 'windows-1252'
   Replacement Properties:
                  MissingRule: 'fill'
              ImportErrorRule: 'fill'
             ExtraColumnsRule: 'addvars'
   Variable Import Properties: Set types by name using setvartype
                VariableNames: {'created_at', 'entry_id', 'field1' ... and 6 more}
                VariableTypes: {'char', 'double', 'double' ... and 6 more}
        SelectedVariableNames: {'created_at', 'entry_id', 'field1' ... and 6 more}
              VariableOptions: Show all 9 VariableOptions 
	Access VariableOptions sub-properties using setvaropts/getvaropts
        PreserveVariableNames: false
   Location Properties:
                    DataLines: [2.00 Inf]
            VariableNamesLine: 1.00
               RowNamesColumn: 0
            VariableUnitsLine: 0
     VariableDescriptionsLine: 0 
	To display a preview of the table, use preview
>> thing=readtable('thing.csv',opt);
>> thing.created_at=datetime(thing.created_at,"InputFormat","yyyy-MM-dd HH:mm:ss z", ...
                                    "TimeZone","Europe/Warsaw",'Locale','pl_PL')
thing =
  4×9 table
         created_at         entry_id    field1    field2    field3     latitude     longitude     elevation       status  
    ____________________    ________    ______    ______    ______    __________    __________    __________    __________
    06-Jun-2020 18:36:17      1.00      434.00     2.12     30.35     {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}
    06-Jun-2020 18:36:33      2.00      433.00     2.12     30.28     {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}
    06-Jun-2020 18:36:48      3.00      436.00     2.13     30.48     {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}
    06-Jun-2020 18:37:03      4.00      433.00     2.12     30.28     {0×0 char}    {0×0 char}    {0×0 char}    {0×0 char}
>>
The conversion via datetime that you demonstrated for the single line fails on R2019b w/o the ISO-639 local code.
Possibly using the locale and readtimetable might work.
The HINT in detectImportOptions to not use the dot notation to set parameters in a returned import object is key here -- simply building an object with the content of .delimiter as ',' doesn't work -- it takes parsing the file again with the delimiter identified to get stuff right. The date string with embedded blanks not delimited by "" is the big problem. I got around it above by passing the 'delimiter' named parameter when creating the import object.
Related Question