How to group by hour, minute, second, 15 minutes, etc.

Question

0.00/5 (No votes)

See more:

This is my first question in this forum, so first of all thank you for your help.

I have a database in which all tables have a column called Time_Stamp.

The tables are storing data that is being collected from different devices and the storage period can change depending on the device.

That is to say, depending on the device it can be saved every second or every minute or every 5 minutes, etc.

The case is that I need to make a query in which I can choose the dates (from - to) and also show the results according to the user choice (second, minute, 5 minute, 15 minute, hour or day)

What I have tried:

With this query

select
  trend021.Time_Stamp,
  PCS_6_ANLYZR_2_ACTIVE_ENERGY_EXP,
  METEO_6_CR01_POA
from
  trend021
inner join 
  trend041 
   on TREND021.time_stamp = trend041.time_stamp
WHERE (
      trend021.Time_Stamp >= DATEADD(day, DATEDIFF(day, 360, GETDATE()), 0))  
  and (METEO_6_CR01_POA >50)

I get a result of a number of days (360), although I'm not sure if this is right, the result is like this

But what I need is that the result can appear grouped or only get the result that matches the period you specify (second, minute, hour, 5 minutes, 15 minutes, etc).

I hope you can help me

Thank you very much!

Posted 3-Jul-22 13:19pm

Member 10024137

Updated 4-Jul-22 0:17am

Mohibur Rashid

v2

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2022-07-04T00:17:00

One approach would be to generate a list of the time periods you want and then join your data table to that.

For example, if I want to group data by every 5 minutes, I can generate the "time slots" like this

SQL

declare @start datetime = (SELECT MIN(datum) from #demo);
declare @end datetime = (SELECT MAX(datum) from #demo);
declare @slotwidth int = 5;
WITH timeslots AS
(
	SELECT @start AS startpoint, dateadd(MINUTE, @slotwidth, @start) as enddate
	UNION ALL
	SELECT  dateadd(MINUTE, @slotwidth, startpoint), dateadd(MINUTE, @slotwidth, enddate) as enddate
	FROM timeslots
	WHERE dateadd(MINUTE, @slotwidth, enddate) <= @end
)
SELECT  *
FROM timeslots t;

For my demo data in #demo this gives

startpoint				enddate
2022-07-04 10:58:33.290	2022-07-04 11:03:33.290
2022-07-04 11:03:33.290	2022-07-04 11:08:33.290
2022-07-04 11:08:33.290	2022-07-04 11:13:33.290
2022-07-04 11:13:33.290	2022-07-04 11:18:33.290
2022-07-04 11:18:33.290	2022-07-04 11:23:33.290

I can then join those results to my original data, grouping by each timeslot e.g.

SQL

declare @start datetime = (SELECT MIN(datum) from #demo);
declare @end datetime = (SELECT MAX(datum) from #demo);
declare @slotwidth int = 5;
WITH timeslots AS
(
	SELECT @start AS startpoint, dateadd(MINUTE, @slotwidth, @start) as enddate
	UNION ALL
	SELECT  dateadd(MINUTE, @slotwidth, startpoint), dateadd(MINUTE, @slotwidth, enddate) as enddate
	FROM timeslots
	WHERE dateadd(MINUTE, @slotwidth, enddate) <= @end
)
SELECT  t.startpoint, t.enddate, count(*)
FROM timeslots t
inner join #demo d on D.datum between T.startpoint AND T.enddate
group by t.startpoint, T.enddate;

which in my case gave me

startpoint				enddate				    (No column name)
2022-07-04 10:58:33.290	2022-07-04 11:03:33.290	6
2022-07-04 11:03:33.290	2022-07-04 11:08:33.290	6
2022-07-04 11:08:33.290	2022-07-04 11:13:33.290	6
2022-07-04 11:13:33.290	2022-07-04 11:18:33.290	6
2022-07-04 11:18:33.290	2022-07-04 11:23:33.290	6

I generated some sample data with the following - basically just a number every minute from when I started...

SQL

WITH q AS
(
	SELECT  GETDATE() AS datum, 1 as etc
	UNION ALL
	SELECT  dateadd(MINUTE, 1, datum), etc + 1
	FROM    q
	WHERE dateadd(MINUTE, 1, datum) < dateadd(MINUTE, 30, getdate())
)
SELECT  DATUM, etc
into #demo 
FROM    q;

Edit: If you want to include seconds in these slots then make SECOND your datepart and just amend variable @slotwidth to always represent your desired slot width in seconds

How to group by hour, minute, second, 15 minutes, etc.

1 solution

Solution 1

Add your solution here

Preview 0

How to group by hour, minute, second, 15 minutes, etc.

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us