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