Re: [SQL] Doubts in timespan

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 1999EST'));
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
>
> ************