Re: [GENERAL] Convert TimeStamp to Date

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');
>>INSERT 16980 1
>>
>>
>So we are talking about August 10th, right?
>
>
>
>>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
>
>