Removing characters from a datetime format to make it recognizable to MATLAB

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');

Answer 1

[Dyuman Joshi]

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 = 16×7 table
          DateTime            SensorID             Name            NodeID    Input      Value       EngineeringUnit
    _____________________    __________    ____________________    ______    _____    __________    _______________

    "2022-08-31 16:59:51"    3.7754e+05    New SMT-A2 Sensor       31462      20           1e+09      -2.4977e+05  
    "2022-08-31 16:59:51"    3.7755e+05    New SMT-A2 Sensor       31462      22           1e+09      -2.4976e+05  
    "2022-08-31 16:59:51"    3.7754e+05    New SMT-A2 Sensor       31462      21           1e+09      -2.4975e+05  
    "2022-08-31 16:59:51"    3.7754e+05    Internal Temperature    31462       5        1.13e+05           22.546  
    "2022-08-31 16:59:51"    3.7754e+05    Internal RH             31462       6      2.4045e+06           59.825  
    "2022-08-31 16:59:51"    3.7754e+05    Battery                 31462       7            4355             4355  
    "2022-08-31 16:59:51"    3.7754e+05    New SMT-A2 Sensor       31462      17           1e+09      -2.4976e+05  
    "2022-08-31 16:59:51"    3.7754e+05    New SMT-A2 Sensor       31462      18           1e+09      -2.4976e+05  
    "2022-08-31 16:59:51"    3.7754e+05    New SMT-A2 Sensor       31462      19           1e+09      -2.4977e+05  
    "2022-08-31 17:00:37"    3.7754e+05    New SMT-A2 Sensor       31462      17           1e+09      -2.4976e+05  
    "2022-08-31 17:00:37"    3.7754e+05    New SMT-A2 Sensor       31462      18           1e+09      -2.4976e+05  
    "2022-08-31 17:00:37"    3.7754e+05    Battery                 31462       7            4355             4355  
    "2022-08-31 17:00:37"    3.7755e+05    New SMT-A2 Sensor       31462      22           1e+09      -2.4976e+05  
    "2022-08-31 17:00:37"    3.7754e+05    New SMT-A2 Sensor       31462      21           1e+09      -2.4975e+05  
    "2022-08-31 17:00:37"    3.7754e+05    New SMT-A2 Sensor       31462      20           1e+09      -2.4977e+05  
    "2022-08-31 17:00:37"    3.7754e+05    New SMT-A2 Sensor       31462      19           1e+09      -2.4977e+05  

data.DateTime = datetime(data.DateTime,'InputFormat','yyyy-MM-dd HH:mm:ss')

data = 16×7 table
          DateTime           SensorID             Name            NodeID    Input      Value       EngineeringUnit
    ____________________    __________    ____________________    ______    _____    __________    _______________

    31-Aug-2022 16:59:51    3.7754e+05    New SMT-A2 Sensor       31462      20           1e+09      -2.4977e+05  
    31-Aug-2022 16:59:51    3.7755e+05    New SMT-A2 Sensor       31462      22           1e+09      -2.4976e+05  
    31-Aug-2022 16:59:51    3.7754e+05    New SMT-A2 Sensor       31462      21           1e+09      -2.4975e+05  
    31-Aug-2022 16:59:51    3.7754e+05    Internal Temperature    31462       5        1.13e+05           22.546  
    31-Aug-2022 16:59:51    3.7754e+05    Internal RH             31462       6      2.4045e+06           59.825  
    31-Aug-2022 16:59:51    3.7754e+05    Battery                 31462       7            4355             4355  
    31-Aug-2022 16:59:51    3.7754e+05    New SMT-A2 Sensor       31462      17           1e+09      -2.4976e+05  
    31-Aug-2022 16:59:51    3.7754e+05    New SMT-A2 Sensor       31462      18           1e+09      -2.4976e+05  
    31-Aug-2022 16:59:51    3.7754e+05    New SMT-A2 Sensor       31462      19           1e+09      -2.4977e+05  
    31-Aug-2022 17:00:37    3.7754e+05    New SMT-A2 Sensor       31462      17           1e+09      -2.4976e+05  
    31-Aug-2022 17:00:37    3.7754e+05    New SMT-A2 Sensor       31462      18           1e+09      -2.4976e+05  
    31-Aug-2022 17:00:37    3.7754e+05    Battery                 31462       7            4355             4355  
    31-Aug-2022 17:00:37    3.7755e+05    New SMT-A2 Sensor       31462      22           1e+09      -2.4976e+05  
    31-Aug-2022 17:00:37    3.7754e+05    New SMT-A2 Sensor       31462      21           1e+09      -2.4975e+05  
    31-Aug-2022 17:00:37    3.7754e+05    New SMT-A2 Sensor       31462      20           1e+09      -2.4977e+05  
    31-Aug-2022 17:00:37    3.7754e+05    New SMT-A2 Sensor       31462      19           1e+09      -2.4977e+05  

[Aaron DeSantis]

Thank you for the assistance.

Answer 2

[Steven Lord]

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'

s = '2022-08-31 16:59:51+00'

d = datetime(s, InputFormat = "yyyy-MM-dd HH:mm:ss+00")

d = datetime
   31-Aug-2022 16:59:51

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'

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")

d = datetime
   16:59:51.79

[Dyuman Joshi]

Steven, I think I have seen you recommend this method before. This approach is quite better than what I have used.

[Peter Perkins]

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'")

ans = datetime
   31-Aug-2022 16:59:51

datetime("2022-08-31 16:59:51+00",Format="uuuu-MM-dd HH:mm:ssx",TimeZone="UTC")

ans = datetime
   2022-08-31 16:59:51+00

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]

@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]

Ah, right. I skimmed it too fast.