Converting a time field in a table to a usable format

Question

David Cynamon 2016-10-25

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/309057-converting-a-time-field-in-a-table-to-a-usable-format

回答： Motasem Mustafa 2020-10-23

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.

0 个评论
显示 -2更早的评论隐藏 -2更早的评论

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Peter Perkins 2016-10-25

2
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/309057-converting-a-time-field-in-a-table-to-a-usable-format#answer_240604

在 MATLAB Online 中打开

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.

3 个评论
显示 1更早的评论隐藏 1更早的评论

Peter Perkins 2016-10-25

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

David Cynamon 2016-10-25

编辑：David Cynamon 2016-10-25

在 MATLAB Online 中打开

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 2016-10-25

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/309057-converting-a-time-field-in-a-table-to-a-usable-format#answer_240569

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?

2 个评论
显示无隐藏无

David Cynamon 2016-10-25

在 MATLAB Online 中打开

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 2016-10-25

在 MATLAB Online 中打开

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 2020-10-23

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/309057-converting-a-time-field-in-a-table-to-a-usable-format#answer_522703

在 MATLAB Online 中打开

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