Datetime missing milliseconds values showing up as Nat

Hello,

I have a table with a column of data with the format Datetime.

-ds.SelectedFormats(1)={'%{yyyy-MM-dd HH:mm:ss.SS}D'};

Some values are showing up as NaT because they are missing values in the milliseconds(.SS) place. i.e '2018-03-15 08:49:28.(blank)' is showing up as NaT.

Could I add a statement to read through the table & Input .00 for the milliseconds places that are blank? or make an if statement to change the format to yyyy-MM-dd HH:mm:ss?

Thanks for any suggestions.

Answer 1

[Peter Perkins]

Anthony, it's not clear what your starting point is. If you have a file, some of whose rows contain timestamps with ms, some without, I think you can read in that field as text, and then convert to datetimes with two passes. Something like

dt = datetime(timestamps,'Format',fmt1);
i = isnat(dt);
dt(i) = datetime(timestamps(i),'Format',fmt2);

It's also possible that you could add .000 to the text timestamps, and it might even be easy by looking at the length of each element.

[Walter Roberson]

If you have the entries in text format then

timestamps_as_text = regexrep(timestamps_as_text, '(:\d\d)$', '$1.00');

and then it should work with a single format.

[Anthony Kenaga]

I am bringing the data in using datastore. I can set the formats for each column(timestap as datetime in this case) before I read it in.

The text type it defaults to is 'char' (cell array of character vectors). I am trying to find out how to change this to a string, according to matlab this is an option https://www.mathworks.com/help/matlab/ref/matlab.io.datastore.tabulartextdatastore.html. Under TextType

I tried >> ds.TextType=string but get the response below:

You cannot set the read-only property 'TextType' of TabularTextDatastore.

I will continue playing around with it and appreciate the help.

[Anthony Kenaga]

I followed Peter Perkins code with good results, had to add in a couple steps for converting data types. Below is the code for reference:

ds = tabularTextDatastore('S:\file');

%Set Table Parameters%

ds.NumHeaderLines=3;

ds.VariableNames={'Time','Record','Temperature','PedalForce'};

ds.SelectedFormats(2:4)={'%f', '%f', '%f'};

TTable = readall(ds);

%Fix missing millisecond value%

CharTime=TTable{1:end,1};%select timestamp data%

Tstr=string(CharTime);%convert to string%

dt = datetime(Tstr,'Format','yyyy-MM-dd HH:mm:ss.SS'); 

i = isnat(dt);

dt(i) = datetime(Tstr(i),'Format','yyyy-MM-dd HH:mm:ss.SS');

At this point I have all timestamp data in the correct form with no NaT results. And I am working on moving it back into the table with the 3 other columns of char vectors.

[Walter Roberson]

? The two formats you are using appear to be the same? I would have expected the second format to not have the .SS ?

[Anthony Kenaga]

Walter thanks for pointing this out. It seems you can run the last line as:

dt(i) = datetime(Tstr(i),'Format','yyyy-MM-dd HH:mm:ss.SS')

or

dt(i) = datetime(Tstr(i),'Format','yyyy-MM-dd HH:mm:ss');

and both will correct the NaT issue. I used the (ss.SS) method first & then cleared everything out and reran using (ss.). I'm not sure why both work it makes me question why it wouldn't work with the first instance

dt = datetime(Tstr,'Format','yyyy-MM-dd HH:mm:ss.SS');

I ran this and it gives me NaT, but then added either of the

dt(i) = lines and both fixed the issue.

[Walter Roberson]

Historically, datenum() without any format string has been known to be adaptable for any one consistent format, but having difficulty with mixed formats of input. Perhaps something similar is going on here with datetime(), that it is adapting as long as the input is consistent since you are only passing in a consistent subset of the data for the second call ?

[Anthony Kenaga]

Walter,

I see what you mean. Thanks for explaining the details and helping me resolve this issue. Have a good day.