[GENERAL] Need help writing SQL statement

2005-06-29 Thread 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 
2005062910 and 2005063110;


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

Re: [GENERAL] Need help writing SQL statement

2005-06-29 Thread 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 
2005062910 and 2005063110;


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

Re: [GENERAL] Need help writing SQL statement

2005-06-29 Thread 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 
  2005062910 and 2005063110;
 
  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

Re: [GENERAL] Need help writing SQL statement

2005-06-29 Thread 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 
2005062910 and 2005063110;


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