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