MATLAB Answers

Conversion and Merger of date and time using datetime

1 view (last 30 days)
The data have two different colum, date and time. Date is in date format and time is in string format. I want to create an another column including both date and time together.
The code I tried below: Although I managed to do that but can't able to see the milliseconds
Data = parquetread('P3007768_2020-11-01-23-59-00.parquet');
fun = @(s)[repmat('0',1,3-numel(s)),s];
Data.h_m_s_ms = regexprep(Data.h_m_s_ms,'\d+$','${fun($&)}');
Data.Date = datetime(Data.h_m_s_ms, "InputFormat","HH:mm:ss:SSS","Format","HH:mm:ss.SSS");
Data.Date = Data.yyyy_mm_dd + timeofday(Data.Date);
Data.Date = datetime(Data.Date ,"InputFormat", "dd-MM-yyyy HH:mm:ss.SSS");
Thanks in advance!
Maitreyee
Results look like the below

Accepted Answer

Peter Perkins
Peter Perkins on 26 Jul 2021
You are correct to use datatime to parse those times of day with : as the decimal separator; duration can't currently do that. All that part looks correct. I think it's this line
Data.Date = Data.yyyy_mm_dd + timeofday(Data.Date);
that's messing you up, because the output will have the same format as Data.yyyy_mm_dd, which is presumaby dd-MM-yyyy HH:mm:ss. And then this line
Data.Date = datetime(Data.Date ,"InputFormat", "dd-MM-yyyy HH:mm:ss.SSS");
looks like it's fixing that, but InputFormat doesn't set the format of the output, so it has no effect. Sewt Date.yyyy_mm_dd's format and you'll be all set:
>> d1 = datetime('today','Format','dd-MMM-uuuu HH:mm:ss.SSS')
d1 =
datetime
26-Jul-2021 00:00:00.000
>> d2 = datetime('01:02:03:004','InputFormat','HH:mm:ss:SSS') % millis hidden for now
d2 =
datetime
26-Jul-2021 01:02:03
>> d = d1 + timeofday(d2) % uses d1's format, which includes .SSS
d =
datetime
26-Jul-2021 01:02:03.004
  1 Comment
Maitreyee Dey
Maitreyee Dey on 27 Jul 2021
Thanks Peter, It worked. I just did a little change as I am processing and overwritting the whole database. And I can't change the original format of the data.
So, I just chaged the d2
d2 = datetime('01:02:03:004','Format','HH:mm:ss:SSS')
and it worked perfectly.
See below,
Many thanks and really appreciated! I was stuck in this stage for a long time.

Sign in to comment.

More Answers (1)

dpb
dpb on 26 Jul 2021
Didn't do you any favors in using the ":" for the separator of the msec field, did they? :)
But, the above looks like should work -- but you don't show or give us the resulting table.
How about attaching a small section of the above original table as a .mat file? Say something like
tTmp=[head(Data);tail(Data)];
save Data tTmp
Hopefully that will have a set of fields at the bottom that don't have the single-digit s field so have both types of the input format field to deal with.
I don't see what the table wouldn't be showing the .Date field above with the desired format unless something went wrong above and you had an error you didn't mention.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!