Removing characters from a datetime format to make it recognizable to MATLAB
Show older comments
I have a large data file that includes date information that I would like to convert to a timetable. This is nothing new but the format used by SMT Research is not recognized in Matlab. They use '2022-08-31 16:59:51+00'. The last deliniation remains unused for all data enties, alway '+00' so I would like to remove it. I have atempted to use the erase and replace function but i get the error "First argument must be text." Can someone help with this. Code is below and the imput file and import function are attached.
%% Call imprort function
data = importfileANL("export-4-5-23.csv"); %inputs the specified CSV file as a table
% import function only works with the standard output of Analytics
%% Convert date info from a date string to a datetime array
data = replace(data.DateTime, "+00",""); % removes proprietary millisecond demotation not recognized in matlab
data.DateTime = datetime(data.DateTime,'InputFormat','yyyy-MM-dd HH:mm:ss');
Accepted Answer
The column with date time values is stored as a categorical array. Convert it to a string array and then use replace (you can also use erase)
data = importfileANL("export-4-5-23_shortened.csv");
data.DateTime = replace(string(data.DateTime),"+00","")
data.DateTime = datetime(data.DateTime,'InputFormat','yyyy-MM-dd HH:mm:ss')
1 Comment
Aaron DeSantis
on 8 Apr 2023
More Answers (1)
You can include literal characters in the InputFormat argument of a call to datetime. See the "Date and Time from Text with Literal Characters" example on the datetime documentation page.
s = '2022-08-31 16:59:51+00'
d = datetime(s, InputFormat = "yyyy-MM-dd HH:mm:ss+00")
If you want to read in the data with the numbers after the + as the fractional seconds you can do that too.
s = '2022-08-31 16:59:51+79'
I'll display just the time part of d, but the date part is still present.
d = datetime(s, InputFormat = "yyyy-MM-dd HH:mm:ss+SS", Format = "HH:mm:ss.SS")
4 Comments
Dyuman Joshi
on 9 Apr 2023
Steven, I think I have seen you recommend this method before. This approach is quite better than what I have used.
Peter Perkins
on 9 Apr 2023
Edited: Peter Perkins
on 9 Apr 2023
Yes, As Steve says, it is absolutely not true that MATLAB cannot recognize these timestamps.
What is true is that MATLAB will not recognize this automatically. Steve is interpreting the +00 as fractional seconds, but I suspect that it is a time zone offset. Do one of these two things:
datetime("2022-08-31 16:59:51+00",InputFormat="uuuu-MM-dd HH:mm:ss'+00'")
datetime("2022-08-31 16:59:51+00",Format="uuuu-MM-dd HH:mm:ssx",TimeZone="UTC")
depending on whether or not you want to preserve the timezone.
I imagine that the format is why Dyuman says that the timestamps are categorical. They should not be, and it's easy to use detectImportOptions to control that. You should read this file with readtimetable and options create using detectImportOptions.
Dyuman Joshi
on 9 Apr 2023
@Peter Perkins note that the OP is using their own method to import data, and thus the data for the datetime values is imported as categorical.
Peter Perkins
on 10 Apr 2023
Ah, right. I skimmed it too fast.
Categories
Find more on Data Type Conversion in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
