I compare a lot of datasets against each other. More often than not, their time domains do not match. Inevitably, I need to resample one or more sets of data. There are a number of ways to deal with this, but I’d like to share one of my favorite techniques.

Introducing the Data

I’m currently dealing with a weather dataset that includes several measured quantities. This particular set of data was recorded at 1-minute intervals, as shown below. I also happen to know that there are no missing intervals for my given period.

Data recorded at this resolution can be useful, but in this particular case, I need the data to be averaged into 5-minute “bins”. This is essentially a way to resample, or, more specifically, downsample the data. In other words, I’m looking for the following, where each timestamp includes averaged values for the previous 5 timestamps:

Unix Timestamps

In my MySQL database, the timestamps are all stored as datetime objects. This means they adhere to the ISO 8601 standard. However, MySQL includes the capability of converting these timestamps to the Unix standard. The Unix standard is based on the number of seconds since the Unix Epoch (January 1st, 1970 UTC). For example, 6/19/2014 00:00 can be represented as 1403136000, and 6/19/2014 00:01 as 1403136001.

Integer Division

Integer division simply means that decimals are truncated after division has taken place. If you were to type 5/3 into your calculator, you would expect to see 1.66667 as the answer. In the integer division world, 5 div 3 instead equals 1 (the 0.666667 is truncated). Similarly, integer division calculates 6.5 div 0.5 to be 13, but it also calculates 6.8 div 0.5 to be 13.

Resample to Data

I’ll build up the code here piece by piece so that you can see how it words. The first step is to convert the timestamps to the Unix format. Next, integer division is used to divide by a specified number of seconds (determined by the desired number of minutes times 60). This code compares the two types of divisions.

SELECT UNIX_TIMESTAMP(ddatetime) div (5*60) as IntegerDivision,
UNIX_TIMESTAMP(ddatetime) / (5*60) as Division 
FROM `weather`

After the integer division has taken place, I multiply the modified Unix time by the same interval I just divided it by. Then, I add the interval to the result so that minutes 0-4 are labeled by the minute-5 timestamp. Lastly, I convert back to ISO 8601 time.

SELECT ddatetime, 
UNIX_TIMESTAMP(ddatetime) div (5*60) as IntegerDivision, 
(UNIX_TIMESTAMP(ddatetime) div (5*60))*(5*60)+(5*60) as ModifiedTimes, 
FROM_UNIXTIME((UNIX_TIMESTAMP(ddatetime) div (5*60))*(5*60)+(5*60)) as FinalStamps 
FROM `weather`

The only thing left to do is average (resample!) and group the results on the ‘FinalStamps’ column!

SELECT FROM_UNIXTIME((UNIX_TIMESTAMP(ddatetime) div (5*60))*(5*60)+(5*60)) as FinalStamps, round(avg(GHI),3) as GHI, round(avg(DNI),3) as DNI, round(avg(DBT),3) as DBT
FROM `weather`
GROUP BY 1

The true beauty of this method is that the interval can be set to any arbitrary number of minutes or seconds. Just modify the (5*60) term to your liking!

Good luck, and happy resampling!