PostgreSQL Group Data by Every 15 Minutes

[wldrumstcs]

I have a PostgreSQL table with multiple columns including "oper_time" of type "timestamp without time zone". I wanted to output the time as well as the count(*) and group the data by hour and by every 15 minutes. In other words, I want to the output to look like:

hr | mn | thecount
---------------------------
11 | 00 | 38
11 | 15 | 52
11 | 30 | 79
11 | 45 | 51
12 | 00 | 54
12 | 15 | 33

etc....

Here's what I have so far, but it tells me I need to group by oper_time or use it in an aggregate function. However, I don't want to group by oper_time, and I'm not sure where to go from here.


SELECT extract(hour from oper_time) as hr, extract(minute from oper_time) as mn,count(*) as thecount
FROM log WHERE CAST(oper_time As Date) >= '09/23/2010'
GROUP BY CAST(oper_time as Date),extract(hour from oper_time),floor(extract(minute from oper_time)/15)
ORDER BY CAST(oper_time as Date),extract(hour from oper_time),extract(minute from oper_time)


Thanks!

[Old Pedant]

I have never used PostGres, but clearly your answer can't be right.

Just the first line makes no sense:

SELECT extract(hour from oper_time) as hr, extract(minute from oper_time) as mn,count(*) as thecount

If you had records such as
7:03
7:05
7:12

then that SELECT would *FORCE* you to get each of those times, INDIVIDUALLY.

I'm 90% certain that you need to use the same FLOOR expression in the SELECT that you are using in the GROUP BY.
Except that the FLOOR expression is going to give you values of 0,1,2, and 3. Only. So it needs fixing, as well.

And, too, since you want to both group by and order by the date-alone, that needs to be in the SELECT.

To wit:

SELECT CAST(oper_time as Date) AS dt,
extract(hour from oper_time) as hr,
floor(extract(minute from oper_time)/15) * 15 AS mn,
count(*) as thecount
FROM log WHERE CAST(oper_time As Date) >= '09/23/2010'
GROUP BY CAST(oper_time as Date),
extract(hour from oper_time),
floor(extract(minute from oper_time)/15) * 15
ORDER BY dt, hr, mn

[wldrumstcs]

You, sir, are my hero. I am not very good with SQL, as you can tell. That did the trick.

Thanks!

[guelphdad]

Wldrumstcs

If you are posting in future re Postgres, please indicate that in your thread title and post in the general database forum rather than this forum which is for mysql only. Syntax can be different between databases.

[wldrumstcs]

Whoops, you are right. Sorry about that!