How can I count the number of values that appear in a given interval?

Josiah_Haswell

Hi folks! I would like to partition a row into intervals (say 3-hour intervals), and count the number of entries in each interval. It looks like the time_slice function is the one I'm looking for, but it's not clear to me how to write the query. Any help would be very much appreciated! Josiah

[Deleted User]

Look this example : -- create table dbadmin=> create table test (c1 timestamp); CREATE TABLE -- inserted some data dbadmin=> copy test from stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 08-09-2012 03:00:01 >> 08-09-2012 03:00:01 >> 08-09-2012 03:50:12 >> 08-09-2012 09:34:15 >> 08-09-2012 19:03:23 >> 08-09-2012 01:10:20 >> \. -- check the time slice dbadmin=> select c1, time_slice (c1, 10800) from test; c1 | time_slice ---------------------+--------------------- 2012-08-09 03:50:12 | 2012-08-09 03:00:00 2012-08-09 01:10:20 | 2012-08-09 00:00:00 2012-08-09 03:00:01 | 2012-08-09 03:00:00 2012-08-09 03:00:01 | 2012-08-09 03:00:00 2012-08-09 09:34:15 | 2012-08-09 09:00:00 2012-08-09 19:03:23 | 2012-08-09 18:00:00 (6 rows) -- do the group by dbadmin=> select time_slice (c1, 10800), count(*) from test group by 1; time_slice | count ---------------------+------- 2012-08-09 03:00:00 | 3 2012-08-09 09:00:00 | 1 2012-08-09 18:00:00 | 1 2012-08-09 00:00:00 | 1 (4 rows) Let me know if this answer your question Cheers, Eugenia

Josiah_Haswell

Thanks--that's exactly what I was looking for!