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.
