Subtracting columns in postgresql table

Question

I have a table with four columns ID, Break name, Time Started, Time Finished.

The data set for Time started and Time finished is Time without Time zone.

I want to add another Column called Duration and want it tp subtract the Time finished with time started. So that on each line that data has been inputted on the duration column will display how long the break was.

Answer 1

Subtracting two timestamps will return an interval that's the difference between them, which should fit your bill:

SELECT *, time_finished - time_Started AS duration
FROM   mytable

Answer 2

As @Mureinik has pointed out, you can do math on timestamps in a query. To fix your table you need to add a duration (or whatever name you prefer) column to your table and the populate the column with an UPDATE query.

First I would create a sandbox table to test out these changes. And when you're happy with the results, you can modify the actual table.

Copy everything from mytable to test

CREATE TABLE test
AS
SELECT * 
FROM mytable

Add the new column with data type interval

ALTER TABLE test
ADD COLUMN duration interval

Populate the new column

UPDATE test
SET duration = time_finished - time_started

Once you're happy with the results in the test table, run the same commands on your live table.