Rounding date time to nearest half hour wihtin a table
40 views (last 30 days)
Show older comments
Hi all,
I've been searching through previous Q&A's but cant seem to find something that works. I have a table with over 800 rows and would like to round the date time to the nearest half hour. I have tried dateshift but get the error: Undefined function 'dateshift' for input arguments of type 'cell'.
Is there anyway of rounding these within the table?
Thanks!
0 Comments
Accepted Answer
per isakson
on 4 Jun 2020
Edited: per isakson
on 8 Jun 2020
An alternate way
%% A slightly modified example from the documentation
MeasurementTime = datetime({'2015-12-18 08:03:05';'2015-12-18 10:33:17';'2015-12-18 12:53:13'});
Temp = [37.3;39.1;42.3];
Pressure = [30.1;30.03;29.9];
WindSpeed = [13.4;6.5;7.3];
TT = timetable(MeasurementTime,Temp,Pressure,WindSpeed);
vec = datevec( TT.MeasurementTime );
v5 = vec(:,5)+vec(:,6)/60;
vec(:,5) = round(v5/30)*30;
vec(:,6) = 0;
TT.MeasurementTime = datetime( vec );
>> TT
TT =
3×3 timetable
MeasurementTime Temp Pressure WindSpeed
____________________ ____ ________ _________
18-Dec-2015 08:00:00 37.3 30.1 13.4
18-Dec-2015 10:30:00 39.1 30.03 6.5
18-Dec-2015 13:00:00 42.3 29.9 7.3
>>
Afterthought
In the example above
TT.MeasurementTime = datetime( vec );
replaces the original datetime object of the table with a new datetime object. Properties, like Format, of this new object will have default values. Values set by the user will thus be lost. And creating a new object might be inefficient. Thus, replace
TT.MeasurementTime = datetime( vec );
by
TT.MeasurementTime.Minute = vec(:,5);
TT.MeasurementTime.Second = 0;
And why involve datevec() when the code below is both shorter and cleaner
%%
mm = TT.MeasurementTime.Minute + TT.MeasurementTime.Second/60;
mm = round(mm/30)*30;
TT.MeasurementTime.Minute = mm;
TT.MeasurementTime.Second = 0;
3 Comments
More Answers (1)
Adam Danz
on 4 Jun 2020
Edited: Adam Danz
on 4 Jun 2020
If your datetime values are stored in a cell array, first convert them to a datetime array using,
datetimeValues = [datetimeCell{:}]';
% Remove the transpose (') at the end if dateTimeCell is a row vector.
Then round all datetime values down to the earliest hour.
% Create demo data
datetimeValues = datetime(2020,02,05) + minutes(0:3:60)';
% Round to the nearest hour
dateTimeShifted = dateshift(datetimeValues,'start','hour');
Then add 30 minutes for datetime values that had minutes between 15-45 and add 1 hour to datetime values that had minutes greater than or equal to 45.
minuteValues = minute(datetimeValues);
dateTimeShifted(minuteValues >= 15 & minuteValues < 45) = dateTimeShifted(minuteValues >= 15 & minuteValues < 45) + minutes(30);
dateTimeShifted(minuteValues >= 45) = dateTimeShifted(minuteValues >= 45) + hours(1);
Here are two ways to test the results.
Create a table of the original times, shifted times, and the duration between those values. The 'difference' column should not contain any values larger than +/- 15 minutes.
table(datetimeValues, dateTimeShifted, minutes(datetimeValues-dateTimeShifted), ...
'VariableNames', {'Original','Shifted','difference'})
% Result (only the first few rows)
% Original Shifted difference
% ____________________ ____________________ __________
% 05-Feb-2020 00:00:00 05-Feb-2020 00:00:00 0
% 05-Feb-2020 00:03:00 05-Feb-2020 00:00:00 3
% 05-Feb-2020 00:06:00 05-Feb-2020 00:00:00 6
% 05-Feb-2020 00:09:00 05-Feb-2020 00:00:00 9
% 05-Feb-2020 00:12:00 05-Feb-2020 00:00:00 12
Duration between the original and shifted times as a function of the original times. Values along the y axis should not exceed +/- 15 minutes.
plot(dateTimeShifted, minutes(datetimeValues-dateTimeShifted), 'o')
grid on; xlabel('shifted datetime'); ylabel('\Delta minutes')
Addendum: I just noticed that these values are within a table. Here are the lines of code above adapted for a table with column "datetimeValues".
T.datetimeValues = [T.datetimeValues{:}]';
dateTimeShifted = dateshift(T.Var1,'start','hour');
minuteValues = minute(datetimeValues);
dateTimeShifted(minuteValues >= 15 & minuteValues < 45) = dateTimeShifted(minuteValues >= 15 & minuteValues < 45) + minutes(30);
dateTimeShifted(minuteValues >= 45) = dateTimeShifted(minuteValues >= 45) + hours(1);
T.Var1 = dateTimeShifted;
0 Comments
See Also
Categories
Find more on Dates and Time in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!