Rounding date time to nearest half hour wihtin a table

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!

Answer 1

[per isakson]

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;

[Adam Danz]

Nice and simple!

[Thomas Webber]

Thanks for the quick replies on this! It's much appreciated! This was just what i was looking for!

[per isakson]

I added an afterthought to my answer.

Answer 2

[Adam Danz]

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;