Thread: Convert TimeStamp to Date
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?
I am now using to_date(TIMESTAMP_FIELD,'YYYY-MM-DD'). It works but
the performance is very poor. My query is about
2-3 times slower now.
So, what is the most efficient way to cast a timestamp field to a
date?
Thank you for your time!
LS
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:
>>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
>
>
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