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 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?

--Aram
>From pgsql-general-owner(at)postgresql(dot)org Tue Aug 7 12:55:23 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
by mail.postgresql.org (Postfix) with ESMTP id 2BFC58820F6
for <pgsql-general(at)postgresql(dot)org>; Tue, 7 Aug 2012 12:55:23 -0300 (ADT)
Received: from misc.wordtothewise.com ([184.105.179.154] helo=m.wordtothewise.com)
by makus.postgresql.org with esmtp (Exim 4.72)
(envelope-from <steve(at)blighty(dot)com>)
id 1Sym7u-0000N9-0v
for pgsql-general(at)postgresql(dot)org; Tue, 07 Aug 2012 15:55:22 +0000
Received: from satsuke.wordtothewise.com (204.11.227.194.static.etheric.net [204.11.227.194])
(using TLSv1 with cipher AES128-SHA (128/128 bits))
(No client certificate requested)
(Authenticated sender: steve)
by m.wordtothewise.com (Postfix) with ESMTPSA id 80B712DDE4
for <pgsql-general(at)postgresql(dot)org>; Tue, 7 Aug 2012 08:55:08 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=wordtothewise.com;
s=1.wttw; t44354908;
bh=WLfzdX8X5A3gOYOKnVnG3TuL/Acv6kBH7fYxLVfY50U=;
h=Content-Type:Mime-Version:Subject:From:In-Reply-To:Date:
Content-Transfer-Encoding:Message-Id:References:To;
b=JDdqdJl91oM1uVBBjPeRC/qLjx/xfj31xB2l488sI9pS5oGUb08GHPcfKmDPY35Uq
hkJEhKVQpSMc6uk35mePndo+QQPOXmQSBJOt2TmINSJKSichbeeH+hLnTKYLrxyUsS
bcWIpkbdPE2nLKoNQh11lWb2biYR2tsimVQaSxYgContent-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 6.0 \(1485\))
Subject: Re: Interval to months
From: Steve Atkins <steve(at)blighty(dot)com>
In-Reply-To: <B3AEA435-1203-43D7-85C8-4716AB506951(at)multifactorial(dot)com>
Date: Tue, 7 Aug 2012 08:55:07 -0700
Content-Transfer-Encoding: quoted-printable
Message-Id: <41ACEA3B-998A-49CE-9562-364472A71549(at)blighty(dot)com>
References: <B3AEA435-1203-43D7-85C8-4716AB506951(at)multifactorial(dot)com>
To: Postgres-General General <pgsql-general(at)postgresql(dot)org>
X-Mailer: Apple Mail (2.1485)
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201208/136
X-Sequence-Number: 189313

On Aug 7, 2012, at 8:41 AM, Aram Fingal <fingal(at)multifactorial(dot)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
>From pgsql-general-owner(at)postgresql(dot)org Tue Aug 7 13:01:14 2012
Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125])
by mail.postgresql.org (Postfix) with ESMTP id 927A98820F8
for <pgsql-general(at)postgresql(dot)org>; Tue, 7 Aug 2012 13:01:12 -0300 (ADT)
Received: from co1ehsobe004.messaging.microsoft.com ([216.32.180.187] helo=co1outboundpool.messaging.microsoft.com)
by makus.postgresql.org with esmtp (Exim 4.72)
(envelope-from <David_Greco(at)harte-hanks(dot)com>)
id 1SymDV-0000YH-RI
for pgsql-general(at)postgresql(dot)org; Tue, 07 Aug 2012 16:01:11 +0000
Received: from mail189-co1-R.bigfish.com (10.243.78.229) by
CO1EHSOBE007.bigfish.com (10.243.66.70) with Microsoft SMTP Server id
14.1.225.23; Tue, 7 Aug 2012 16:00:56 +0000
Received: from mail189-co1 (localhost [127.0.0.1]) by
mail189-co1-R.bigfish.com (Postfix) with ESMTP id 60FD640363 for
<pgsql-general(at)postgresql(dot)org>; Tue, 7 Aug 2012 16:00:56 +0000 (UTC)
X-Forefront-Antispam-Report: CIP:157.56.237.5;KIP:(null);UIP:(null);IPV:NLI;H:BY2PRD0811HT002.namprd08.prod.outlook.com;RD:none;EFVD:NLI
X-SpamScore: -5
X-BigFish: PS-5(zzc85fh1455Mzz1202hzz8275bh8275dhz2fh2a8h668h839hd25hf0ah107ah)
Received-SPF: pass (mail189-co1: domain of harte-hanks.com designates 157.56.237.5 as permitted sender) client-ip7.56.237.5; envelope-fromvid_Greco(at)harte-hanks(dot)com; helo=BY2PRD0811HT002.namprd08.prod.outlook.com ;.outlook.com ;
Received: from mail189-co1 (localhost.localdomain [127.0.0.1]) by mail189-co1
(MessageSwitch) id 1344355253853097_2778; Tue, 7 Aug 2012 16:00:53 +0000
(UTC)
Received: from CO1EHSMHS010.bigfish.com (unknown [10.243.78.241]) by
mail189-co1.bigfish.com (Postfix) with ESMTP id C309C9C0051 for
<pgsql-general(at)postgresql(dot)org>; Tue, 7 Aug 2012 16:00:53 +0000 (UTC)
Received: from BY2PRD0811HT002.namprd08.prod.outlook.com (157.56.237.5) by
CO1EHSMHS010.bigfish.com (10.243.66.20) with Microsoft SMTP Server (TLS) id
14.1.225.23; Tue, 7 Aug 2012 16:00:52 +0000
Received: from BY2PRD0811MB415.namprd08.prod.outlook.com ([169.254.4.27]) by
BY2PRD0811HT002.namprd08.prod.outlook.com ([10.255.91.165]) with mapi id
14.16.0175.005; Tue, 7 Aug 2012 16:00:51 +0000
From: David Greco <David_Greco(at)harte-hanks(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Feature Request - Postgres FDW
Thread-Topic: Feature Request - Postgres FDW
Thread-Index: Ac10tUhvz/9tdgAvQ4WRAa+1997mEg=
Date: Tue, 7 Aug 2012 16:00:51 +0000
Message-ID: <187F6C10D2931A4386EE8E58E13857F609EF78CB(at)BY2PRD0811MB415(dot)namprd08(dot)prod(dot)outlook(dot)com>
Accept-Language: en-US
Content-Language: en-US
X-MS-Has-Attach:
X-MS-TNEF-Correlator:
x-originating-ip: [63.95.64.254]
Content-Type: multipart/alternative;
boundary="_000_187F6C10D2931A4386EE8E58E13857F609EF78CBBY2PRD0811MB415_"
MIME-Version: 1.0
X-OriginatorOrg: harte-hanks.com
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201208/137
X-Sequence-Number: 189314

--_000_187F6C10D2931A4386EE8E58E13857F609EF78CBBY2PRD0811MB415_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Surprised to see this isn't offered as a Foreign Data Wrapper- one to other Postgres servers. I was attempting to replace some uses I have of dbilink, and found a couple places where I am using it to connect to Postgres. One is for pseudo "Autonomous Transactions"- a db link to the same postgres server with autocommit turned on. Another is to a different database server, simply for separation of concerns, load distribution, etc, where data is rarely but sometimes needed from the originating server.

Has there been any talk of providing a Postgres FDW? What are your thoughts?

~Dave Greco

--_000_187F6C10D2931A4386EE8E58E13857F609EF78CBBY2PRD0811MB415_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal">Surprised to see this isn&#8217;t offered as a Foreign Data Wrapper- one to other Postgres servers. I was attempting to replace some uses I have of dbilink, and found a couple places where I am using it to connect to Postgres. One is for pseudo
&#8220;Autonomous Transactions&#8221;- a db link to the same postgres server with autocommit turned on. Another is to a different database server, simply for separation of concerns, load distribution, etc, where data is rarely but sometimes needed from the originating
server.<o:p></o:p></p>
<p class="MsoNormal"><o:p>&nbsp;</o:p></p>
<p class="MsoNormal">Has there been any talk of providing a Postgres FDW? What are your thoughts?<o:p></o:p></p>
<p class="MsoNormal"><o:p>&nbsp;</o:p></p>
<p class="MsoNormal">~Dave Greco<o:p></o:p></p>
<p class="MsoNormal"><o:p>&nbsp;</o:p></p>
</div>
</body>
</html>

--_000_187F6C10D2931A4386EE8E58E13857F609EF78CBBY2PRD0811MB415_--