Thread: Having trouble with Interval computation

Having trouble with Interval computation

From
"Jim Ballard"
Date:
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
 
But this gives me a syntax error at "year".  I also can't find a way to cast this to the desired interval units.
 
What is the proper way to express this in Postgres?  Will the standard form be supported at some point?
 
Thanks very much,
 
Jim Ballard
 

Re: Having trouble with Interval computation

From
Peter Eisentraut
Date:
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


Re: Having trouble with Interval computation

From
Thomas Lockhart
Date:
> 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