Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to query for Interval

How to query for Interval

From
"Vishal Kashyap "
Date:
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);

Any pointers / help would be appreciated


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

Re: How to query for Interval

From
Richard Broersma Jr
Date:
I had a similar question a while ago.

select count( timestmp ) as cnt, date_trunc('hour', timestmp) as hour

from  process_table

where date_trunc('day', timestmp) between '2006-04-01' and '2006-04-11'

group by date_trunc('hour', timestmp);


I do not currently have access to postgresql to verify the syntax however.  But this is the gist
of it.

Regards,

Richard Broersma Jr.

--- Vishal Kashyap  <vishalonlist@gmail.com> wrote:

> 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);
>
> Any pointers / help would be appreciated
>
>
> --
> With Best Regards,
> Vishal Kashyap.
> http://www.vishal.net.in
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

Re: How to query for Interval

From
Richard Broersma Jr
Date:
This thread might be useful to you also.

http://archives.postgresql.org/pgsql-sql/2006-04/msg00014.php

Regards,

Richard

--- Vishal Kashyap  <vishalonlist@gmail.com> wrote:

> 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);
>
> Any pointers / help would be appreciated
>
>
> --
> With Best Regards,
> Vishal Kashyap.
> http://www.vishal.net.in
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

Re: How to query for Interval

From
"A. Kretschmer"
Date:
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    ===

Re: How to query for Interval

From
"Vishal Kashyap "
Date:
thanks Richard and Andreas. You rock.

On 4/12/06, A. Kretschmer <andreas.kretschmer@schollglas.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