Thread: Temporal query question

The usual syntax is something like:

select myfield,numeric_field
from mytable
where temporal_attribute_a < temporal_attribute_b - ( numeric_field * '1 day'::interval);

One of the temporal_attributes tends to be 'now' or 'today' but the
principle is the same...

On Tue, Nov 30, 2004 at 11:30:16AM +0100, Stefano Bonnin wrote:
> Hi all,
>
> I have a "simple" question about the following temporal query with "interval" operator:
>
> can I define a query of this type?
>
> select myfield,numeric_field from mytable where temporal_attribute < temporal_attribute - interval numeric_field || '
days'
>
> obviously this syntax is wrong.
> In other words, is there a way to define a query with a interval operator with variable argument?
> Thanks
> Stefano
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Stefano Bonnin wrote:
> Hi all,
>
> I have a "simple" question about the following temporal query with
> "interval" operator:
>
> can I define a query of this type?
>
> select myfield,numeric_field from mytable where temporal_attribute <
> temporal_attribute - interval numeric_field || ' days'
>
> obviously this syntax is wrong.

Not by much:

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

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

--
   Richard Huxton
   Archonet Ltd

Richard Huxton <dev@archonet.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