PostgreSQL合并date与time列查询近10分钟数据的报错解决问询

问题原因 #

你当前的报错是因为两边对比的类型不匹配：

• WHERE子句左侧用TO_CHAR()将拼接后的时间转换成了文本类型
• 右侧CURRENT_TIME - INTERVAL '10' MINUTE返回的是带时区的time类型
  两种不同类型的数据无法直接做大小对比，所以触发了运算符不存在的报错。
  另外你使用CURRENT_TIME也不符合需求，你要比对的是完整的日期+时间点，应该用CURRENT_TIMESTAMP而不是仅获取当前时间。

正确实现方式 #

直接把date类型的mlx_date和time类型的mlx_time拼接成timestamp without time zone类型，再和当前时间减10分钟的时间戳做对比即可，不需要转成字符：

SELECT
    ip_visit_1.ipv1_firstname, 
    ip_visit_1.ipv1_lastname, 
    ip_visit_1.ipv1_num, 
    ns_medication.mlx_date, 
    ns_medication.mlx_time, 
    ns_medication.mlx_item
FROM
    ns_medication
    INNER JOIN ip_visit_1
        ON ns_medication.mlx_acctnum = ip_visit_1.ipv1_num
WHERE
    -- 拼接date和time为timestamp类型，和10分钟前的时间戳对比
    (ns_medication.mlx_date + ns_medication.mlx_time) >= CURRENT_TIMESTAMP - INTERVAL '10 minutes'

补充说明 #

• PostgreSQL原生支持date + time直接运算得到timestamp类型，不需要额外转换函数
• 如果你的业务涉及跨时区场景，可以拼接成timestamptz类型后再做对比：(mlx_date + mlx_time) AT TIME ZONE '你的时区' >= CURRENT_TIMESTAMP - INTERVAL '10 minutes'
• 这个写法还可以用到mlx_date和mlx_time上的索引，如果转成字符后对比会导致索引失效，查询性能更低

内容的提问来源于stack exchange，提问作者Perk8