JZTXT

json

发布时间 2023-06-07 12:18:41作者: 一万年行不行
//查询时间偏移
select * from  coiner where created_at + interval '8 hour' > '2023-01-13 00:00:00'

//jsonb 查询 attribute.Award.type = 'ttc'
attribute -> 'Award' ->> 'type' = 'ttc


//时间偏移去重分组
select count(distinct(user_id) ),to_char(created_time + interval '7 hour', 'YYYY-MM-DD') as dd
from ramadan_user_checkin_log
where created_time > '2023-04-01'
group by dd


--时间偏移 json内容sum
select 
 sum((attribute ->> 'deduct_ttc')::NUMERIC) as deduct_ttc,
 to_char(created_time + interval '8 hour', 'YYYY-MM-DD') as dd 
from bean_records 
where come_from = 'recharge' and status = 'add_bean_success' and qa = 'false' 
group by dd;