SQL calculate time diff in View-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

DATEDIFF(ss,start_time,end_time)

enter link description here

CodePudding user response：

You can use timediff function as

timediff(R.end_time,R.start_time)

to calculate the time.