Thread: Interval to months

I have a field which contains an interval value and I sometimes need to represent the full interval (not a part) as a
decimalnumber of months.  For example, "5 years 6 mons 3 days" as "66.1 months".  I've been trying to figure out how to
dothis and haven't found a definitive answer.   

The following gives an approximation:
round(cast(extract(epoch from time_interval)/2592000 as numeric), 2) || ' months'

The number 2592000 is seconds in a 30 day month.  Accounting for leap years, etc. Google calculates it as 2629743.83.
Thething is that the 30 day month number gives the right answer for short intervals while the Google number gives the
rightanswer for longer intervals (several years or more.) Is there a better way? 

--Aram

On Aug 7, 2012, at 8:41 AM, Aram Fingal <fingal@multifactorial.com> wrote:

> I have a field which contains an interval value and I sometimes need to represent the full interval (not a part) as a
decimalnumber of months.  For example, "5 years 6 mons 3 days" as "66.1 months".  I've been trying to figure out how to
dothis and haven't found a definitive answer.   
>
> The following gives an approximation:
> round(cast(extract(epoch from time_interval)/2592000 as numeric), 2) || ' months'
>
> The number 2592000 is seconds in a 30 day month.  Accounting for leap years, etc. Google calculates it as 2629743.83.
The thing is that the 30 day month number gives the right answer for short intervals while the Google number gives the
rightanswer for longer intervals (several years or more.) Is there a better way? 


Something like this?

select 12 * extract(year from ?) + extract(month from ?) + extract(epoch from ? - date_trunc('month', ?)) / 2592000

Ugly, but likely closer to accurate. You can't get actually accurate, of course, as you don't know how long a month is.

Cheers,
  Steve