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(at)objectwareinc(dot)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(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+