金仓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

这个SQL语句的错误是因为在比较时间戳和时间时，没有匹配的操作符。你可以尝试添加类型转换来解决这个问题。例如，将时间戳转换为时间或将时间转换为时间戳。您可以在比较时间戳和时间时使用“AT 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 AT TIME ZONE 'UTC') / (SELECT count() total FROM cms_all_status WHERE deptid = '0214b47ce0f144278d9ac8e5a51c06c9'), 0) AS NUMERIC), 2) online_rate

这个语句可以将当前时间转换为UTC时间，并与检测时间戳进行比较。

相关问题

金仓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

根据错误信息看，是因为在比较 `detecttime` 和 `CURRENT_TIME` 时，类型不匹配导致的。`detecttime` 是 `timestamp without time zone` 类型，而 `CURRENT_TIME` 是 `time with time zone` 类型。可以通过将 `CURRENT_TIME` 转换成 `timestamp without time zone` 类型来解决这个问题，具体可以使用 `CAST(CURRENT_TIME AS timestamp without time zone)` 来进行强制类型转换，修改后的 SQL 如下：

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

将mysql 创建的函数CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_backend_detection`(IN deviceid VARCHAR(50), IN pkid1 INT(11)) BEGIN update `sys_ptz_device` set is_valid=3 where device_id=deviceid ; update `sys_ptz_conf` set device_id =null where device_id=deviceid; delete from `sys_backend_detection` where device_id=deviceid and pkid=pkid1; END修改成金仓函数

金仓数据库中创建存储过程的语法与MySQL有一些不同，需要进行一些修改。下面是修改后的代码：

CREATE PROCEDURE delete_backend_detection(IN deviceid VARCHAR(50), IN pkid1 INT)
AS
BEGIN
    UPDATE sys_ptz_device SET is_valid = 3 WHERE device_id = deviceid;
    UPDATE sys_ptz_conf SET device_id = NULL WHERE device_id = deviceid;
    DELETE FROM sys_backend_detection WHERE device_id = deviceid AND pkid = pkid1;
END;

请注意，金仓数据库中不需要指定 DEFINER，也不需要使用分号作为语句的结束符。同时，INT 类型的长度在金仓中也不需要指定。