ERROR:合并类型BYTEA和时间戳没有时区不能匹配
问题描述:
我试图运行此查询,但我得到这个错误:ERROR:合并类型BYTEA和时间戳没有时区不能匹配
ERROR: COALESCE types bytea and timestamp without time zone cannot be matched
我想这个问题是在这条线:
COALESCE(:last_timestamp,date_trunc('hour', timestamp '1969-01-01 00:00:00.000000'))
参数:
:last_timestamp
可以为NULL,这就是为什么我这样的情况下
@Query(value="SELECT\n" +
" id,\n" +
" wristband_records_processed.device_id as deviceId,\n" +
" user_id as userId,\n" +
" rssi,\n" +
" record_timestamp as locationTimestamp,\n" +
" beacon_id as beaconId,\n" +
" locatable_devices.locatable_type as locatableType,\n" +
" coalesce(EXTRACT(EPOCH FROM (record_timestamp - lag(record_timestamp) OVER(order by record_timestamp))),0)as duration\n" +
" FROM wristband_records_processed\n" +
" LEFT JOIN locatable_devices on locatable_devices.device_id = wristband_records_processed.beacon_id\n" +
"where user_id = :userId and record_type != 'HANDWASH'\n" +
" and rssi >= -85 AND locatable_type != 'UNKNOWN' AND wristband_records_processed.record_timestamp> COALESCE(:last_timestamp,date_trunc('hour', timestamp '1969-01-01 00:00:00.000000')) \n " +
"OFFSET :offset LIMIT :limit ;", nativeQuery = true)
感谢您的帮助
答
只是让他们同样
COALESCE(date_trunc('hour', :last_timestamp),
date_trunc('hour', timestamp '1969-01-01 00:00:00.000000'))
或
COALESCE(:last_timestamp, timestamp '1969-01-01 00:00:00.000000')
+0
同样的错误...不为我工作 –
我试过很多的铸造方法,但其中不适合我 –
工作在哪里在Spring代码中定义并设置last_timestamp? –
如果它只是类型,而不是值 - 试试'COALESCE(:last_timestamp :: text :: timestamptz'或甚至'COALESCE(to_timestamp(:last_timestamp :: text):: timestamptz' –