15

I have a table with the following schema, and I need to define a query that can group data based on intervals of time (Ex. records per minute) and then provide the sum of the changes to the SnapShotValue since the previous group. At present, the SnapShotValue always increments so I only need the sum of differences. Can anyone help with a SQL Server T-SQL query that might do this? I am open to changing the schema, but this is what I currently have.

Schema

CaptureTime   (datetime)
SnapShotValue (int)

Sample data

1 Jan 2012 00:00:00,   100
1 Jan 2012 00:00:30,   125
1 Jan 2012 00:01:00,   200
1 Jan 2012 00:01:30,   300
1 Jan 2012 00:02:15,   400
1 Jan 2012 00:02:30,   425
1 Jan 2012 00:02:59,   500

Desired Query Result

1 Jan 2012 00:01:00,   225 -- Sum of all captured data changes up to the 1 minute mark
1 Jan 2012 00:02:00,   500 -- Sum of all captured data changes up to the 2 minute mark
1 Jan 2012 00:03:00,   125 -- Sum of all captured data changes up to the 3 minute mark
Improve this question

3 Answers 3

Reset to default
20 
select dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0),
       sum(SnapShotValue)
from YourTable
group by dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0)

SE-Data

datediff(minute, 0, CaptureTime) gives you the number of minutes since 1900-01-01T00:00:00.

dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0) adds the number of minutes since 1900-01-01T00:00:00 to 1900-01-01T00:00:00 ending up with a datetime with only minutes.

The 1+ is there because you wanted the next minute.

To do the same with a 5 minute interval you need to do some calculations. Divide the minutes with 5 and multiply with 5 gives you the minutes rounded down to a 5 minute precision. This works because the result of an integer division in SQL Server is an integer.

dateadd(minute, 5 + (datediff(minute, 0, CaptureTime) / 5) * 5, 0)
Improve this answer
3
  • This provided a nice framework for the answer, but I am still having a little trouble understanding how this works. When I tried to change the offset from a 1 minute interval to a 5 minute interval I still get a 1 minute interval with the whole result starting at a different beginning point. This is very likely because I am misunderstanding how this works. Could you provide one more sample showing an interval other then 1 minute? Thanks...
    – JoeGeeky
    May 10, 2012 at 12:36
  • @JoeGeeky did you change all 3 instances of the "5" in his example? I variablized mine and it works beautifully! Lol, I just noticed your comment date...whoops!
    – ganders
    Aug 11, 2021 at 5:26
  • Here's mine dateadd(minute, @minIncrement + (datediff(minute, 0, [Start]) / @minIncrement) * @minIncrement, 0)
    – ganders
    Aug 11, 2021 at 5:26
2

The following is translation of accepted answer into PostgreSQL (I chose January 1st 2000, as the base date. Change it to an earlier date if your data is older than that):

SELECT 
  TIMESTAMP '2000-01-01 00:00:00' + 1 + DATE_PART('minute', CaptureTime - TIMESTAMP '2000-01-01 00:00:00') * INTERVAL '1 minute' 
  , SUM(SnapShotValue)
FROM table_name
GROUP BY TIMESTAMP '2000-01-01 00:00:00' + 1 + DATE_PART('minute', CaptureTime - TIMESTAMP '2000-01-01 00:00:00') * INTERVAL '1 minute' 
ORDER BY TIMESTAMP '2000-01-01 00:00:00' + 1 + DATE_PART('minute', CaptureTime - TIMESTAMP '2000-01-01 00:00:00') * INTERVAL '1 minute'
Improve this answer
0

I'm a little confused by the 3rd example there, is this supposed to be 1325? Based off the requirements of capturing the sum of the snapshotvalues, the below query should get what you're after. 

select dateadd(minute,1,convert(varchar(20),capturetime)), sum(snapshotvalue)
from snapshotdata sd
group by dateadd(minute,1,convert(varchar(20),capturetime))
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.