Skip site navigation (1) Skip section navigation (2)

Re: Temporal query question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: Stefano Bonnin <stefano(dot)bonnin(at)comai(dot)to>,pgsql-general(at)postgresql(dot)org
Subject: Re: Temporal query question
Date: 2004-11-30 16:33:13
Message-ID: 21085.1101832393@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Richard Huxton <dev(at)archonet(dot)com> writes:
> SELECT now() - (1 || ' days')::interval;

Note that the preferred form is

	SELECT now() - (n * '1 day'::interval);

if n is a numeric variable.  When you write

	SELECT now() - (n || ' days')::interval;

you are relying on the following: (1) an implicit cast from n's numeric
type to text; (2) the textual concatenation operator ||; (3) an explicit
cast from text to interval; (4) the timestamp - interval operator.
In the preferred way, '1 day'::interval is (in effect) a compile-time
constant of type interval, and the "*" represents an invocation of the
built-in float8 * interval operator.  So you have (1) an implicit cast
to float8, if n isn't already float8; (2) the float8 * interval
operator; (3) the timestamp - interval operator.  This is probably
significantly faster than the other way, and more importantly it does
not rely on an implicit cast across type categories, which is something
we are trying to get away from.

> You could use CAST(...) instead of course, and a date plus/minus an 
> integer defaults to days.

Right, there are also the date +/- integer operators, which are the best
thing to use if you only want date-level arithmetic.  With timestamp
minus interval you have to consider questions like what happens on
daylight savings transition days.  So the correct answer to this might
just be

	SELECT CURRENT_DATE - 1;

			regards, tom lane

In response to

pgsql-general by date

Next:From: Sally RuggeroDate: 2004-11-30 16:40:18
Subject: Re: delete with index scan
Previous:From: MageDate: 2004-11-30 16:19:47
Subject: Re: change natural column order

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group