Convert TimeStamp to Date

On Wed, Jul 23, 2003 at 01:36:52PM -0400, shuai@objectwareinc.com wrote:

Hi all,

I am trying to convert a timestamp field to a date. ('1993-08-10
17:48:41.074' to '1993-08-10').
I used date(TIMESTAMP_FIELD), but it seemed working only for date
after year 2000 and return the previous date
for any day before year 2000. Is that a bug or I just didn't
understand the function?

tree=# select date('1993-08-10 17:48:41.074');
date
------------
10-08-1993
(1 row)

Maybe I don't understand what you mean: that was a date before 2000, and
it seems to give the right answer..

I tend to be pedantic and use
select cast('1993-08-10 17:48:41.074' as date)

Cheers,

Patrick

On Wed, 2003-07-23 at 12:48, Patrick Welche wrote:

On Wed, Jul 23, 2003 at 01:36:52PM -0400, shuai@objectwareinc.com wrote:

Hi all,

I am trying to convert a timestamp field to a date. ('1993-08-10
17:48:41.074' to '1993-08-10').
I used date(TIMESTAMP_FIELD), but it seemed working only for date
after year 2000 and return the previous date
for any day before year 2000. Is that a bug or I just didn't
understand the function?

tree=# select date('1993-08-10 17:48:41.074');
date
------------
10-08-1993
(1 row)

Maybe I don't understand what you mean: that was a date before 2000, and
it seems to give the right answer..

I tend to be pedantic and use
select cast('1993-08-10 17:48:41.074' as date)

Works for me, too. PG 7.3.3 on Linux 2.4.20.

template1=# create table t (f1 timestamp);
CREATE TABLE
template1=# insert into t values ('1993-08-10 17:48:41');
INSERT 16980 1
template1=# select * from t;
f1
---------------------
1993-08-10 17:48:41
(1 row)

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)

-- 
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
+-----------------------------------------------------------------+

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.

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:

On Wed, 2003-07-23 at 14:28, 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?

I didn't even notice that. I wonder what version Patrick Welche
is running?

tree=# select date('1993-08-10 17:48:41.074');
date
------------
10-08-1993
(1 row)

"Many eyes make all bugs shallow"

-- 
+-----------------------------------------------------------------+
| Ron Johnson, Jr.        Home: ron.l.johnson@cox.net             |
| Jefferson, LA  USA                                              |
|                                                                 |
| "I'm not a vegetarian because I love animals, I'm a vegetarian  |
|  because I hate vegetables!"                                    |
|    unknown                                                      |
+-----------------------------------------------------------------+

Ron Johnson wrote:

tree=# select date('1993-08-10 17:48:41.074');
date
------------
10-08-1993
(1 row)

This *does* work on 7.3 (I suppose, because it ends up converting *text*
to date, not *timestamp*, and it is timestamp conversion, that's broken):

testdb=# select date('1993-08-10 17:48:41.074'::timestamp);
date
------------
1993-08-11
(1 row)

Dima

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

Thank you and everybody else. This explains it.

But still, is there a solution other than casting a timestamp to
timestamptz and then to date? Because I major concern is
performance.(and I probably can't change to another version) Thanks.

LS

Dmitry Tkach <dmitry@openratings.com> writes:

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