Yep ... looks like it got broken in 7.3. 7.2.4 works fine, but 7.3 (and CVS tip) doesn't.
This is because in 7.2, timestamp_date () and timestamptz_date () do the same thing -
convert the time to Julian date and then subtruct the offset for Y2K, and both work.
In 7.3 (and 7.4) timestamptz_date () is still doing that (and select '1999-12-31 00:00:01'::timestamptz::date still works),
*but* timestamp_date() is changed for some reason to just divide the timestamp by the number of microseconds per day, that is obviously wrong for the case when ts is negative (before 2000) because integer division (unlike floor ()) truncates towards zero...
I'd send the patch... but just thought I would better be done by someone who knows the reason why that function had changed to begin with...
Dima
Claudio Lapidus wrote:
template1=# insert into t values ('1993-08-10 17:48:41');So we are talking about August 10th, right?
INSERT 16980 1
template1=# select f1, date(f1), f1::date, cast(f1 as date) from t;
f1 | date | f1 | f1
---------------------+------------+------------+------------
1993-08-10 17:48:41 | 1993-08-11 | 1993-08-11 | 1993-08-11
(1 row)
Here all casts give Aug. 11th, same as on my 7.3.2 (tested right now). This is one day *more* than expected, not 'the previous date' as the original poster said. Perhaps some sort of rounding here?
cl.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
