I have a table with a column defined as time CHAR(6) with values such as '18:00' that I need to convert from char to time .
time CHAR(6)
'18:00'
char
time
I searched here but failed.
The syntax :: can be used to create a value:
::
SELECT my_column::time FROM my_table
If the value is indeed a valid time, you can simply specify it:
select '18:00'::time
As said, you can use :: to cast, but you can also use the standard CAST () function:
SELECT CAST(my_column AS time) AS my_column_time FROM my_table;
This also works on other databases, not just PostgreSQL.