Re: Temporal query question

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