Converting a time field in a table to a usable format

I have data in a table which is in the format '00:00:00:000', representing 'Hour:Minute:Second:Milisecond'.

I want to be able to have this data in a usable format so I can perform operations such as making a sub-table only consisting of entries that took place during a specified hour of the day.

I tried using the 'datevec' function with no success and would love to know if there is a function that can make the data useful.

Answer 1

[Peter Perkins]

Assuming you're starting out with something like this

>> x = [1;2;3];
>> t = {'11:59:59:795'; '11:59:59:936'; '12:00:00:714'};
>> T = table(x,s)
T =
    x          t       
    _    ______________
    1    '11:59:59:795'
    2    '11:59:59:936'
    3    '12:00:00:714'

what you probably want is to replace s with either a datetime or a duration vector, depending on whether or not your timestamps have a date associated with them. I'll assume not.

You cannot currently convert from text to duration, but it's easy to get there. First create a datetime and then remove the date portion:

>> T.t = datetime(T.t,'Format','HH:mm:ss:SSS')
T =
    x         t      
    _    ____________
    1    11:59:59:795
    2    11:59:59:936
    3    12:00:00:714
>> T.t = timeofday(T.t); T.t.Format = 'hh:mm:ss.SSS'
T =
    x         t      
    _    ____________
    1    11:59:59.795
    2    11:59:59.936
    3    12:00:00.714

Then you can do selection operations with the duration, such as

>> T(T.t < hours(12),:)
ans =
    x         t      
    _    ____________
    1    11:59:59.795
    2    11:59:59.936

You have R2016a, if you had R2016b you could try the new timetable type. Hope this helps.

[David Cynamon]

This seems like just what I'm looking for, thank you.

However, when I put

data.Time = datetime(data.Time,'Format','HH:mm:SSS');

into Matlab it throws the error that it's "unable to parse date/time text using the format 'HH:mm:ss:SSS'.

[Peter Perkins]

You've typed something wrong somewhere. 'HH:mm:SSS' is not 'HH:mm:ss:SSS'.

[David Cynamon]

Thank you very much, however If I run just

data.Time = datetime(data.Time,'Format','HH:mm:ss:SSS';

it compiles fine, but if I include any of the other you mentioned then that line gets thrown as an error. "Input data must be a numeric or a date/time string or a cell array or char matrix containing date/time character vectors."

Answer 2

[Alexandra Harkai]

You can ultimately use the hour function to get the hour from the date data.

(Plus timetable allows somewhat similar manipulations, for example getting rows for a given period of time, but not necessarily for 'between 4pm and 5pm on any day', although I may be wrong there.)

By the way, is there a specific problem/error you see using datevec?

[David Cynamon]

When I use datevec as so:

p = data.Time(1); %taking a time from the data. Its a cell with '00:00:00:027' as its contents
newTime = datevec(p,'HH:MM:SS:FFF');

I get informed "Failed to convert from text to date number". Have I structured the datevec wrong?

[Alexandra Harkai]

newTime = datevec(p,'HH:MM:SS:FFF');

seems to be working (on Windows10, R2016a) for either of these cases:

p = {'00:00:00:027'} % this is a cell
p = '00:00:00:027'   % this is not a cell

What are the size and class of your p?

Answer 3

[Motasem Mustafa]

I used to have the same issue and I have posted my question yesterday :

'' Dears,

I am using the code below to do parsing for date-time cells in an MS Excel sheet with date-time form of ( 01/05/2019 00:00) as in the screenshot below.

clc,clear,close all;
[num1,data] = xlsread('Book_new.xlsx','sheet1','A1:A30');
a=datevec(data,'dd/mm/yyyy HH:MM:SS');
date=datestr(datenum(a),'dd/mm/yyyy');
time=datestr(datenum(a),'HH:MM:SS');
Year=datestr(datenum(a),'yyyy');
mm=datestr(datenum(a),'mm');
dd=datestr(datenum(a),'dd');
yy=datestr(datenum(a),'yyyy');
[status,message] =xlswrite('motasem.xlsx',str2num(yy),'sheet1','A1:A30');
[status,message] =xlswrite('motasem.xlsx',str2num(mm),'sheet1','B1:B30');
[status,message] =xlswrite('motasem.xlsx',str2num(dd),'sheet1','C1:C30');
[status,message] =xlswrite('motasem.xlsx',string(time),'sheet1','D1:D30');

When I run the code for example for the 1st 30 readings (half hourly readings) it gives me the following error :

"Error using dtstr2dtvecmx

Failed to convert from text to date number.

Error in datevec (line 123)

y = dtstr2dtvecmx(t,icu_dtformat);

Error in motasem (line 4)

a=datevec(data,'dd/mm/yyyy HH:MM:SS');"

But when I change the range of data to avoid the first reading which contains the time 00:00:00 it works and gives the below output :

Any suggestions please ?

"

The new code that works is using readtable function as follows :

clc,clear,close all;
data = readtable('Book_new.xlsx','Range','A1:A60','ReadVariableNames',false);
A = table2array(data);
yy=datestr(datenum(A),'yyyy');
mm=datestr(datenum(A),'mm');
dd=datestr(datenum(A),'dd');
time=datestr(datenum(A),'HH:MM:SS');
[status,message] =xlswrite('motasem.xlsx',str2num(yy),'sheet1','A1:A30');
[status,message] =xlswrite('motasem.xlsx',str2num(mm),'sheet1','B1:B30');
[status,message] =xlswrite('motasem.xlsx',str2num(dd),'sheet1','C1:C30');
[status,message] =xlswrite('motasem.xlsx',string(time),'sheet1','D1:D30');

Hope this will help you

All the best