From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: How to query for Interval |
Date: | 2006-04-12 05:30:03 |
Message-ID: | 20060412053003.GA1271@webserv.wug-glas.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 ===
From | Date | Subject | |
---|---|---|---|
Next Message | operationsengineer1 | 2006-04-12 06:05:33 | Re: advice on setting up schema sought |
Previous Message | Chris Browne | 2006-04-12 03:48:21 | Re: Curses interface |