pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Doubts in timespan
3+ messages / 3 participants
[nested] [flat]

* Doubts in timespan
@ 1999-08-26 13:23 Paulo Roberto Kappke <paulok@cyclades.com.br>
  1999-08-27 13:15 ` Re: [SQL] Doubts in timespan José Soares <jose@sferacarta.com>
  1999-08-30 15:19 ` Re: [SQL] Doubts in timespan Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 2 replies; 3+ messages in thread

From: Paulo Roberto Kappke @ 1999-08-26 13:23 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Doubts in timespan
  1999-08-26 13:23 Doubts in timespan Paulo Roberto Kappke <paulok@cyclades.com.br>
@ 1999-08-27 13:15 ` José Soares <jose@sferacarta.com>
  1 sibling, 0 replies; 3+ messages in thread

From: José Soares @ 1999-08-27 13:15 UTC (permalink / raw)
  To: Paulo Roberto Kappke <paulok@cyclades.com.br>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] Doubts in timespan
  1999-08-26 13:23 Doubts in timespan Paulo Roberto Kappke <paulok@cyclades.com.br>
@ 1999-08-30 15:19 ` Herouth Maoz <herouth@oumail.openu.ac.il>
  1 sibling, 0 replies; 3+ messages in thread

From: Herouth Maoz @ 1999-08-30 15:19 UTC (permalink / raw)
  To: Paulo Roberto Kappke <paulok@cyclades.com.br>; pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-08-30 15:19 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-26 13:23 Doubts in timespan Paulo Roberto Kappke <paulok@cyclades.com.br>
1999-08-27 13:15 ` José Soares <jose@sferacarta.com>
1999-08-30 15:19 ` Herouth Maoz <herouth@oumail.openu.ac.il>

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