个人笔记~~~
select a.id,a.log_at,to_char(to_timestamp(a.log_at/1000) AT TIME ZONE 'UTC-8','yyyy-MM-dd HH24:MI:SS') as time,b.profile::json->>'name',a.content
from lecture_user_records a,users b,lecture_records c
where a.lecture_record_id = c.id
and b.id = a.user_id
and c.lecture_id = '891a5a90-ff90-11e9-a409-97fc8f56cf4f'
order by a.id desc;
时间戳这里是以毫秒计算,以秒计算的话不需要除以 1000
to_char(to_timestamp(a.log_at/1000) AT TIME ZONE 'UTC-8' ,'yyyy-MM-dd HH24:MI:SS') (没弄明白为什么是-8转换的结果才是对的,不应该是+8吗???)
查询某个字段是json数据格式的其中某个值
b.profile::json->>'name',