postgresql - 在 psql 12 中比较文本格式的时间

Question

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

但我得到如下错误：

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

我的“时间”栏以文本格式如下

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

我究竟做错了什么？

标签： postgresqlpsqlpgadmin-4postgresql-12

Answer 1

看来您的字段“时间”的值time无法转换为timestamp.

所以试试这个方法：

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