CODEWITHSUNDEEP
sql
Acubi
Acubi

Reputation: 2783

how to get the average value every 2 hours in sql query?

$query= SELECT channel1, channel2, channel3
FROM `Table`
WHERE `id` =1
AND `dateTime` >= '2011-10-15 00:00:01'
AND `dateTime` <= '2011-10-17 23:59:59'

Based on the above query, how to get the average value of channel1, channel2, channel3 columns every 2 hours?

Upvotes: 2

Views: 4009

Answers (3)

ABueno
ABueno

Reputation: 1

The key for this query is to group records every two hours.

Consider:

floor = a function that returns the largest integer value that is equal to or less than a number (find a similar one in your database)

time_hh = your dateTime column in the 'hhmmm' format, I mean, from '0000' to '23:59' (format your dateTime field using your database functions)

The query would be:

SELECT 
  floor( time_hh / 200 ), 
  min(datetime), 
  max(datetime), 
  avg(channel1), 
  avg(channel2), 
  avg(channel3)
FROM 
  `table`
GROUP BY
  floor( time_hh / 200 )

Upvotes: 0

user359040
user359040

Reputation:

Assuming MySQL:

SELECT date(`dateTime`) dateDay, 2*floor(date_format(`dateTime`,'%H')/2) dateHour,
       avg(channel1), avg(channel2), avg(channel3)
FROM `Table`
WHERE `id` =1
AND `dateTime` >= '2011-10-15 00:00:01'
AND `dateTime` <= '2011-10-17 23:59:59'
group by date(`dateTime`), 2*floor(date_format(`dateTime`,'%H')/2)

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44316

Here is a tsql solution:

SELECT avg(channel1) channel 1, avg(channel2) channel2, avg(channel3) channel3, 
       dateadd(hour, datediff(hour, 0,datetime)/2*2,0) FROM Table 
WHERE id =1 AND 
dateTime >= '2011-10-15 00:00:01' AND 
dateTime <= '2011-10-17 23:59:59' 
group by datediff(hour, 0,datetime)/2*2

Upvotes: 2

Related Questions