From: Wojtek <wojtg@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?
Date: Tue, 2 Mar 2004 16:00:46 +0900
Message-ID: <162716013640.20040302160046@polbox.com> (raw)
In-Reply-To: <23923.1078208721@sss.pgh.pa.us>
References: <16712460437.20040302150133@polbox.com>
<23923.1078208721@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: wojtg@polbox.com, tgl@sss.pgh.pa.us
Subject: Re: What's wrong with my date/interval arithmetic?
In-Reply-To: <162716013640.20040302160046@polbox.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox