Conversion and Merger of date and time using datetime

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

Answer 1

[Peter Perkins]

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

[Maitreyee Dey]

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.

Answer 2

[dpb]

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.