Subtract two timestamptz values and insert the result into a third column-CodePudding

I have the following table:

CREATE TABLE duration
(
departure_time TIMESTAMPTZ, 
arrival_time TIMESTAMPTZ, 
duration TIME  NOT NULL, -- Not sure about the datatype.. 
flight_id INT UNIQUE NOT NULL,

CHECK (scheduled_duration > 0),
CHECK (scheduled_arrival_time > scheduled_departure_time),

FOREIGN KEY (flight_id) REFERENCES flight(flight_id),
PRIMARY KEY (scheduled_departure_time, scheduled_arrival_time)
);

I want to calculate arrival_time - departure_time and then insert the result into the column duration. Preferably, the result of the duration subtraction would be 6h 30m. I am new to databases and PostgreSQL and I can't find a way to calculate a subtraction of two timestamps, taking into consideration their timezones at the same time.

CodePudding user response：

Use a generated column

CREATE TABLE duration
(
departure_time TIME WITH TIME ZONE, 
arrival_time TIME WITH TIME ZONE, 
scheduled_duration INT,
flight_id INT,
duration2 TIME GENERATED ALWAYS AS ("arrival_time"::time - "departure_time"::time) STORED,
CHECK (scheduled_duration > 0),
CHECK (arrival_time > departure_time),

FOREIGN KEY (flight_id) REFERENCES flight(flight_id),
PRIMARY KEY (departure_time, arrival_time)
);

CodePudding user response：

SELECT
    EXTRACT(EPOCH FROM '2022-07-07 15:00:00.00000'::TIMESTAMP - '2022-07-07 15:00:00.00000'::TIMESTAMP)