Difference between shutdown options

Should I assume I have passed probation?

Identifying "long and narrow" polygons in with PostGIS

How to test the sharpness of a knife?

Make a Bowl of Alphabet Soup

Showing mass murder in a kid's book

Origin of pigs as a species

Given this phrasing in the lease, when should I pay my rent?

If the only attacker is removed from combat, is a creature still counted as having attacked this turn?

How to make money from a browser who sees 5 seconds into the future of any web page?

Anime with legendary swords made from talismans and a man who could change them with a shattered body

Language involving irrational number is not a CFL

Why does the Persian emissary display a string of crowned skulls?

How can I, as DM, avoid the Conga Line of Death occurring when implementing some form of flanking rule?

Would this string work as string?

What happens if I try to grapple mirror image?

I'm just a whisper. Who am I?

Sigmoid with a slope but no asymptotes?

Why can't the Brexit deadlock in the UK parliament be solved with a plurality vote?

Overlapping circles covering polygon

ContourPlot — How do I color by contour curvature?

Has the laser at Magurele, Romania reached a tenth of the Sun's power?

How would a solely written language work mechanically

Confusion over Hunter with Crossbow Expert and Giant Killer

Grouping records based on intervals of time

Group by minute and two minute intervalsWhat options exist for copying data between servers on a regular basis?query optimization: time intervalsreturn percentage of records based on value in columnQuery (duration) different between data value changeDatetime column vs Date + Time columnsInsert data between 30 minute time intervalsGroup daily schedule into [Start date; End date] intervals with the list of week daysHow to reduce table partition timing for existing table in SQL ServerNon-aggregated columns in group byLookup Table Decisions - Lookup Based On Characters In Specific Positions

8

3

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

sql-server-2008 query t-sql

share|improve this question

asked May 9 '12 at 23:01

JoeGeekyJoeGeeky

5773921

add a comment | 

8

3

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

sql-server-2008 query t-sql

share|improve this question

asked May 9 '12 at 23:01

JoeGeekyJoeGeeky

5773921

add a comment | 

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

sql-server-2008 query t-sql

share|improve this question

asked May 9 '12 at 23:01

JoeGeekyJoeGeeky

5773921

CaptureTime   (datetime)

SnapShotValue (int)

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

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

share|improve this question

asked May 9 '12 at 23:01

JoeGeekyJoeGeeky

5773921

share|improve this question

asked May 9 '12 at 23:01

JoeGeekyJoeGeeky

5773921

asked May 9 '12 at 23:01

JoeGeekyJoeGeeky

5773921

asked May 9 '12 at 23:01

JoeGeekyJoeGeeky

5773921

3 Answers
3

active

oldest

votes

10

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)

share|improve this answer

edited May 10 '12 at 13:08

answered May 10 '12 at 6:05

Mikael ErikssonMikael Eriksson

17.9k34687

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 '12 at 12:36
  

  
  
  
  

add a comment | 

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

share|improve this answer

edited May 10 '12 at 1:25

answered May 9 '12 at 23:34

WT_WWT_W

5471616

add a comment | 

0

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' 

share

answered 9 mins ago

LoMaPhLoMaPh

1011

New contributor

LoMaPh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f17669%2fgrouping-records-based-on-intervals-of-time%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

10

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)

share|improve this answer

edited May 10 '12 at 13:08

answered May 10 '12 at 6:05

Mikael ErikssonMikael Eriksson

17.9k34687

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 '12 at 12:36
  

  
  
  
  

add a comment | 

10

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)

share|improve this answer

edited May 10 '12 at 13:08

answered May 10 '12 at 6:05

Mikael ErikssonMikael Eriksson

17.9k34687

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 '12 at 12:36
  

  
  
  
  

add a comment | 

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)

share|improve this answer

edited May 10 '12 at 13:08

answered May 10 '12 at 6:05

Mikael ErikssonMikael Eriksson

17.9k34687

select dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0),

       sum(SnapShotValue)

from YourTable

group by dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0)

dateadd(minute, 5 + (datediff(minute, 0, CaptureTime) / 5) * 5, 0)

share|improve this answer

edited May 10 '12 at 13:08

answered May 10 '12 at 6:05

Mikael ErikssonMikael Eriksson

17.9k34687

answered May 10 '12 at 6:05

Mikael ErikssonMikael Eriksson

17.9k34687

answered May 10 '12 at 6:05

Mikael ErikssonMikael Eriksson

17.9k34687

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

share|improve this answer

edited May 10 '12 at 1:25

answered May 9 '12 at 23:34

WT_WWT_W

5471616

add a comment | 

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

share|improve this answer

edited May 10 '12 at 1:25

answered May 9 '12 at 23:34

WT_WWT_W

5471616

add a comment | 

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

share|improve this answer

edited May 10 '12 at 1:25

answered May 9 '12 at 23:34

WT_WWT_W

5471616

select dateadd(minute,1,convert(varchar(20),capturetime)), sum(snapshotvalue)

from snapshotdata sd

group by dateadd(minute,1,convert(varchar(20),capturetime))

share|improve this answer

edited May 10 '12 at 1:25

answered May 9 '12 at 23:34

WT_WWT_W

5471616

answered May 9 '12 at 23:34

WT_WWT_W

5471616

answered May 9 '12 at 23:34

WT_WWT_W

5471616

0

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' 

share

answered 9 mins ago

LoMaPhLoMaPh

1011

New contributor

LoMaPh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

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' 

share

answered 9 mins ago

LoMaPhLoMaPh

1011

New contributor

LoMaPh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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' 

share

answered 9 mins ago

LoMaPhLoMaPh

1011

New contributor

LoMaPh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

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' 

share

answered 9 mins ago

LoMaPhLoMaPh

1011

New contributor

LoMaPh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 9 mins ago

LoMaPhLoMaPh

1011

New contributor

LoMaPh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 9 mins ago

LoMaPhLoMaPh

1011