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

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
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?
Posted by Michael A. Cleverly on
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'

Posted by Dan Wickstrom on
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.

Posted by Don Baccus on
In general ...

to_char is provided as an Oracle compatibility function and isn't real bright.  In particular, something like
to_char(creation_date, 'HH24:MI:SS') between '11:30:00' and '14:30:00'
will never use an index on creation_date in Postgres.  It might in Oracle because it is needed in so many situations.
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.
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.