问题
pgsql毫秒级时间戳,转换时间的问题,报错
问题呈现
# 问题1
> ERROR: operator does not exist: text / integer
LINE 6: to_char(to_timestamp(rtqc_expire_time/1000) AT TIME ZONE 'U...
# 问题2
ERROR: operator does not exist: character = integer
原因:PostgreSQL8.3以后,取消了默认类型转换。因此需要使比较的类型保持一致。
关键点,进行数据转换
rtqc_expire_time::int
SELECT
name,
phone,
rtqc_number,
identity_number,
to_char(to_timestamp(rtqc_expire_time::INT8/1000) AT TIME ZONE 'UTC-8','YYYY-MM-DD HH:MI:SS') as time
FROM
t_driver
WHERE
owner_organization_id = 36