From: | Scott Marlowe <smarlowe(at)g2switchworks(dot)com> |
---|---|
To: | Jeffrey Melloy <jmelloy(at)visualdistortion(dot)org> |
Cc: | D A GERM <dgerm(at)shepherd(dot)edu>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Need help writing SQL statement |
Date: | 2005-06-29 18:51:00 |
Message-ID: | 1120071060.8208.119.camel@state.g2switchworks.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 2005-06-29 at 13:20, Jeffrey Melloy wrote:
> D A GERM wrote:
>
> > I have been trying to write an sql statement that returns the same
> > hours in a time stamp no matter what the date.
> > I can to pull same hours on the the same days but have not been able
> > to figure out how to pull all the same hours no matter what the date.
> >
> > Here is the one sql statement I have been using:
> > SELECT COUNT(time_stamp) FROM table WHERE time_stamp BETWEEN
> > 20050629100000 and 20050631100000;
> >
> > Any help would be appreciated.
> >
> > Thanks in advanced for any help
> >
> You can do something like
> SELECT count(*)
> FROM table
> where date_part('hour', timestamp) in (10, 11)
>
> This query is going to require a seq scan, so if you're running it
> frequently you can make an index on date_part('hour', timestamp)
Note that an index created on date_part('hour',timestamp) should be
usable here as long as it's selectable enough.
From | Date | Subject | |
---|---|---|---|
Next Message | Matt Miller | 2005-06-29 18:53:58 | Re: CVS Build - No Doc |
Previous Message | Richard Huxton | 2005-06-29 18:37:15 | Re: Passing a table name to a function for dynamic queries.... |