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@xxxxxxxxx> 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
