人大金仓SQL 错误 [42883]: 错误: 操作符不存在: timestamp without time zone <= time with time zone

Question

    此代码为人大金仓sql:

    SELECT ROUND(CAST(IFNULL((
        SELECT count(*) "online"
        FROM cms_all_status
        WHERE deptid = '4195bacc64614089ba98ff75d2482fbb' AND detecttime BETWEEN (now() - INTERVAL'120 SECOND') AND CURRENT_TIME
    ) / (
        SELECT count(*) total
        FROM cms_all_status
        WHERE deptid = '0214b47ce0f144278d9ac8e5a51c06c9'
    ), 0) AS NUMERIC), 2) online_rate

出现 SQL 错误 [42883]: 错误: 操作符不存在: timestamp without time zone <= time with time zone
  Hint: 没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
  Position: 151 At Line: 5, Line Position: 68

怎么解决

Answer 1

这个错误是因为在比较“timestamp without time zone”类型的值和“time with time zone”类型的值时，PostgreSQL数据库无法找到匹配的操作符，因此它建议您增加明确的类型转换。

要解决此问题，请使用以下代码在比较前将“timestamp without time zone”类型的值转换为“time with time zone”类型：

SELECT ROUND(
    CAST(
        IFNULL(
            (
                SELECT COUNT(*) "online" 
                FROM cms_all_status 
                WHERE deptid = '4195bacc64614089ba98ff75d2482fbb' 
                AND detecttime BETWEEN (NOW() - INTERVAL '120 SECOND') AND CURRENT_TIME::time
            ) / (
                SELECT COUNT(*) total 
                FROM cms_all_status 
                WHERE deptid = '0214b47ce0f144278d9ac8e5a51c06c9'
            ), 0
        ) as NUMERIC
    ), 2
) online_rate;