X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 097B0D1B233 for ; Fri, 16 Jul 2004 08:52:24 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 91994-01 for ; Fri, 16 Jul 2004 11:52:22 +0000 (GMT) Received: from web41010.mail.yahoo.com (web41010.mail.yahoo.com [66.218.93.9]) by svr1.postgresql.org (Postfix) with SMTP id 3BE99D1B1CE for ; Fri, 16 Jul 2004 08:52:18 -0300 (ADT) Message-ID: <20040716115215.29699.qmail@web41010.mail.yahoo.com> Received: from [66.108.25.20] by web41010.mail.yahoo.com via HTTP; Fri, 16 Jul 2004 04:52:14 PDT Date: Fri, 16 Jul 2004 04:52:14 -0700 (PDT) From: Theodore Petrosky Subject: Re: Problem in age on a dates interval To: Luis Sousa , pgsql-sql@postgresql.org In-Reply-To: <40F795A4.5030206@ualg.pt> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200407/98 X-Sequence-Number: 18350 Luis, wow.... at first I thought I had my head around a leap year problem so I advanced your query a year.... testbed=# SELECT age('2005-05-14 16:00'::timestamp,'2005-02-18 16:00'::timestamp); age ---------------- 2 mons 24 days (1 row) testbed =# SELECT '2005-02-18 16:00'::timestamp +'2 mons 24 days'::interval; ?column? --------------------- 2005-05-12 16:00:00 (1 row) I just thought I would let you know it can get worse..... :) I don't know how daylight savings time is playing this problem... but I didn't expect the problem to grow like that. Ted --- Luis Sousa wrote: > Hi all, > > I'm using PostgreSQL 7.3.3 on i386-pc-linux-gnu, > compiled by GCC gcc > (GCC) 3.3 (Debian), and I don't understand the > results of the following > queries: > > > SELECT age('2004-05-14 16:00'::timestamp,'2004-02-18 > 16:00'::timestamp); > age > ---------------- > 2 mons 25 days > > SELECT '2004-02-18 16:00'::timestamp+'2 mons 25 > days'::interval; > ?column? > --------------------- > 2004-05-13 16:00:00 > > In this case, the age from 2004-05-14 16:00 to > 2004-02-18 16:00 is 2 > mons 25 days, but if I add the age to the initial > date, it returns one > day less!? > > SELECT age('2004-05-26 16:00'::timestamp,'2004-02-18 > 16:00'::timestamp); > age > --------------- > 3 mons 8 days > > SELECT '2004-02-18 16:00'::timestamp+'3 mons 8 > days'::interval; > ?column? > --------------------- > 2004-05-26 16:00:00 > > Here, the age between 2004-05-26 16:00 and > 2004-02-18 16:00 is 3 mons 8 > days, and this interval added to the initial date > gives the correct result!! > __________________________________ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail