X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A69D6D1B49B for ; Tue, 2 Mar 2004 06:01:53 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 32110-10 for ; Tue, 2 Mar 2004 02:01:51 -0400 (AST) Received: from smtp.polbox.com (smtp2.polbox.com [213.241.3.136]) by svr1.postgresql.org (Postfix) with ESMTP id EA11BD1B476 for ; Tue, 2 Mar 2004 02:01:39 -0400 (AST) Received: (from root@localhost) by smtp.polbox.com (8.11.6/8.9.2) id i2261gi19788 for pgsql-sql@postgresql.org..procmail; Tue, 2 Mar 2004 07:01:42 +0100 Received: from kitten.yoyogi.hottolink.com ([219.96.225.254]) by smtp.polbox.com (8.11.6/8.9.2) with ESMTP id i2261fj19781 for ; Tue, 2 Mar 2004 07:01:42 +0100 Date: Tue, 2 Mar 2004 15:01:33 +0900 From: Wojtek Reply-To: Wojtek X-Priority: 3 (Normal) Message-ID: <16712460437.20040302150133@polbox.com> To: pgsql-sql@postgresql.org Subject: What's wrong with my date/interval arithmetic? MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/19 X-Sequence-Number: 17118 Hi, I ran this query on Postgres 7.3: select min_time, max_time, min_time+age(max_time,min_time) as result, to_timestamp('2003-10-17 23:07:00','YYYY-MM-dd HH24:MI:SS') +age(to_timestamp('2003-12-01 03:50:45','YYYY-MM-dd HH24:MI:SS'), to_timestamp('2003-10-17 23:07:00','YYYY-MM-dd HH24:MI:SS')) as expected_result from (select min(postingblogdate) as min_time from blg_weblog_header) as min_time, (select max(postingblogdate) as max_time from blg_weblog_header) as max_time and get a result like this: min_time,max_time,result,expected_result 2003-10-17 23:07:00,2003-12-01 03:50:45,2003-12-02 03:50:45,2003-12-01 03:50:45 Why is the "result" incorrect (off by one day)? When I do the exactly same arithmetic using timestamps created with to_timestamp, everything is OK ("expected_result"). Is this a bug or am I doing something worng? thanks for any thoughts about this problem, Wojtek P.S. postingblogdate is of type "timestamp without time zone"