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 5FBD6D1BB15 for ; Tue, 2 Mar 2004 15:03:36 +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 99321-10 for ; Tue, 2 Mar 2004 11:03:17 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 7C061D1B55E for ; Tue, 2 Mar 2004 11:03:05 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i22F36nb027415; Tue, 2 Mar 2004 10:03:06 -0500 (EST) To: Wojtek Cc: pgsql-sql@postgresql.org Subject: Re: What's wrong with my date/interval arithmetic? In-reply-to: <162716013640.20040302160046@polbox.com> References: <16712460437.20040302150133@polbox.com> <23923.1078208721@sss.pgh.pa.us> <162716013640.20040302160046@polbox.com> Comments: In-reply-to Wojtek message dated "Tue, 02 Mar 2004 16:00:46 +0900" Date: Tue, 02 Mar 2004 10:03:06 -0500 Message-ID: <27414.1078239786@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/22 X-Sequence-Number: 17121 Wojtek writes: > Investigating that a little further I found out that there is a difference > in results returned by age: > select age(cast(to_timestamp('2003-12-01 03:50:45','YYYY-MM-dd HH24:MI:SS') as timestamp), > cast(to_timestamp('2003-10-17 23:07:00','YYYY-MM-dd HH24:MI:SS') as timestamp)) > is '1 mon 14 days 04:43:45' > and > select 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')) > is '1 mon 13 days 04:43:45' I get '1 mon 14 days 04:43:45' and '1 mon 14 days 05:43:45' respectively. This is a reasonable result for my timezone (EST5EDT), because there is a daylight-savings transition involved: regression=# select to_timestamp('2003-12-01 03:50:45','YYYY-MM-dd HH24:MI:SS'); to_timestamp ------------------------ 2003-12-01 03:50:45-05 (1 row) regression=# select to_timestamp('2003-10-17 23:07:00','YYYY-MM-dd HH24:MI:SS'); to_timestamp ------------------------ 2003-10-17 23:07:00-04 (1 row) Note the October date is taken as GMT-4, the December GMT-5. The hour gained in the fall DST transition is accounted for when doing timezone-aware arithmetic, but not when doing timezone-free arithmetic. I still think the behavior you see is related to the timezone you're using, which you still haven't told us. Also, what PG version are you running, and on what platform? regards, tom lane