Need help writing SQL statement

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Need help writing SQL statement

D A GERM
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

--

"Well then what am I supposed to do with all my creative ideas- take a bath and wash myself with them? 'Cause that is what soap is for" (Peter, Family Guy)



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: Need help writing SQL statement

Jeffrey Melloy
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)

Jeff

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: Need help writing SQL statement

Scott Marlowe
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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: Need help writing SQL statement

D A GERM
thank you all for your help.

this solved it:
SELECT count(*) FROM table where date_part('hour', time_stamp) in (10, 11);


Scott Marlowe wrote:

>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.
>  
>

--
D. Aaron Germ
Scarborough Library, Shepherd University
(304) 876-5423

"Well then what am I supposed to do with all my creative ideas- take a bath and wash myself with them? 'Cause that is what soap is for" (Peter, Family Guy)



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match