Re: Mechanics of Select

On Feb 11, 2008, at 2:23 AM, Willem Buitendyk wrote:

> As others have suggested my big problem with the function I wrote
> was that I had made it Volatile instead of Immutable (it is no
> doubt suffering from code bloat as well). That made all the
> difference. Curiously though - I tried it just with the date_trunc
> function and it was just as slow as my old Volatile function.
>
> select * from track where datetime >= '2007-04-01' and datetime <
> date_trunc('month','2007-04-01'::timestamp)+interval '1 month'; was
> about 55s

That's probably because '2007-04-01'::timestamp can be at different
time zones depending on client configuration and hence is volatile.

If you need a timestamp you probably want to use the servers TZ,
which you can specify using: timestamp at <your timezone>

Are you always entering the first day of a month for start date? In
that case you can leave out the entire date_trunc as the interval
already calculates the correct length internally:

template1=> select '2007-04-01'::date + interval '1 month';
?column?
---------------------
2007-05-01 00:00:00

> select * from track where datetime >= '2007-04-01' and datetime <
> first_day_next_month('2007-04-01'); was about 36s

Also, specifying dates like this without specifying their format is a
bad habit in my book. You're in trouble as soon as the date format
for your database changes (different system, for example). I suggest
you use to_date('2007-04-01', 'YYYY-MM-DD') instead.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,47b015f9167323996417255!