Thread: Having trouble with Interval computation

Jim Ballard writes:

> I'd like to get the difference between two timestamp columns expressed
> as an interval with units "year to month."  I believe the standard way
> to express this is as:
>
>    (timestamp1 - timestamp2) year to month

timestamp1 - timestamp2

The units of the interval are calculated automatically.

> But this gives me a syntax error at "year".  I also can't find a way
> to cast this to the desired interval units.

extract, date_part, date_trunc are available.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

> I'd like to get the difference between two timestamp columns expressed
> as an interval with units "year to month."
...
> What is the proper way to express this in Postgres?  Will the standard
> form be supported at some point?

lockhart=# select age('today', '1957-06-13');
          age
-----------------------
 44 years 1 mon 3 days
(1 row)

lockhart=# select date_trunc('month', age('today', '1957-06-13'));
   date_trunc
----------------
 44 years 1 mon
(1 row)

Note that if the age() function is not used, and instead you use the
subtraction operator, then the result is zero, since the operator gives
a days/hours/min/sec result and does not preserve *any* year or month
information.

"Standard form", that is SQL9x rather than Ingres, will likely be
supported at some point, maybe sooner rather than later if the syntax
can fit into our parser.

hth

                      - Thomas