Convert HH:MM:SS.FFF to seconds

I have a large file of acceleration data with each line containing the time stamp. I want to read the file and convert the time stamp to seconds, i.e. the entry with time stamp "01:59:58.01" has 7198.01 seconds. My problem is that when the time stamp has zero milliseconds, the data logger cuts off the milliseconds so it outputs "01:59:58" instead of "01:59:58.01". When I try to use datestr on the time data, it cuts off the milliseconds for all timestamps. I have included an example below. Any ideas on how I can do this? I am using Matlab 2018. Thanks

% example of 5 lines of output from data logger. Filename is "Acceleration_2018_04_04_0159.dat"

"2018-04-04 01:59:58",592349164,0.982,1.697

"2018-04-04 01:59:58.01",592349165,0.233,1.865

"2018-04-04 01:59:58.02",592349166,0.796,1.196

"2018-04-04 01:59:58.03",592349167,-0.11,1.098

"2018-04-04 01:59:58.04",592349168,0.637,1.905

fid = fopen('Acceleration_2018_04_04_0159.dat');

data = textscan(fid,'%s %s %f %f %f','delimiter',' ,"','MultipleDelimsAsOne',1,'TreatAsEmpty','"NAN"')

% if i run the following lines the milliseconds are not included since the first time entry in the file cuts off
the milliseconds ,i.e. 01:59:58

test = datestr(data{1,2}(:),'HH:MM:SS.FFF');

[Y,M,D,H,M,S] = datevec(test);

t = (H*3600+M*60+S)

% if I instead, run the next 3 lines, which skips the first time entry then it works fine and the milliseconds
are included

test = datestr(data{1,2}(2:end),'HH:MM:SS.FFF');

[Y,M,D,H,M,S] = datevec(test);

t = (H*3600+M*60+S)

[Guillaume]

Which version of matlab are you on? Any reason why you're using datevec and datestr instead of the easier to use datetime?

Answer 1

[Guillaume]

Assuming you're on a not too ancient version of matlab:

t = readtable('Acceleration_2018_04_04_0159.dat');
d = datetime(t.Var1, 'InputFormat', 'yyyy-MM-dd HH:mm:ss.SS', 'Format', 'yyyy-MM-dd HH:mm:ss.SS');
d(isnat(d)) = datetime(t.var1(isnat(d)), 'InputFormat', 'yyyy-MM-dd HH:mm:ss');
t.Var1 = d

Replace Var1 by the name of the 1st column is the file contains a header.

To get the duration in seconds in a new table variable:

t.DurSeconds = seconds(t.Var1 - t.Var1(1));

[phillo]

Thanks Guillaume, this works great

Answer 2

[Walter Roberson]

datestr has difficulty processing data in mixed format. It can make adjustments for missing fraction of a second if all of the entries are missing that, but not if only some of them are. One way to deal with this is to use regexprep to fix the missing milliseconds. There was a post showing the code about a week ago. Search for datestr datetime regexprep

[phillo]

Thanks Walter. So, I tried the following based on the older post, which fixes the milliseconds. I noticed that regexprep and datevec are quite slow in a loop. The data logger is sampled at 100Hz and creates an output file every 30 minutes for a total duration of 24 hours. When I run a loop to open each data file and store the acceleration and time data, the code takes longer than expected to execute. I will search for quicker ways to import that data but thanks for solving my initial problem.

temp = regexprep(data{1,2}, ':\d\d$', '$&.0', 'lineanchors');

[~,~,~,H,M,S]=datevec(temp);

t = (H*3600+M*60+S);

[Walter Roberson]

The posting I was referring to appears to be https://www.mathworks.com/matlabcentral/answers/391061-datetime-missing-milliseconds-values-showing-up-as-nat#comment_550485 -- though thinking again you might need to add 'lineanchors' as an option in that regexprep()

You should be able to batch up all of your available dates instead of executing in a loop.

If you were to use datetime objects, then

t = floor(seconds(timeofday(temp_as_datetime)))

[phillo]

Thanks Walter