From: Tom Lane <tgl@sss.pgh.pa.us>
To: Wojtek <wojtg@polbox.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: What's wrong with my date/interval arithmetic?
Date: Tue, 02 Mar 2004 10:03:06 -0500
Message-ID: <27414.1078239786@sss.pgh.pa.us> (raw)
In-Reply-To: <162716013640.20040302160046@polbox.com>
References: <16712460437.20040302150133@polbox.com>
<23923.1078208721@sss.pgh.pa.us>
<162716013640.20040302160046@polbox.com>
Wojtek <wojtg@polbox.com> 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
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: tgl@sss.pgh.pa.us, wojtg@polbox.com
Subject: Re: What's wrong with my date/interval arithmetic?
In-Reply-To: <27414.1078239786@sss.pgh.pa.us>
* 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