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?

The query and the error message are not consistent. The query has `'23:55:00'::timestamp`, while the error shows `"23:55:00::timestamp"`. — GMB, Sep 08 '20 at 09:31
It seems your field `"time"` is having values as `time` which can not be converted into `timestamp`, if you want to compare the time only then you can use `time` in place of `timestamp` and 'current_time' in place of `now()` — Akhilesh Mishra, Sep 08 '20 at 09:37

score 1 · Accepted Answer · answered Sep 08 '20 at 09:45

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;

answered Sep 08 '20 at 09:45

Akhilesh Mishra

1 Answers1