Comparing time in text format in psql 12

Question

SELECT *
FROM   lighting
WHERE  cast("time" as timestamp) BETWEEN '23:55:00'::timestamp
AND now();

But I get the error as follows:

ERROR: column "23:55:00::timestamp" does not exist LINE 3: WHERE cast("time" as timestamp) BETWEEN "23:55:00::timestam...

My "time" column is as follows in a text format

05:50:53
06:58:38
07:30:42

What am I doing wrong?

Answer 1

It seems your field "time" is having values as time which can not be converted into timestamp.

So try this way:

SELECT *
FROM   lighting
WHERE  cast("time" as time) BETWEEN '23:55:00'::time
AND current_time;