SQL calculate time diff in View

user_14478240 0
Viewed 66

How do I make function call in VIEW in order to calculate the difference between two time variables What I have right now is

CREATE TABLE Recordings
(
    recording_id INTEGER PRIMARY KEY,
    start_time TIME,
    end_time TIME
)

CREATE VIEW duration(start_time, end_time)
    AS SELECT R.start_time, R.end_time
        #Function call to calculate time
    FROM Recordings R;

I looked up SQL documentation, it only has date_part which calculates the date difference, how do I calculate TIME?

postgresql sql
3 Answers

Just subtract the two values:

CREATE VIEW duration(start_time, end_time)
AS 
SELECT R.start_time, R.end_time,
       R.end_time - R.start_time as duration
FROM Recordings R;

The result is a value of type interval. If you want something else, there are plenty of examples on how to convert an interval to e.g. the number of minutes or seconds.

user_3303150
user_50196100

You can use timediff function as

timediff(R.end_time,R.start_time)

to calculate the time.

user_147789380
Related