Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TfPsy-0005xT-NY for pgsql-bugs@arkaria.postgresql.org; Mon, 03 Dec 2012 06:52:12 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TfPsx-0007Um-CY for pgsql-bugs@arkaria.postgresql.org; Mon, 03 Dec 2012 06:52:11 +0000 Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TfPAO-0007Se-A9 for pgsql-bugs@postgresql.org; Mon, 03 Dec 2012 06:06:08 +0000 Received: from smtprelay0145.b.hostedemail.com ([64.98.42.145] helo=smtprelay.b.hostedemail.com) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TfPAK-0000eL-M5 for pgsql-bugs@postgresql.org; Mon, 03 Dec 2012 06:06:07 +0000 Received: from filter.hostedemail.com (b-bigip1 [10.5.19.254]) by smtprelay05.b.hostedemail.com (Postfix) with SMTP id 191B8F9C306; Mon, 3 Dec 2012 06:06:03 +0000 (UTC) X-Panda: scanned! X-Session-Marker: 616C76686572726540616C76682E6E6F2D69702E6F7267 X-Filterd-Recvd-Size: 2600 Received: from perhan.alvh.no-ip.org (unknown [190.95.4.9]) (Authenticated sender: alvherre@alvh.no-ip.org) by omf12.b.hostedemail.com (Postfix) with ESMTPA; Mon, 3 Dec 2012 06:06:02 +0000 (UTC) Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 59A866E0CE; Mon, 3 Dec 2012 03:05:57 -0300 (CLST) Date: Mon, 3 Dec 2012 03:05:57 -0300 From: Alvaro Herrera To: aanisimov@inbox.ru Cc: pgsql-bugs@postgresql.org Subject: Re: BUG #7722: extract(epoch from age(...)) appears to be broken Message-ID: <20121203060556.GA4601@alvh.no-ip.org> References: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.21 (2010-09-15) Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-bugs Precedence: bulk Sender: pgsql-bugs-owner@postgresql.org aanisimov@inbox.ru wrote: > The following bug has been logged on the website: >=20 > Bug reference: 7722 > Logged by: Artem Anisimov > Email address: aanisimov@inbox.ru > PostgreSQL version: 9.2.1 > Operating system: Slackware Linux 14.0/amd64 > Description:=20=20=20=20=20=20=20=20 >=20 > The following to queries give the same result (first arguments to age() > differ in the day number only, second arguments are identical): >=20 > select extract(epoch from age('2012-11-23 16:41:31', '2012-10-23 > 15:56:10')); >=20 > and >=20 > select extract(epoch from age('2012-11-22 16:41:31', '2012-10-23 > 15:56:10')); alvherre=3D# select age('2012-11-22 16:41:31', '2012-10-23 15:56:10'); age=20=20=20=20=20=20=20=20 ------------------ 30 days 00:45:21 (1 fila) alvherre=3D# select age('2012-11-23 16:41:31', '2012-10-23 15:56:10'); age=20=20=20=20=20=20=20 ---------------- 1 mon 00:45:21 (1 fila) The problem is that age() returns 30 days in one case, and "one month" in t= he other; extract() then considers the month as equivalent to 30 days. This is documented as such, see [1]. [1] http://www.postgresql.org/docs/current/static/functions-datetime.html I think if you want a precise computation you should just subtract the two dates and then extract epoch from the result. alvherre=3D# select extract(epoch from timestamp '2012-11-22 16:41:31' - '2= 012-10-23 15:56:10'); date_part=20 ----------- 2594721 (1 fila) alvherre=3D# select extract(epoch from timestamp '2012-11-23 16:41:31' - '2= 012-10-23 15:56:10'); date_part=20 ----------- 2681121 (1 fila) --=20 =C1lvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services --=20 Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs