Обсуждение: Doubts in timespan

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

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

At 16:23 +0300 on 26/08/1999, Paulo Roberto Kappke wrote:


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

If I'm not mistaken, Postgres does not allow subselects to be used in this
context. In any case the age function should not be in quotes...

Instead, the syntax Postgres uses from the days of old would be something like

UPDATE tasktime
SET elapsed_time = age( tt.inserted_time, inserted_time )
FROM tasktime tt
WHERE row=$old_row AND tt.row = $row;

This has nothing to do with timespan.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma