Re: How to query for Interval - Mailing list pgsql-novice

am  12.04.2006, um  2:23:12 +0530 mailte Vishal Kashyap  folgendes:
> Hi,
>
>
> I have a table with time stamps in it . These timestamp represent a event count.
> I just wanted to know how could I query such that I get a count of
> event per hour.
>
> Table structure is
>
> my_table(id serial, time_event timestamp,event varchar(200));
>
> I want to do something like
> select count(id) from  my_table where time_event in (every 1 hour);

*untested*

select x, count(b.*)
    from generate_series(0,23) x,
        my_table y
    where date_trunc('hour', y.time_event) '2006/04/12'::date + (x||'hour')::interval
    group by x
    order by x;

The result should be a table for every hour for the given date.


HTH, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===