Converting a time field in a table to a usable format

15 次查看（过去 30 天）
David Cynamon
David Cynamon 2016-10-25
回答： 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.

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

采纳的回答

Peter Perkins
Peter Perkins 2016-10-25
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更早的评论
Peter Perkins
Peter Perkins 2016-10-25
You've typed something wrong somewhere. 'HH:mm:SSS' is not 'HH:mm:ss:SSS'.
David Cynamon
David Cynamon 2016-10-25
编辑：David Cynamon 2016-10-25
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."

请先登录，再进行评论。

更多回答（2 个）

Alexandra Harkai
Alexandra Harkai 2016-10-25
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
David Cynamon 2016-10-25
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
Alexandra Harkai 2016-10-25
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?

请先登录，再进行评论。

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

类别

在 Help Center 和 File Exchange 中查找有关 Dates and Time 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by