Calculate time intervals in an hour

I have a table with 2 columns: start time and end time. For each row the interval interval (end time -start time) represents the duration. I need to find the toal duration per each hour. Note that the time intervals not necessarily are restricted to a specific hour. A part of the table looks like:

Start Time End time

13-Sep-2019 04:12:37 13-Sep-2019 04:16:34

13-Sep-2019 04:18:36 13-Sep-2019 04:33:36

13-Sep-2019 04:33:36 13-Sep-2019 04:48:36

13-Sep-2019 04:48:36 13-Sep-2019 05:03:36

13-Sep-2019 05:03:36 13-Sep-2019 05:18:36

13-Sep-2019 05:18:37 13-Sep-2019 05:33:37

13-Sep-2019 05:33:37 13-Sep-2019 05:48:37

13-Sep-2019 05:48:37 13-Sep-2019 06:03:37

13-Sep-2019 06:03:37 13-Sep-2019 06:18:38

13-Sep-2019 06:18:38 13-Sep-2019 06:33:38

The ideal output would be a table with two columns: hour and total time intervals.

Ex:

Hour Total Duration

13-Sep-2109 04:00:00 X

13-Sep-2109 05:00:00 Y

13-Sep-2109 06:00:00 Z

[the cyclist]

What type of objects are stored in the table? Are they datetime objects or something else?

Can you upload your actual data, or the subset that corresponds to the part above?

[the cyclist]

Could the input span multiple dates?

[Matheus Pacifici]

The objects are in date time.

the complete table is:

StartTime EndTime

12-Sep-2019 21:30:39 12-Sep-2019 21:45:39

12-Sep-2019 21:45:39 12-Sep-2019 22:00:39

12-Sep-2019 22:00:39 12-Sep-2019 22:15:39

12-Sep-2019 22:15:40 12-Sep-2019 22:30:40

12-Sep-2019 22:30:40 12-Sep-2019 22:45:40

12-Sep-2019 22:45:40 12-Sep-2019 23:00:40

12-Sep-2019 23:00:41 12-Sep-2019 23:15:41

12-Sep-2019 23:15:41 12-Sep-2019 23:30:41

12-Sep-2019 23:30:41 12-Sep-2019 23:45:41

12-Sep-2019 23:45:41 13-Sep-2019 00:00:41

13-Sep-2019 00:00:42 13-Sep-2019 00:15:42

13-Sep-2019 00:15:42 13-Sep-2019 00:18:34

13-Sep-2019 00:20:35 13-Sep-2019 00:35:35

13-Sep-2019 00:35:35 13-Sep-2019 00:50:35

13-Sep-2019 00:50:35 13-Sep-2019 01:05:35

13-Sep-2019 01:05:35 13-Sep-2019 01:12:34

13-Sep-2019 01:14:35 13-Sep-2019 01:29:35

13-Sep-2019 01:29:35 13-Sep-2019 01:36:34

13-Sep-2019 01:38:35 13-Sep-2019 01:53:35

13-Sep-2019 01:53:35 13-Sep-2019 02:08:35

13-Sep-2019 02:08:35 13-Sep-2019 02:23:35

13-Sep-2019 02:23:36 13-Sep-2019 02:38:36

13-Sep-2019 02:38:36 13-Sep-2019 02:40:34

13-Sep-2019 02:42:36 13-Sep-2019 02:57:36

13-Sep-2019 02:57:36 13-Sep-2019 03:12:36

13-Sep-2019 03:12:36 13-Sep-2019 03:27:36

13-Sep-2019 03:27:36 13-Sep-2019 03:42:36

13-Sep-2019 03:42:37 13-Sep-2019 03:57:37

13-Sep-2019 03:57:37 13-Sep-2019 04:12:37

13-Sep-2019 04:12:37 13-Sep-2019 04:16:34

13-Sep-2019 04:18:36 13-Sep-2019 04:33:36

13-Sep-2019 04:33:36 13-Sep-2019 04:48:36

13-Sep-2019 04:48:36 13-Sep-2019 05:03:36

13-Sep-2019 05:03:36 13-Sep-2019 05:18:36

13-Sep-2019 05:18:37 13-Sep-2019 05:33:37

13-Sep-2019 05:33:37 13-Sep-2019 05:48:37

13-Sep-2019 05:48:37 13-Sep-2019 06:03:37

13-Sep-2019 06:03:37 13-Sep-2019 06:18:38

13-Sep-2019 06:18:38 13-Sep-2019 06:33:38

13-Sep-2019 06:33:38 13-Sep-2019 06:48:38

13-Sep-2019 06:48:38 13-Sep-2019 07:03:38

13-Sep-2019 07:03:39 13-Sep-2019 07:18:39

13-Sep-2019 07:18:39 13-Sep-2019 07:33:39

13-Sep-2019 07:33:39 13-Sep-2019 07:48:39

13-Sep-2019 07:48:39 13-Sep-2019 08:03:39

13-Sep-2019 08:03:40 13-Sep-2019 08:18:40

13-Sep-2019 08:18:40 13-Sep-2019 08:33:40

13-Sep-2019 08:33:40 13-Sep-2019 08:48:40

13-Sep-2019 08:48:40 13-Sep-2019 09:03:40

13-Sep-2019 09:03:41 13-Sep-2019 09:18:41

13-Sep-2019 09:18:41 13-Sep-2019 09:33:41

13-Sep-2019 09:33:41 13-Sep-2019 09:48:41

13-Sep-2019 09:48:41 13-Sep-2019 10:03:42

13-Sep-2019 10:03:42 13-Sep-2019 10:18:42

13-Sep-2019 10:18:42 13-Sep-2019 10:33:42

13-Sep-2019 10:33:42 13-Sep-2019 10:48:42

13-Sep-2019 10:48:43 13-Sep-2019 11:03:43

13-Sep-2019 11:03:43 13-Sep-2019 11:18:43

13-Sep-2019 11:18:43 13-Sep-2019 11:33:43

13-Sep-2019 11:33:43 13-Sep-2019 11:48:43

13-Sep-2019 11:48:44 13-Sep-2019 12:03:44

13-Sep-2019 12:03:44 13-Sep-2019 12:18:44

13-Sep-2019 12:18:44 13-Sep-2019 12:33:44

13-Sep-2019 12:33:44 13-Sep-2019 12:48:44

13-Sep-2019 12:48:45 13-Sep-2019 13:03:45

13-Sep-2019 13:03:45 13-Sep-2019 13:07:00

13-Sep-2019 13:34:30 13-Sep-2019 13:49:30

13-Sep-2019 13:49:30 13-Sep-2019 14:04:30

13-Sep-2019 14:04:30 13-Sep-2019 14:19:30

13-Sep-2019 14:19:31 13-Sep-2019 14:34:31

13-Sep-2019 14:34:31 13-Sep-2019 14:36:42

13-Sep-2019 14:48:31 13-Sep-2019 15:03:31

13-Sep-2019 15:03:32 13-Sep-2019 15:09:27

I need a function to give me duration in each hour

[Matheus Pacifici]

Yes, it can span multiple dates.

for the whole data, the ideal output would be :

StartTime duration

09-12-2019 21:00

09-12-2019 22:00

09-12-2019 23:00

09-13-2019 00:00

09-13-2019 01:00

09-13-2019 02:00

09-13-2019 03:00

09-13-2019 04:00

09-13-2019 05:00

09-13-2019 06:00

09-13-2019 07:00

09-13-2019 08:00

09-13-2019 09:00

09-13-2019 10:00

09-13-2019 11:00

09-13-2019 12:00

09-13-2019 13:00

09-13-2019 14:00

09-13-2019 15:00

[the cyclist]

I meant for you to upload the data in a MAT file (using the paper clip icon). It's not very easy to paste those data to create variables in the workspace for testing.

Answer 1

[the cyclist]

% Create an input data table
startDateStr = {'2014-05-26 04:44:44';
                '2014-05-26 04:45:44';
                '2014-05-26 05:44:44';
                '2014-05-26 05:45:44';
                };
endDateStr   = {'2014-05-26 04:44:55';
                '2014-05-26 04:45:56';
                '2014-05-26 05:44:57';
                '2014-05-26 05:45:58';
                };

startTime = datetime(startDateStr,'InputFormat','yyyy-MM-dd hh:mm:ss');
endTime = datetime(endDateStr,'InputFormat','yyyy-MM-dd hh:mm:ss');

tbl = table(startTime,endTime);

% Find the unique hours, and the index to those hours
hourOfDay = dateshift(tbl.startTime,'start','hour');
[Hour,~,whichHour] = unique(hourOfDay);

% Find the durations (in seconds, but could be something else)
durationsInSeconds = seconds(tbl.endTime - tbl.startTime);

% Find the total duration for each of the unique hours
TotalDuration = accumarray(whichHour,durationsInSeconds);

% Put the result in a table
output = table(Hour,TotalDuration)

[Adam Danz]

Nice! I was about to post something similar. Just note that this approach assumes that there are no overlapping times. For example if there are two start/end times that overlap (example below) those times will be counted twice which could result in >60 minutes in an hour (which may or may not be desired).

     StartTime                            EndTime
    12-Sep-2019 21:30:39    12-Sep-2019 21:45:39
    12-Sep-2019 21:35:39    12-Sep-2019 22:55:39

[addendum]

It also assumes that the entire datetime range is within the same day since it is only looking at hourly bins.

[Matheus Pacifici]

Thanks the cyclist.

It works pretty well.

There's some fixing needed to be done, however, some hours have more than 3600 seconds in the total Duration column. i'll see what i can do and get back to you.

Many Thanks

[Adam Danz]

That may indicate one of the caveat I mentioned above: either the dates span across more than 24 hours or there are start-end times that overlap.

[Matheus Pacifici]

Attached excel table

[Matheus Pacifici]

Hello Adam, the housr do span across more than 24 hours as it's a continous data collection. But I manually checked and there are no overlapping end time/start time. They do however overlapp the hour mark, which might be the reason it's been coun ted twice

[Adam Danz]

If a starttime and endtime have two different hours (03:59 to 04:59), the entire duration will be assigned to the first hour (3:00) since "whichHour" is based on the starting hour. So your hunch is correct that this would cause those simptoms (along with the span >24hr).

Answer 2

[Adam Danz]

I've made lots of comments to explain what's going on. But here's a summary.

This approach interpolates each start:end time to second-resolution, combines all of those vectors into one, and applies unique() to get rid of any duplicates. That results in a single long vector of timestamps at 1-second-resolution that cover every start:end without overlap. Then we use histcounts() to count the number of seconds per hourly bin. The hourly bins span across days (or months or years) and covers the entire range of your data. The number of seconds per hour are converted to number of minutes per hour.

The result is a table displayed below.

The "Minutes" column can also be displayed in "durations". You'll see a commented line in the code that makes that conversion, if desired.

% Read in data 
T = readtable('RepoInSeatHistory.xlsx'); 
T.startTime = datetime(T.startTime,'InputFormat','yyyy-MM-dd HH:mm:ss +0000'); 
T.endTime = datetime(T.endTime,'InputFormat','yyyy-MM-dd HH:mm:ss +0000'); 

% Determine the min and max datetime to the hour (rounding up/down)
minHr = min(dateshift(T.startTime,'start','hour'));
maxHr = max(dateshift(T.startTime,'end','hour'));

% Create bins that span every day/hour  (this allows for spans >24 hrs)
hrBins = minHr : hours(1) : maxHr; 

% Interpolate all start:end to second-resolution
dtInterpCell = arrayfun(@(i)T.startTime(i) : seconds(1) : T.endTime(i), 1:size(T,1),'UniformOutput',false);
dtInterp = dateshift([dtInterpCell{:}],'end','second');  %a long vector of all start:end in second-resolution

% Remove any duplicates (this avoids problem of overlapping start-end values)
dtInterp = unique(dtInterp); %note, the order of the datetimes doesn't matter

% Now simply use histcounts to determine the number of seconds within hourly bins 
% that potentially span multiple days
secondsPerHour = histcounts(dtInterp,hrBins); 

% Convert to minutes/hr
% This may result in decimals that represent partial minutes since we're measuring
% at second-resolution.  If that's not desired, you can use floor() or ceil() to
% round down or up. 
MinutsPerHour = secondsPerHour/60;
% MinutsPerHour = minutes(secondsPerHour/60); % Alternative: minute durations

% Put results in a table
hrBins(end) = []; %remove last edge
Tout = table(hrBins(:), MinutsPerHour(:),'VariableNames',{'HourBins', 'Minutes'});

Result

Tout =
  19×2 table
          HourBins          Minutes
    ____________________    _______
    12-Sep-2019 21:00:00    29.333 
    12-Sep-2019 22:00:00        60 
    12-Sep-2019 23:00:00        60 
    13-Sep-2019 00:00:00        58 
    13-Sep-2019 01:00:00        56 
    13-Sep-2019 02:00:00    57.983 
    13-Sep-2019 03:00:00        60 
    13-Sep-2019 04:00:00    57.983 
    13-Sep-2019 05:00:00        60 
    13-Sep-2019 06:00:00        60 
    13-Sep-2019 07:00:00        60 
    13-Sep-2019 08:00:00        60 
    13-Sep-2019 09:00:00        60 
    13-Sep-2019 10:00:00        60 
    13-Sep-2019 11:00:00        60 
    13-Sep-2019 12:00:00        60 
    13-Sep-2019 13:00:00    32.517 
    13-Sep-2019 14:00:00      48.2 
    13-Sep-2019 15:00:00    9.4833 

[Matheus Pacifici]

Thanks Adams. It worked perfectly.

I just had to adjust dtInter and hrBins to datenums as histcounts wouldn't take datetime input. Other than that, it's amazing.

Thanks for the help and for being so detailed in your explanations.

Regards!!

[Adam Danz]

I wonder what release of Matlab you are using. Histcounts does take datetime inputs (at least in r2019a and b). Search for "datetime" on the histcounts documentation page:

https://www.mathworks.com/help/matlab/ref/histcounts.html

Glad I could help!