How to split a table based on date and hour

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?

Thanks in advance!

Answer 1

[Guillaume]

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')

[Piro93]

Thanks Guillaume, very good!!

Answer 2

[Steven Lord]

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.