Thread: current_date vs 'now'

Hi,

I was doing EXPLAIN ANALYZE on a query where I compare against
current_date and noticed the following:
  Filter: (date <= ('now'::text)::date)

I knew about now() but did not know about 'now' and have since learnt
of 'today', 'tomorrow', etc.  Great!

So, I changed my condition to <= 'now' to see if there would be any
improvement, which changed the filter line to:
  Filter: (date <= '2009-02-05'::date)

and without fail this query is 2ms quicker every time.  I alternated
between the two and ran them both several times to make sure it wasn't
just a cache difference.

AFAIK current_date is standard and 'now' and 'today', etc are not...
so that's one reason to continuing using current_date.  However, I
wonder why 'today' and current_date don't generate the same query
plan?

Jamie

Jamie Tufnell <diesql@googlemail.com> writes:
> AFAIK current_date is standard and 'now' and 'today', etc are not...
> so that's one reason to continuing using current_date.  However, I
> wonder why 'today' and current_date don't generate the same query
> plan?

'today'::date is a special string that is recognized by the date type's
input routine, but what it produces is a constant, for instance
'2009-02-05'::date if I executed it today.  current_date is a function
that produces the current date whenever it is executed.  Consider
for instance

regression=# create view v as select current_date as d1, 'today'::date as d2;
CREATE VIEW
regression=# select * from v;    d1     |     d2     
------------+------------2009-02-05 | 2009-02-05
(1 row)

regression=# \d v     View "public.v"Column | Type | Modifiers 
--------+------+-----------d1     | date | d2     | date | 
View definition:SELECT 'now'::text::date AS d1, '2009-02-05'::date AS d2;

As the view definition printout suggests, tomorrow this view will produce 
    d1     |     d2     
------------+------------2009-02-06 | 2009-02-05

because the constant isn't going to change.

As you can see from both this example and your own, our current
implementation of current_date is ('now'::text)::date.  (Which
sucks, we ought to hide it better ;-))  What that actually means
is you have a constant of type text containing the string 'now',
and when the expression is executed there's a *run time* cast to
type date, causing the date input converter to get invoked at
that time.  So it'll still work tomorrow.

Of course, the cost of the runtime interpretation of the string
is what accounts for your speed difference.
        regards, tom lane

On 2/6/09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> As the view definition printout suggests, tomorrow this view will produce
>
>  2009-02-06 | 2009-02-05
>
> because the constant isn't going to change.

Thanks for that explanation Tom.  Very clear and helpful.

Jamie