Thread: Help with SQL updating not working.

Hi all,

I'm trying to perform an update and having a bad brain day!

It involves calculating the different between two times and inserting
the difference into a third field

Create the table

test=# CREATE TABLE the_times (time1 time, time2 time, time_diff
integer); -- time_diff is in seconds;
CREATE TABLE


Insert test values (all the same - doesn't matter)


test=# insert into the_times (time1, time2) values ('08:34:21', '09:31:21');
INSERT 0 1
test=# insert into the_times (time1, time2) values ('08:34:21', '09:31:21');
INSERT 0 1
test=# insert into the_times (time1, time2) values ('08:34:21', '09:31:21');
INSERT 0 1
test=# insert into the_times (time1, time2) values ('08:34:21', '09:31:21');
INSERT 0 1

test=# select * from the_times;

  time1     |   time2   | time_diff
----------+----------+----------------------
 08:34:21 | 09:31:21 |
 08:34:21 | 09:31:21 |
 08:34:21 | 09:31:21 |
 08:34:21 | 09:31:21 |


Now, into the time_diff field, I want to insert the time difference in seconds

I've tried various combinations of this

update the_times as  set time_diff = extract ('epoch' from
(the_times.time2 - the_times.time1)::interval)  from the_times;

but to no avail.

Could any kind soul help me out on this?

TIA and rgs,


Paul...

Paul Linehan <linehanp@tcd.ie> wrote:

> Hi all,
>
> I'm trying to perform an update and having a bad brain day!
>
> It involves calculating the different between two times and inserting
> the difference into a third field
>
> Create the table
>
> test=# CREATE TABLE the_times (time1 time, time2 time, time_diff
> integer); -- time_diff is in seconds;
> CREATE TABLE

the time_diff - column are unnecessary, it can be calculated in the
select.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°