Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA00844 for ; Fri, 27 Aug 1999 09:18:58 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.9.3/8.9.3/NETTuno 3.3) with ESMTP id PAA22199; Fri, 27 Aug 1999 15:18:32 +0200 (MDT) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 11KNca-00020N-00; Fri, 27 Aug 1999 15:08:52 +0000 Message-ID: <37C68F55.EB6CC268@sferacarta.com> Date: Fri, 27 Aug 1999 15:15:01 +0200 From: =?iso-8859-1?Q?Jos=E9?= Soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: Paulo Roberto Kappke CC: pgsql-sql@postgresql.org Subject: Re: [SQL] Doubts in timespan References: <37C53FD3.8410737A@cyclades.com.br> Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Ola' Paulo Roberto, --I think your problem is caused by the UPDATE statement which doesn't allow subselects: --The following example returns a valid timespan: prova=> select age('Thu Aug 26 09:31:00 1999 EST'); age ----------------- @ 7 hours 29 mins (1 row) --and I can insert it in the elapsed_time field that is a timespan data type, using a subselect: prova=> insert into tasktime (elapsed_time) (select age('Thu Aug 26 09:31:00 1999 EST')); INSERT 535009 1 --but I can't UPDATE it using a subselect: prova=> update tasktime set elapsed_time = (select age('Thu Aug 26 09:31:00 1999 EST')); ERROR: parser: parse error at or near "select" I hope this help. José Paulo Roberto Kappke ha scritto: > Hello all, > > I'm having some problems with timespan type. > > I have in the same table an attribute named "inserted_time" with > datetime type and another attribute named "elapsed_time" with timespan > type. > > When I'll insert a new entry in this table, I need to calculate the > difference between last "inserted_time" and new "inserted_time", and > update "elapsed_time" in the last row. > > I used the following command: > > "UPDATE tasktime SET elapsed_time='age((SELECT inserted_time FROM > tasktime WHERE row=$row),(SELECT inserted_time FROM tasktime WHERE > row=$old_row))' WHERE row=$old_row" > > And I received the message: > > "PostgresSQL query failed: ERROR: Bad timespan external representation > 'age(Thu Aug 26 09:31:00 1999 EST, Thu Aug 26 09:29:00 1999 EST)'" > > I tried to change the update command as follow: > > "UPDATE tasktime SET elapsed_time='age($actual_inserted_time, > $old_inserted_time)' WHERE row=$old_row" > > And I received the same message. > > Any body knows where are my mistake ???? > > Any body could explain how is the data in a timespan type ???? > > Thanks in advance, > > Paulo Roberto Kappke > Cyclades Corporation > > ************