Convert datetime to datestr without losing milisecond data

I am calling in time and date data from an excel spreadsheet using the readcell function, with the resultant column consisting of datetime arrays (column 4). I tried to set the variable type as 'string' to solve the below problem, but it is still calling in collumn 4 as datetime.

opts = detectImportOptions(fname);
opts.Sheet = 2;
opts = setvartype(opts, 'string');
Data = readcell(fname,opts);

The problem I am having is that the original data called in has milliseconds included:

>> Data{1,4}

ans =

datetime

22-Jul-2023 15:18:15.171

But when I convert the datetime data into a string:

datestr(ans)

the result is

'22-Jul-2023 15:18:15'

losing the millisecond information.

Basically, I want to search the next line to see if the next datetime occurs within 2 seconds of the original (ie. if Data{2,4} is within 2.00 seconds of Data{1,4}) without losing the milisecond info.

So either is there a different way of converting the datetime to a string or char or anything that will not lose the millisecond information?

Or is there another way of calling in the spreadsheet data in the first place?

The data I want to call in from the spreadsheet is:

collumn 1: file names (includes numbers and letters)

collumn 2: participant name (letters)

collumn 4: datetime

collumn 7: data (numbers)

Thanks in advance!

Answer 1

[Pratyush]

Hi Alexis,

I understand that you want to check if two 'datetime' objects are within 2 seconds of each other and you want to retain the millisecond information.

To ensure that you retain the millisecond information when working with 'datetime' objects in MATLAB, you don't need to convert them to strings. Instead, you can directly perform calculations and comparisons using the 'datetime' objects.

To compare two 'datetime' objects and check if they are within 2 seconds of each other, you can directly subtract them and use the 'seconds' function to get the difference in seconds:

% Assuming Data{1,4} and Data{2,4} are datetime objects
timeDiff = abs(Data{2,4} - Data{1,4});
if seconds(timeDiff) <= 2
    disp('The datetimes are within 2 seconds of each other.');
else
    disp('The datetimes are not within 2 seconds of each other.');
end

Regarding the import issue, if you want to import the datetime data as strings directly from the Excel file, you should specify the variable type for the specific column, not for all columns. Here's how you can do it:

% Define the file name
fname = 'your_excel_file.xlsx';

% Create import options for the specific Excel sheet
opts = detectImportOptions(fname, 'Sheet', 2);

% Set the variable type for column 4 as 'string'
opts = setvartype(opts, 4, 'string');

% Import the data
Data = readcell(fname, opts);

% Check the imported data type for column 4
disp(class(Data{1,4})); % This should display 'string'

This way, you are setting only column 4 to be imported as strings, which should preserve the format of the 'datetime', including milliseconds, as it is shown in the Excel file. If you then need to perform datetime comparisons, you can convert these string values back to 'datetime' objects with the appropriate format specifier:

% Convert the string with milliseconds back to datetime
dt_obj = datetime(Data{1,4}, 'InputFormat', 'dd-MMM-yyyy HH:mm:ss.SSS');
disp(dt_obj);

[Alexis]

Thanks - this is incredibly helpful :)

Answer 2

[Stephen23]

"I tried to set the variable type as 'string' to solve the below problem"

The best way to import Excel serial date numbers is as DATETIME objects. Do not import as string.

"But when I convert the datetime data into a string: datestr(ans)"

Why are you using deprecated DATESTR?

"is there a different way of converting the datetime to a string or char or anything that will not lose the millisecond information?"

Of course, just use STRING or CHAR or SPRINTF or COMPOSE or anything else of that ilk:

dt = datetime('now','Format','yyyy-MM-dd HH:mm:ss.SSS')

dt = datetime
   2023-12-14 06:28:40.204

char(dt)

ans = '2023-12-14 06:28:40.204'

string(dt)

ans = "2023-12-14 06:28:40.204"

sprintf("%s",dt)

ans = "2023-12-14 06:28:40.204"

"Or is there another way of calling in the spreadsheet data in the first place?"

READTABLE

Tip for the future: read the documentation of the functions you are using. When you open the DATESTR documentation this is at the very top, it already tells you the answer to your question:

[James Tursa]

Side Note: These methods work because of the Format. If you had a different Format they wouldn't. E.g.,

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

dt = datetime
   2023-12-14 16:53:41

char(dt)

ans = '2023-12-14 16:53:41'

string(dt)

ans = "2023-12-14 16:53:41"

sprintf("%s",dt)

ans = "2023-12-14 16:53:41"

second(dt)

ans = 41.5882

So, it is critical that you make sure the Format contains the fractional seconds you want before doing the conversion. They all missed the .5882 part above because those digits were not included in the Format.

[Alexis]

Thanks - I had used datestr becuase I had already used string and char and had the same problem across all methods. The format information is helpful.