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

select fire_lat ptz_lat, fire_lon ptz_lon, count(1) count from enbogis.bis_all_fire_alarm_index where 1=1 and districtcode is not NULL and alarm_time is not null and source_lon is not null and source_lat is not null and fire_lon is not null and fire_lat is not null这是一段金仓sql 其中is not null失效

可能是因为数据表中存在空字符串，而空字符串不是 NULL 值。您可以使用以下语句将空字符串转换为 NULL 值：

update enbogis.bis_all_fire_alarm_index set districtcode = null where districtcode = '';

然后再执行您的 SQL 语句，应该就能正确筛选出非空的记录了。