MATLAB Answers

Rounding date time to nearest half hour wihtin a table

14 views (last 30 days)
Thomas Webber
Thomas Webber on 4 Jun 2020
Edited: per isakson on 8 Jun 2020
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

Sign in to comment.

Accepted Answer

per isakson
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

Thomas Webber
Thomas Webber on 5 Jun 2020
Thanks for the quick replies on this! It's much appreciated! This was just what i was looking for!

Sign in to comment.

More Answers (1)

Adam Danz
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

Sign in to comment.


Translated by