On Aug 7, 2012, at 8:41 AM, Aram Fingal <fin...@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 decimal number of months. For
> example, "5 years 6 mons 3 days" as "66.1 months". I've been trying to
> figure out how to do this 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 right answer 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
