Re: Convert TimeStamp to Date

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