Consolidate 10 second interval data to 30 minute interval data

Question

Consolidate 10 second interval data to 30 minute interval data

joe-584802

SSC Eights!

Points: 949
More actions
June 18, 2019 at 2:44 pm

#3654789
Hi Guys,
I have a table that stores oil burned in litres per 10 second interval and I need to consolidate this to 30 minute interval.
I've tried the following statement ...
```
SELECT TOP (100) PERCENT DATEADD(MINUTE, ROUND(DATEDIFF(SECOND, 0, DateTime) / 10.0, 0) * 30, 0) AS Date_Time, SUM(Burner1) AS Burn1
FROM dbo.tblOilBurner
GROUP BY DATEADD(MINUTE, ROUND(DATEDIFF(SECOND, 0, DateTime) / 10.0, 0) * 30, 0)
```
... but get an error message.
The datediff function resulted in an overflow. The number of dateparts seperating two date/time instances is too large. Try to use datediff with a less precise datepart.
Any help with this is appreciated!
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76997 More actions · Answer 1

That's what comes from using a formula that you found on the web without understanding exactly what it is doing. The zero that you are using is a shortcut for the date 1900-01-01. DATEDIFF can only calculate the difference in seconds between dates about 70 years apart. Currently, that means that dates compared to today's date (2019-06-18) can only go back to 1951-05-31.

You have a few options:

Use a date much closer than 1900-01-01.
Use DATEDIFF_BIG() which uses a bigint difference instead of an int difference.
Split the data and time components and use a DATEDIFF on TIME expressions.
Use a tally table to create your ranges.

My choice would probably be the last one, but you haven't provided sample data, which makes it difficult to provide tested code.

Drew

This reply was modified 5 years, 6 months ago by drew.allen.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ScottPletcher SSC Guru Points: 101123 More actions · Answer 2

Assuming you don't have dates before 1980, then:

SELECT TOP (100) PERCENT DATEADD(MINUTE, ROUND(DATEDIFF(SECOND, base_date, DateTime) / 10.0, 0) * 30, base_date) AS Date_Time, SUM(Burner1) AS Burn1
FROM dbo.tblOilBurner
CROSS APPLY (
    SELECT CAST('19800101' AS datetime) AS base_date
) AS alias1
GROUP BY DATEADD(MINUTE, ROUND(DATEDIFF(SECOND, base_date, DateTime) / 10.0, 0) * 30, base_date)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

drew.allen SSC Guru Points: 76997 More actions · Answer 3

There is also a problem with your formula.

You're dividing your seconds by 10.0 and then rounding.
You're then multiplying by 30 and changing the units to minutes.
- This is equivalent to multiplying by 1800.

This is another reason that you are running into problems with overflow. Your conversion isn't correct.

This is part of the reason that I recommended using a tally table to create your ranges.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ScottPletcher SSC Guru Points: 101123 More actions · Answer 4

I ignored the actual calc before, but Drew is quite right, of course, that needs corrected too:

SELECT TOP (100) PERCENT 
    DATEADD(SECOND, DATEDIFF(SECOND, base_date, DateTime) / 1800 * 1800, base_date) AS Date_Time, 
    SUM(Burner1) AS Burn1
FROM ( SELECT CAST('19800401 13:41' AS datetime) AS datetime, 10 AS burner1) AS x /*dbo.tblOilBurner*/
CROSS APPLY (
    SELECT CAST('19800101' AS datetime) AS base_date
) AS alias1
GROUP BY DATEADD(SECOND, DATEDIFF(SECOND, base_date, DateTime) / 1800 * 1800, base_date)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".