Converting a time field in a table to a usable format
21 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
David Cynamon
el 25 de Oct. de 2016
Respondida: Motasem Mustafa
el 23 de Oct. de 2020
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 comentarios
Respuesta aceptada
Peter Perkins
el 25 de Oct. de 2016
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 comentarios
Peter Perkins
el 25 de Oct. de 2016
You've typed something wrong somewhere. 'HH:mm:SSS' is not 'HH:mm:ss:SSS'.
David Cynamon
el 25 de Oct. de 2016
Editada: David Cynamon
el 25 de Oct. de 2016
Más respuestas (2)
Alexandra Harkai
el 25 de Oct. de 2016
(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 comentarios
Alexandra Harkai
el 25 de Oct. de 2016
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
el 23 de Oct. de 2020
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
0 comentarios
Ver también
Categorías
Más información sobre Logical en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!