Skip site navigation (1) Skip section navigation (2)

Re: How to query for Interval

From: "Vishal Kashyap " <vishalonlist(at)gmail(dot)com>
To: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: How to query for Interval
Date: 2006-04-12 13:07:04
Message-ID: 77b69d210604120607m3e6c7838w4a32fa32811a88c8@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
thanks Richard and Andreas. You rock.

On 4/12/06, A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com> wrote:
> 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    ===
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>


--
With Best Regards,
Vishal Kashyap.
http://www.vishal.net.in

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2006-04-12 14:01:33
Subject: Re: Configure command from a precompiled package
Previous:From: Mag GamDate: 2006-04-12 11:39:56
Subject: Removing Tsearch2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group