以下是两种不同格式:
from_unixtime(ceil(update_time/1000),'yyyy-MM-dd HH:mm:ss')
from_unixtime(ceil(1536249600000/1000),"yyyy-MM-dd'T'HH:mm:ss'Z'")
select
cs.cno,
o.fullname,
bb.name office,
bt.name team,
cs.enterprise_id,
-- call_date,
from_unixtime(ceil(cs.call_date/1000),'yyyy-MM-dd HH:mm:ss') call_date,
--from_unixtime(ceil(call_date/1000),"yyyy-MM-dd'T'HH:mm:ss'Z'"),
SUM(cs.bridge_effective_duration) bridge_effective_duration,
SUM(cs.effective_tel_count) effective_tel_count,
SUM(cs.bridge_tel_hand_count) bridge_tel_hand_count,
SUM(cs.bridge_tel_liexiaobao_count) bridge_tel_liexiaobao_count,
SUM(cs.bridge_tel_shangji_count) bridge_tel_shangji_count
from ods.stics cs
left join ods.employees o
on cs.cno=o.exttel
left join ods.branchs bb
on bb.number=regexp_replace(regexp_replace(o.branchno, '\\{', ''), '\\}', '')
left join ods.teams bt
--on o.teamsno=bt.number
on bt.number=regexp_replace(regexp_replace(o.teamsno, '\\{', ''), '\\}', '')
WHERE
enterprise_id='7131'
AND from_unixtime(ceil(call_date/1000),'yyyy-MM-dd') >= '2019-05-01' AND from_unixtime(ceil(call_date/1000),'yyyy-MM-dd') <= '2019-05-05'
GROUP BY
cs.cno,
cs.enterprise_id,
o.branchno,
cs.call_date,
o.fullname,
bb.name,
bt.name