Date: Tue, 2 Mar 2004 16:00:46 +0900
From: Wojtek <wojtg@polbox.com>
Reply-To: Wojtek <wojtg@polbox.com>
Message-ID: <162716013640.20040302160046@polbox.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: What's wrong with my date/interval arithmetic?
In-Reply-To: <23923.1078208721@sss.pgh.pa.us>
References: <16712460437.20040302150133@polbox.com>
	<23923.1078208721@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

TL> It  is  not  "exactly  the  same arithmetic", because to_timestamp
TL> delivers  a  result of type timestamp-with-time-zone, whereas your
TL> other  values  are  evidently timestamp without time zone. You did
TL> not  say  what  timezone  setting  you  are using, but I think the
TL> discrepancy is probably explained by that.

I tried doing this: (casting to 'timestamp without timezone')

select cast(to_timestamp('2003-10-17 23:07:00','YYYY-MM-dd HH24:MI:SS') as timestamp)
        +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))

And it turns out you were right Tom, the result is
2003-12-02 03:50:45, so the data type _does_ matter.

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'

But it still doesn't answer the questions why there is a difference in
results and why it is exactly one day.

The  "timestamp  without  time zone" arithmetic should produce correct
results  when all operations are done _within_ that data type (without
mixing types), yet I'm getting this one day discrepancy.

puzzled Wojtek