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 B0D3AD1B52B for ; Fri, 19 Sep 2003 13:10:05 +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 41784-01 for ; Fri, 19 Sep 2003 10:09:36 -0300 (ADT) Received: from smtp014.mail.yahoo.com (smtp014.mail.yahoo.com [216.136.173.58]) by svr1.postgresql.org (Postfix) with SMTP id 06DA0D1B518 for ; Fri, 19 Sep 2003 10:09:32 -0300 (ADT) Received: from ptil-138-190-chn.primus-india.net (HELO IVESIA67) (sgnerd@203.196.190.138 with login) by smtp.mail.vip.sc5.yahoo.com with SMTP; 19 Sep 2003 13:09:34 -0000 Message-ID: <038501c37eaf$ff68a310$7502a8c0@hdsc.com> Reply-To: "Kumar" From: "Kumar" To: "psql" Subject: Problem with timestamp - Pls help Date: Fri, 19 Sep 2003 18:44:36 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0382_01C37EDE.133BC6F0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/196 X-Sequence-Number: 15174 This is a multi-part message in MIME format. ------=_NextPart_000_0382_01C37EDE.133BC6F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Dear Friends, I am using Postgres 7.3.4 on Linux server 7.3.=20 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 To achieve this I have used the following wats=3D# select now(); now ------------------------------ 2003-09-19 18:39:58.62398+00 (1 row) wats=3D# select substr(now(),1,22); substr ------------------------ 2003-09-19 18:40:01.25 (1 row) wats=3D# select timestamp(substr(now(),1,22)); ERROR: parser: parse error at or near "substr" at character 18 wats=3D# 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 typ= es You may need to add explicit typecasts wats=3D# 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 =3D substr(now(),1,22); ERROR: column "rec_modified_date" is of type timestamp without time zone b= ut expression is of type text You will need to rewrite or cast the expression Please help me with this. Regards Senthil Kumar S ------=_NextPart_000_0382_01C37EDE.133BC6F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Dear Friends,

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