pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: José Soares <jose@sferacarta.com>
To: Paulo Roberto Kappke <paulok@cyclades.com.br>
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Doubts in timespan
Date: Fri, 27 Aug 1999 15:15:01 +0200
Message-ID: <37C68F55.EB6CC268@sferacarta.com> (raw)
References: <37C53FD3.8410737A@cyclades.com.br>

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




view thread (3+ messages)  latest in thread

Message-ID: <37C68F55.EB6CC268@sferacarta.com>
Permalink:  ../37C68F55.EB6CC268@sferacarta.com/
Also on:    postgresql.org/message-id/37C68F55.EB6CC268@sferacarta.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: jose@sferacarta.com, paulok@cyclades.com.br
  Subject: Re: [SQL] Doubts in timespan
  In-Reply-To: <37C68F55.EB6CC268@sferacarta.com>
  References: <37C53FD3.8410737A@cyclades.com.br> <37C68F55.EB6CC268@sferacarta.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox