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










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