Re: Get interval in months

Gerhard,

Check out:

http://www.postgresql.org/docs/8.3/interactive/functions-datetime.html

In particular, look at 'age()' or 'justify_days()', but I think age() is
the one you want.

SELECT age('2008-02-01'::timestamp, '2008-01-01'::timestamp) AS "1 month"

Produces: "1 mon"

SELECT justify_days('2008-02-01'::timestamp - '2008-01-01'::timestamp) AS "1
month"

Produces "1 mon 1 day"

One thing to be aware of is the different behaviors in these functions.
age() uses the actual number of days in the month when representing the
time, where justify_days() always assumes 30 days. In your example, January
has 31 days, not 30, which is why the difference of '1 day' in the output.
It's a little more obvious in this example, where both queries give a number
of days:

SELECT age('2008-02-05'::timestamp, '2008-01-01'::timestamp) AS "1 month"

Produces: "1 mon 4 days"

SELECT justify_days('2008-02-05'::timestamp - '2008-01-01'::timestamp) AS "1
month"

Produces: "1 mon 5 days"

Had I used June into July, these would have agreed at '1 mon 4 days'.

Dave

On Sat, Nov 8, 2008 at 4:12 AM, Gerhard Heift <
ml-postgresql-20081012-3518(at)gheift(dot)de> wrote:

> Hello,
>
> I want to get an interval in months from two dates:
>
> SELECT '2008-02-01'::timestamp - '2008-01-01'::timestamp AS '1 month'
>
> Here I want '1 month' and not '31 days' as answer.
>
> How can I do this?
>
> Regards,
> Gerhard
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFJFVgNa8fhU24j2fkRAq0ZAJ925CHwchm/kZCwTTDcZF/QVXAlewCeMYGL
> h3y0pxtiOiDV7pExYiEcSZ0=
> =84G4
> -----END PGP SIGNATURE-----
>
>

--
David Spadea
President
Spadea Enterprises, Inc
http://www.spadea.net