I am using Postgres 7.3.4 on Linux server 7.3. <= /DIV>

I wanted to update one column of my table with now() or= =20 timestamp. And I want that timestamp of format=20 ------------------------
2003-09-19 18:39:08.13

To achieve this I have used the following

wats=3D# select=20 now();
&nbs= p; =20 now
------------------------------
2003-09-19=20 18:39:58.62398+00
(1 row)

wats=3D# select=20 substr(now(),1,22);
&nb= sp;=20 substr
------------------------
2003-09-19 18:40:01.25
(1=20 row)

wats=3D# select=20 timestamp(substr(now(),1,22));
ERROR: parser: parse error a= t or=20 near "substr" at character 18
wats=3D# select=20 to_timestamp(substr(now(),1,22));
ERROR: Function=20 to_timestamp(text) does not exist
      &n= bsp;=20 Unable to identify a function that satisfies the given argument=20 types
        You may need to add exp= licit=20 typecasts
wats=3D# select=20 date(substr(now(),1,22));
   =20 date
------------
2003-09-19
(1 row)

Also I cant directly update my timestamp column as=20 follows

update= =20 "WATS".users set= =20 to_rec_modified_date =3D substr(now(),1,22);

ERROR: column "rec_modified_date" is of type time= stamp=20 without time zone but expression is of type text
You will need to= =20 rewrite or cast the expression

Please help me with this.

Regards

Senthil Kumar S

------=_NextPart_000_0382_01C37EDE.133BC6F0-- From jasiek@klaster.net Fri Sep 19 14:05:28 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 1F4E7D1B506 for ; Fri, 19 Sep 2003 14:05:28 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 47610-04 for ; Fri, 19 Sep 2003 11:04:59 -0300 (ADT) Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by svr1.postgresql.org (Postfix) with ESMTP id 1EBFFD1B507 for ; Fri, 19 Sep 2003 11:04:55 -0300 (ADT) Received: from klaster.net (core-1.citynet.pl [80.48.135.69]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id BC6BD2B3D0; Fri, 19 Sep 2003 16:03:35 +0200 (CEST) Message-ID: <3F6B0CCA.8030001@klaster.net> Date: Fri, 19 Sep 2003 16:03:54 +0200 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL; rv:1.5a) Gecko/20030718 X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: Kumar Cc: psql Subject: Re: Problem with timestamp - Pls help References: <038501c37eaf$ff68a310$7502a8c0@hdsc.com> In-Reply-To: <038501c37eaf$ff68a310$7502a8c0@hdsc.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/197 X-Sequence-Number: 15175 > 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 From sszabo@megazone.bigpanda.com Fri Sep 19 15:40:32 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id ADD3BD1B4E3 for ; Fri, 19 Sep 2003 15:40:32 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 72955-08 for ; Fri, 19 Sep 2003 12:40:00 -0300 (ADT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 925AAD1B514 for ; Fri, 19 Sep 2003 12:40:00 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id D398C352CD; Fri, 19 Sep 2003 08:39:59 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id D241C35277; Fri, 19 Sep 2003 08:39:59 -0700 (PDT) Date: Fri, 19 Sep 2003 08:39:59 -0700 (PDT) From: Stephan Szabo To: Kumar Cc: psql Subject: Re: Problem with timestamp - Pls help In-Reply-To: <038501c37eaf$ff68a310$7502a8c0@hdsc.com> Message-ID: <20030919083808.K212@megazone.bigpanda.com> References: <038501c37eaf$ff68a310$7502a8c0@hdsc.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/202 X-Sequence-Number: 15180 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. From tgl@sss.pgh.pa.us Fri Sep 19 15:42:42 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 3EFFBD1B51E for ; Fri, 19 Sep 2003 15:42:42 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 73789-07 for ; Fri, 19 Sep 2003 12:42:31 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 3D536D1B4FD for ; Fri, 19 Sep 2003 12:42:31 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h8JFgUiP020528; Fri, 19 Sep 2003 11:42:30 -0400 (EDT) To: "Kumar" Cc: "psql" Subject: Re: Problem with timestamp - Pls help In-reply-to: <038501c37eaf$ff68a310$7502a8c0@hdsc.com> References: <038501c37eaf$ff68a310$7502a8c0@hdsc.com> Comments: In-reply-to "Kumar" message dated "Fri, 19 Sep 2003 18:44:36 +0530" Date: Fri, 19 Sep 2003 11:42:30 -0400 Message-ID: <20527.1063986150@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/203 X-Sequence-Number: 15181 "Kumar" 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 From sszabo@megazone.bigpanda.com Fri Sep 19 15:59:50 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id B7A79D1B4E1 for ; Fri, 19 Sep 2003 15:59:50 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 78339-01 for ; Fri, 19 Sep 2003 12:59:20 -0300 (ADT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id A76EBD1B4EB for ; Fri, 19 Sep 2003 12:59:19 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 3BAA83533A; Fri, 19 Sep 2003 08:59:19 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 3A3EC35339; Fri, 19 Sep 2003 08:59:19 -0700 (PDT) Date: Fri, 19 Sep 2003 08:59:19 -0700 (PDT) From: Stephan Szabo To: Kumar Cc: psql Subject: Re: Problem with timestamp - Pls help In-Reply-To: <20030919083808.K212@megazone.bigpanda.com> Message-ID: <20030919085901.F1519@megazone.bigpanda.com> References: <038501c37eaf$ff68a310$7502a8c0@hdsc.com> <20030919083808.K212@megazone.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/205 X-Sequence-Number: 15183 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. From tgl@sss.pgh.pa.us Fri Sep 19 16:27:39 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 76A32D1B523 for ; Fri, 19 Sep 2003 16:27:39 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 86354-08 for ; Fri, 19 Sep 2003 13:27:29 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 84890D1B503 for ; Fri, 19 Sep 2003 13:27:28 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h8JGRRiP021871; Fri, 19 Sep 2003 12:27:27 -0400 (EDT) To: Stephan Szabo Cc: Kumar , psql Subject: Re: Problem with timestamp - Pls help In-reply-to: <20030919083808.K212@megazone.bigpanda.com> References: <038501c37eaf$ff68a310$7502a8c0@hdsc.com> <20030919083808.K212@megazone.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Fri, 19 Sep 2003 08:39:59 -0700" Date: Fri, 19 Sep 2003 12:27:26 -0400 Message-ID: <21870.1063988846@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/206 X-Sequence-Number: 15184 Stephan Szabo 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