MATLAB: Resample multiple overlapping datetime to 1-minute (and 15-minutes) resolution

retimetabletimetable

Coming back again on resampling datetime….

Situation is I have many water pumps which are filled up in different time-frames. I have this table with multiple overlapping time and the total volume of individual pumps. Each row is basically a different water pump.

Column 1 (Start time), Column 2 (End time), Column 3 (Volume)

I want to sample the data to minute resolution (also 15 mins resolution) with the specified volume.

Example:

Pump1: 00:04:34 06:33:34 56

Pump2: 00:10:09 09:50:01 93

What I want it

00:04:00 56

00:05:00 56

….

00:10:00 56+93

00:11:00 56+93

….

06:34:00 93

As you can see, it sums up when there is overlapping time. Or else it is the only volume.

I have attached the actual table as well.

Thanks!

Best Answer

Let's start off with two simple timetable arrays, one per pump.

pump1 = timetable(datetime(["01-Jan-2018 00:14:09"; "01-Jan-2018 11:22:13"]), ...
    [2.87; 2.87], 'VariableNames', {'Volume_pump1'})
pump2 = timetable(datetime(["01-Jan-2018 00:16:40"; "01-Jan-2018 14:15:10"]), ...
    [4.709; 4.709], 'VariableNames', {'Volume_pump2'})

synchronize the two timetable arrays to a new one whose Times are regularly spaced (every 15 minutes), spanning the union of the Times for the two smaller timetable arrays. I'm using linear interpolation and no extrapolation, so the pump is only 'on' during the range spanned by the Times in its smaller timetable array from above.

P = synchronize(pump1, pump2, 'regular', 'linear', ...
    'TimeStep', minutes(15), ...
    'EndValues', 0)

Note that because I told synchronize to give regular spacing and the Times from pump1 and pump2 aren't multiples of 15 minutes, they're not included. You could construct a NEWTIMES vector (combine the Times together and use bounds to determine the earliest and latest times) and pass that into synchronize if you need those exact times to be included.

To get the total volume:

P2 = timetable(P.Time, sum(P.Variables, 2), 'VariableNames', {'totalVolume'})

Related Solutions

MATLAB: Calculate time intervals in an hour

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

MATLAB: Timezone to UTC: How to add duration vector to datetime vector

Frank, what's up is Daylight Saving Time, which occurred on March 8th this year. I'm guessing you knew that, but got mixed up in the time arithmetic. Here's what's going on. You have this:

>> time = ['2015/03/08 00:00';'2015/03/08 01:00';'2015/03/08 03:00';'2015/03/08 04:00'];
>> Time = datenum(time,'yyyy/mm/dd HH:MM')
Time =
                    736031
          736031.041666667
                736031.125
          736031.166666667

datenums don't know anything about time zones or DST. Those times mean whatever you want them to mean. Since you then do this ...

>> date = datetime(Time,'ConvertFrom','datenum','Timezone','America/Montreal')
date = 
   08-Mar-2015 00:00:00
   08-Mar-2015 01:00:00
   08-Mar-2015 03:00:00
   08-Mar-2015 04:00:00

... I assume you mean that those datenums are to be interpreted as times in the America/Montreal time zone. But what you've left out is this:

>> date.Format = [date.Format ' z']
date = 
   08-Mar-2015 00:00:00 UTC-5
   08-Mar-2015 01:00:00 UTC-5
   08-Mar-2015 03:00:00 UTC-4
   08-Mar-2015 04:00:00 UTC-4

The second and third times are only an hour apart. Then this ...

>> [UTC_Offset,dst] = tzoffset(date);
>> Date = date - UTC_Offset
Date = 
   08-Mar-2015 06:00:00 UTC-4
   08-Mar-2015 07:00:00 UTC-4
   08-Mar-2015 07:00:00 UTC-4
   08-Mar-2015 08:00:00 UTC-4

... adds 5 hours to the first and second times, but adds only 4 hours to the third and fourth times. But you're doing the addition in the America/Montreal time zone. Adding 5 hours to the first two puts them after the DST shift @2am, which makes them look like you added 6 hours. In other words, your array is not UTC, it's shifted America/Montreal. The easiest thing to do to get UTC times is this:

>> date.TimeZone = 'UTC'
date = 
   08-Mar-2015 05:00:00 UTC
   08-Mar-2015 06:00:00 UTC
   08-Mar-2015 07:00:00 UTC
   08-Mar-2015 08:00:00 UTC

Hope this helps.

Best Answer

Related Solutions

MATLAB: Calculate time intervals in an hour

MATLAB: Timezone to UTC: How to add duration vector to datetime vector

Related Question