How do I subtract two times on Postgres if they are stored as character varying?-CodePudding

I have a table that has stored columns time_attended and time_departed from a site.

for example time_attended = "16:00" and time_departed = "17:00"

They are stored as character varying fields - how would I find the time difference?

Doing a conversion eg - SELECT time_attended::date - time_departed::date FROM table

throws up the syntax error 'invalid input syntax for type date: "16:00"'

Any ideas? thanks

CodePudding user response：

If those are time values, cast them to a time not a date

select time_attended::time - time_departed::time
from the_table