Обсуждение: Problem with timestamp - Pls help

> Dear Friends,
>  
> I am using Postgres 7.3.4 on Linux server 7.3.
>  
> I wanted to update one column of my table with now() or timestamp. And I 
> want that timestamp of format ------------------------
>  2003-09-19 18:39:08.13
>  
> To achieve this I have used the following
>  
> wats=# select now();
>              now
> ------------------------------
>  2003-09-19 18:39:58.62398+00
> (1 row)
>  
> wats=# select substr(now(),1,22);
>          substr
> ------------------------
>  2003-09-19 18:40:01.25
> (1 row)
>  
> wats=# select timestamp(substr(now(),1,22));
> ERROR:  parser: parse error at or near "substr" at character 18
select substr(now(),1,22)::timestamp
or
select cast(substr(now(),1,22) as timestamp)

> Also I cant directly update my timestamp column as follows
>  
> 
> update "WATS".users set to_rec_modified_date = substr(now(),1,22);
> 
> ERROR:  column "rec_modified_date" is of type timestamp without time 
> zone but expression is of type text
>  You will need to rewrite or cast the expression
Use syntax above.

Regards,
Tomasz Myrta

On Fri, 19 Sep 2003, Kumar wrote:

> Dear Friends,
>
> I am using Postgres 7.3.4 on Linux server 7.3.
>
> I wanted to update one column of my table with now() or timestamp. And I want that timestamp of format
------------------------

>  2003-09-19 18:39:08.13

I think select CAST(CURRENT_TIMESTAMP(0) AS timestamp without time zone)
may get you what you want without having to rely on the text format.

"Kumar" <sgnerd@yahoo.com.sg> writes:
> I wanted to update one column of my table with now() or timestamp. And I wa=
> nt that timestamp of format ------------------------
>  2003-09-19 18:39:08.13

It sounds like you simply want to limit the fractional precision of the
value.  Why don't you just declare the column as timestamp(2) ?  All
this fooling around with substrings is inefficient and doesn't have much
to do with your real intent anyhow.
        regards, tom lane

On Fri, 19 Sep 2003, Stephan Szabo wrote:

> On Fri, 19 Sep 2003, Kumar wrote:
>
> > Dear Friends,
> >
> > I am using Postgres 7.3.4 on Linux server 7.3.
> >
> > I wanted to update one column of my table with now() or timestamp. And I want that timestamp of format
------------------------
>
> >  2003-09-19 18:39:08.13
>
> I think select CAST(CURRENT_TIMESTAMP(0) AS timestamp without time zone)

I meant (2) above.

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> I think select CAST(CURRENT_TIMESTAMP(0) AS timestamp without time zone)
> may get you what you want without having to rely on the text format.

If you don't want the time zone, there's also LOCALTIMESTAMP(n).  See
http://www.postgresql.org/docs/7.3/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
        regards, tom lane