How to convert string to datetime format in parquet file format within parquet datastore?

The below is the sample parquet datastructure that i am using.

This is the structure of the data. Where both the date and time are in string format. This is the data that have been converted in csv to parquet. I have tried many times to convert second and third column into datetime format. So that I can write some query to operate/plot on perticular duration for analysis.

So the question is how to convert the string into second and third column into datetime format in parquet file format?

I am using Matlab 2020a.

Many Thanks in advance!

Answer 1

[Stephen23]

>> D = {'2020/1/1' ;'2020/1/1'  ;'2020/1/1'   };
>> T = {'00:01:0:0';'00:01:0:60';'00:01:0:320'};

Method one: sscanf and duration:

>> M = sscanf(sprintf(' %s:',T{:}),'%f:',[4,Inf]).';
>> dt = datetime(D,'InputFormat','yyyy/M/d') + duration(M(:,1),M(:,2),M(:,3),M(:,4))
dt = 
   01-Jan-2020 00:01:00
   01-Jan-2020 00:01:00
   01-Jan-2020 00:01:00
>> dt.Second % check the milliseconds:
ans =
         0
    0.0600
    0.3200

Method two: regexprep:

>> C = strcat(D,'@',regexprep(T,{':(\d)$',':(\d\d)$'},{':00$1',':0$1'}));
>> dt = datetime(C,'InputFormat','yyyy/M/d@H:m:s:SSS')
dt = 
   01-Jan-2020 00:01:00
   01-Jan-2020 00:01:00
   01-Jan-2020 00:01:00
>> dt.Second % check the milliseconds:
ans =
         0
    0.0600
    0.3200

[Maitreyee Dey]

Thank you for the answer.

Sorry i might not explain properly what I need,

I have 1440 files for a day and each file consists of 6000 rows indicating each 10ms intervals of data.

I have successfully converted them into csv to parquet but can't able convert the two strings colum into datetime format. I have attached one file in csv format from the datalake as I won't be able to attached the parquet file. PFA. If this one

As you mentioned the below how can i automatically setect these values instead?

D = {'2020/1/1' ;'2020/1/1' ;'2020/1/1' };

T = {'00:01:0:0';'00:01:0:60';'00:01:0:320'};

i.e., if the file name

data = readtable('P3007768_2020-01-01-00-59-00.csv');

D = data(:,2);

T = data(:,3);

and then what will be the code of method one and two? how I can convert them into datetime format into another column or may merge these two columns into one and save a new file?

Where the column looks like

01-Jan-2020 00:59:00:00

......

01-Jan-2020 00:59:59:990

[Stephen23]

D = data.yyyy_mm_dd;
T = data.h_m_s_ms;
...
data.datetime = dt;

https://www.mathworks.com/help/matlab/ref/writetable.html

"Where the column looks like"

Change the datetime format as required:

https://www.mathworks.com/help/matlab/ref/datetime.html#buhzxmk-1-Format