Timestamp in ISO 8601 to yyyy-MM-dd HH:mm:ss

Hey there!

I am trying to parse the ISO8601 timestamp into readible format like 'yyyy-MM-dd HH:mm:ss'

However, no matter how hard I try, I couldn't be able to do the conversion.

I keep getting the message which reads "

>>> datetime('0000-00-00T14:26:21:000','InputFormat','yyyy-MM-dd''T''HH:mm:ss')

Error using datetime

Unable to convert '0000-00-00T14:26:21:000' to datetime using the format 'yyyy-MM-dd'T'HH:mm:ss'.

Can anyone please tell me what I am doing incorrectly in the syntax ?

[Stephen23]

'0000-00-00T14:26:21:000'
%                   ^ ISO 8601 specifies that decimal fraction must use dot or comma.

[Ercan]

Thank you Stephen23.

However, it's all I got from a sensor data table.

Timestamp (ISO8601)

0000-00-00T14:26:21:000

0000-00-00T14:26:21:001

0000-00-00T14:26:21:002

0000-00-00T14:26:21:003

0000-00-00T14:26:21:004

and goes on...

Should I first convert it into ISO standard?

[dpb]

Besides that, datetime can't handle the zero fields in the date string portion. Testing failed with any field being "00"

[Stephen23]

Building on dpb's comment: given that all date units are zero, perhaps a DURATION object would be more suitable.

Do any of the timestamps have non-zero date units? What is the range of expected times/dates?

[Ercan]

All of the timestamps have zero date units. But this is not important for my project. I just need to convert the time after "T" to a plottable time value. At this moment MATLAB won't allow me to plot the time against, say pressure.

Answer 1

[dpb]

It's a pit(proverbial)a(ppendage) when vendor code produces nonstandard formats...

S='0000-00-00T14:26:21:000';        % sample date string
datetime(extractAfter(S,'T'),'InputFormat','HH:mm:ss:SSS','Format',"HH:mm:ss.SSS")

ans = datetime
   14:26:21.000

converts the time portion with the behavior of datetime with a missing date to default to the current date.

Unfortunately, the limitations of what TMW has implemented for the duration class formats wipes out that as an alternative...

duration(extractAfter(S,'T'),'InputFormat','hh:mm:ss:SSS')

Error using duration
Unsupported format 'hh:mm:ss:SSS'. See the documentation of 'InputFormat' for valid formats.

datetime is more forgiving in letting the additional ":" pass when given as part of the format string; for some reason the developers didn't provide duration the same level of customization.

[Ercan]

I can't thank you enough, to be honest. Saved my day.

[dpb]

Here's where timeofday has its use, too...

S='0000-00-00T14:26:21:000';        % sample date string
timeofday(datetime(extractAfter(S,'T'),'InputFormat','HH:mm:ss:SSS'))

ans = duration
   14:26:21

returns a duration to wipe out the funky date portion.

Answer 2

[Cris LaPierre]

I would follow this example: Date and Time from Text with Literal Characters

DateStrings = {'2014-05-26T13:30-05:00';'2014-08-26T13:30-04:00';'2014-09-26T13:30Z'}

t = datetime(DateStrings,'InputFormat','uuuu-MM-dd''T''HH:mmXXX','TimeZone','UTC')

Your data is returning an error because the month-day data are zeros, which violates the ISO8601 date format spec:

• [MM] indicates a two-digit month of the year, 01 through 12.
• [DD] indicates a two-digit day of that month, 01 through 31.

[dpb]

@Cris, I can get the literal date string to match if do as above with the fractional seconds

DateStrings = {'0000-00-00T14:26:21:000'};
t=timeofday(datetime(DateStrings,'InputFormat','''0000-00-00T''HH:mm:ss:SSS','TimeZone','UTC'))

t = duration
   14:26:21

but I haven't been able to find in perturbation using "XXX" that succeeds with the OP's actual input forms.

t=timeofday(datetime(DateStrings,'InputFormat','''0000-00-00T''HH:mmXXX','TimeZone','UTC'))

Error using datetime
Unable to convert '0000-00-00T14:26:21:000' to datetime using the format ''0000-00-00T'HH:mmXXX'.

I think this one is still far simpler to just pull out the portion of the date string of interest; the bad formatting seems to break the parsing.

I don't think that's datetime's fault...

[Cris LaPierre]

Me either. I think your answer is the best you can do.

XXX is for capturing timezone (in the example, the timezone is in the format -5:00), but the OP's data does not have timezone. In that case, your first option is the correct approach.

t=timeofday(datetime('0000-00-00T14:26:21:000','InputFormat','''0000-00-00T''HH:mm:ss:SSS'))

t = duration
   14:26:21

Main point - this is not an ISO 8601 date. If it were, datetime could import it.

[dpb]

OK, thanks...I misinterpreted your "I would follow..." as saying it should work somehow.

I always forget about the "XXX" being part of the formatting string set; the doc for datetime is pretty disjointed in that it takes several jumps to get to the full table of possible formatting strings from the abbreviated list at the input description don't always go there.