Thread: 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
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)
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)
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
wats=# select to_timestamp(substr(now(),1,22));
ERROR: Function to_timestamp(text) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
wats=# select date(substr(now(),1,22));
date
------------
2003-09-19
(1 row)
ERROR: parser: parse error at or near "substr" at character 18
wats=# select to_timestamp(substr(now(),1,22));
ERROR: Function to_timestamp(text) does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
wats=# select date(substr(now(),1,22));
date
------------
2003-09-19
(1 row)
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
You will need to rewrite or cast the expression
Please help me with this.
Regards
Senthil Kumar S
> 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