15min Average - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

15min Average

Author

Topic

KandS
Starting Member

1 Post

Posted - 2014-10-12 : 08:31:52

I have a table which holds temperature data which is recorded every minute, it has the following fields
date (type Date)
time (type Time)
temp1 (type float)

Example data would be
2014-10-12, 00:00:00, 19.87
2014-10-12, 00:01:00, 20.25
2014-10-12, 00:02:00, 18.41
etc etc so there is 1440 records per day

I can use the following to return the data logged every 15 minutes
SELECT * FROM test1 WHERE date = '2014-10-12' GROUP BY UNIX_TIMESTAMP(time) DIV 900

This returns me 4 points per hour but what I want is the average of the data in each of the 15 minute periods

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-10-12 : 13:43:44

This looks very much like MySQL question, which would be better answered in MySQL forum (This is MSSQL server forum).
However, this might help you along:

select `date`
       ,date_add(convert('00:00:00',time),interval floor((hour(`time`)*60+minute(`time`))/15)*15 minute) as `time`
       ,avg(temp1) as temp1
  from temperature
 group by `date`
         ,floor((hour(`time`)*60+minute(`time`))/15)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-10-13 : 06:04:00

GROUP BY DATEDIFF(MINUTE, '19000101', Col1) / 15

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources