Time difference between two columns storing only time in SQL-CodePudding

I have two columns (session_starttime & session_endtime) whose data type is a text. As seen in the image, it is only the time part that is stored and no date.

I am struggling to get a session duration in seconds. The ultimate goal is to get the average time per session.

I have tried the code below but it did not work

select 
avg(datediff('second', session_endtime,session_starttime)) as Average_settlement_time
from user_sessions ```

ERROR

(psycopg2.errors.UndefinedFunction) function datediff(unknown, text, text) does not exist LINE 8: avg(datediff('second', session_endtime,session_starttime)) a...

HINT: No function matches the given name and argument types. You might need to add explicit type casts.

CodePudding user response：

PostgreSQL doesn't have a datediff function.

You can explicitly cast both columns to time and then subtract them, resulting in an INTERVAL result:

SELECT session_endtime::TIME - session_starttime::TIME
FROM mytable
-- E.g. returns 0 years 0 mons 0 days 0 hours 1 mins 40.00 secs for the first row

If you want a number of seconds instead of an INTERVAL, you can extract the EPOCH from it:

SELECT EXTRACT(EPOCH FROM session_endtime::TIME - session_starttime::TIME)
FROM mytable
-- E.g. returns 100 for the first row