Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
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
Updated 4-Jul-22 0:17am
v2

1 solution

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
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900