# How to split a table based on date and hour

9 views (last 30 days)
Piro93 on 15 Jul 2019
Answered: Steven Lord on 15 Jul 2019
Hi to everybody, I'm a MATLAB beginner and I have a problem with a table of data related with the production of metallic pieces.
This is an example of my table:
Date Pieces
2012-01-07 05:55:47 128
2012-01-07 06:05:07 267
2012-01-07 08:24:14 589
2012-01-07 22:05:07 341
2012-01-08 11:45:23 160
2012-01-08 12:13:53 157
I would like to summarized the pieces produced, divided per days. The real problem is that (for the problem) the day does not start at 00:00:00 but at 06:00:00 and it finishes at the 6am of the subsequent day.
Considering the example, the first row refers to 2012-01-06, second, third and fourth to 2012-01-07 and so on.
How can I split the data in this way?

Guillaume on 15 Jul 2019
Edited: Guillaume on 15 Jul 2019
The easiest way is to use retime (requires a timetable) or groupsummary grouping by day, Of course, for matlab a day starts at 00:00 and there's no option to tell it that you want a day to start at 6:00. The easiest workaround is to simply subtract 6 hours from your dates:
%T: a table
Toffset = T;
Toffset.Date = Toffset.Date - hours(6);
result = groupsummary(Toffset, 'Date', 'day', 'sum')
%TT: a timetable
TToffset = TT;
TToffset.RowTimes = TToffset.RowTimes - hours(6);
result = retime(TToffset, 'daily', 'sum')

#### 1 Comment

Piro93 on 15 Jul 2019
Thanks Guillaume, very good!!

Steven Lord on 15 Jul 2019
In addition to being able to specify 'day' as the newTimeStep input to retime you can specify a newTimes vector. Similarly, in groupsummary you can specify the groupbins input as 'day' or as a list of bin edges. Let's make a newTimes vector / vector of bin edges.
Start off with a vector of random datetime values.
d = datetime('now') + hours(48*randn(10, 1));
Let's get the earliest and latest datetime values in d.
firstTime = min(d);
lastTime = max(d);
Shift the earliest datetime to the start of its day (midnight) then add six hours. Similarly, shift the latest datetime to the end of its day and add six hours.
firstSixAM = dateshift(firstTime, 'start', 'day') + hours(6);
lastSixAM = dateshift(lastTime, 'end', 'day') + hours(6);
Actually, we need to shift firstSixAM earlier if the earliest time occurs before 6 AM on its day. In that case, dateshift determined midnight on that day but then the added six hours stepped past the earliest time. So we actually need 6 AM the day before.
if firstTime < firstSixAM
firstSixAM = firstSixAM - days(1);
end
The allSixAMs vector created below contains a vector of datetime values, spaced one day apart, each of which represents 6 AM on that date. You can use this as the newTimes for a retime call or as the bin edges for groupsummary.
allSixAMs = firstSixAM:days(1):lastSixAM;
We can check that every element of d is in the range spanned by the elements of allSixAMs.
whichBin = discretize(d, allSixAMs);
t = timetable(d, whichBin, allSixAMs(whichBin).', allSixAMs(whichBin+1).', ...
'VariableNames', {'BinNumber', 'StartOfBin', 'EndOfBin'})
The value of d in each row in t should be between the value of StartOfBin and EndOfBin in that row.