Re: [BUGS] [GENERAL] Convert TimeStamp to Date

#1

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:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#2

Dmitry Tkach <dmitry (AT) openratings (DOT) com> writes:

Quote:

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...

Good catch. It looks like Tom Lockhart changed this routine when he was
adding the int64-timestamp option. He probably had a momentary brain
fade about the direction of rounding needed :-(

I've changed it back to doing things the 7.2 way in CVS tip. This will
be in 7.3.4 unless Marc already wrapped the tarball, which I don't think
he did.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] [GENERAL] Convert TimeStamp to Date

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] [GENERAL] Convert TimeStamp to Date - 07-23-2003 , 03:57 PM

Re: [BUGS] [GENERAL] Convert TimeStamp to Date - 07-23-2003 , 07:39 PM