Forum OpenACS Q&A: Best way to select between hours?

Posted by Rocael Hernández Rizzardini on 03/05/01 03:23 PM

Hi all!
I would like to know what are the best way to do select of a period of
hours in the days from a PG table that has a sysdate field, I did this
with:
select creation_user from chat_msgs where to_char(creation_date,
'HH24:MI:SS') > '11:30:00' and to_char(creation_date, 'HH24:MI:SS') <
'14:30:00' group by creation_user;

Is another better way to do this?
bye,

2: Response to Best way to select between hours? (response to 1)

Posted by Michael A. Cleverly on 03/05/01 04:43 PM

The easiest way to get all the values within a given range of dates (or integers, etc.) would be to use select ... where column between 'x' and 'y'.

Taking your query above, something like:

select distinct creation_user from chat_msgs where to_char(creation_date, 'HH24:MI:SS') between '11:30:00' and '14:30:00'

3: Response to Best way to select between hours? (response to 1)

Posted by Dan Wickstrom on 03/05/01 04:48 PM

Try something like:

select creation_user from chat_msgs where creation_date between date_trunc('days',creation_date) + '11:30:00'::interval and date_trunc('days',creation_date) + '14:30:00'::interval group by creation_user;

One thing, the between operator is inclusive of the end-points, so you might have to change it slightly if you don't want to include the end-points.

4: Response to Best way to select between hours? (response to 1)

Posted by Don Baccus on 03/05/01 11:23 PM

In general ...

to_char is provided as an Oracle compatibility function and isn't real bright. In particular, something like
<blockquote><pre>
to_char(creation_date, 'HH24:MI:SS') between '11:30:00' and '14:30:00'
</pre></blockquote>
will never use an index on creation_date in Postgres. It might in Oracle because it is needed in so many situations.
<p>
Dan's example should be able to use an index on creation_date so should run a lot faster if the table's got lots of rows in it.
<p>
You can get PG to use indexes and to_char(), but the way you'd do that would be to create a functional index using the to_char function...
more trouble than it's worth in this case.