Код:
select up.PC as host,
up.date_add_tmz as date,
max(up.uptime_sec) as uptime_sec,
(select concat(cast(floor(max(up.uptime_sec)/60/60/24) as char(4)),' ', 'дн.', ' ',
cast(floor(mod(max(up.uptime_sec)/60/60/24,1)*24) as char(2)),':',
cast(floor(mod(mod(max(up.uptime_sec)/60/60/24,1)*24,1)*60) as char(2)),':',
cast(round(mod(mod(mod(max(up.uptime_sec)/60/60/24,1)*24,1)*60,1)*60) as char(2)))
from dual) as uptime
from (select hs.host as PC,
DATE(DATE_ADD(FROM_UNIXTIME(clock), INTERVAL (tm.value-3) HOUR)) AS date_add_tmz,
DATE_ADD(FROM_UNIXTIME(clock), INTERVAL (tm.value-3) HOUR) as datetime_add_tmz,
value_avg as uptime_sec
from zabbix.trends as tr,
(SELECT * FROM zabbix.hostmacro where macro='{$TIMEZONE}' and value <> '') as tm,
(SELECT distinct hostid, host FROM zabbix.hosts) as hs,
zabbix.items as it,
(select * from zabbix.hosts_groups where groupid in (225, 227, 229, 231, 233, 235, 237, 239)) as gr
where clock BETWEEN 1660251600 and 1660481705
and hs.hostid=tm.hostid
and tr.itemid=it.itemid
and gr.hostid=hs.hostid
and it.hostid=hs.hostid
and it.key_ in ('system.uptime')) as up where datetime_add_tmz NOT LIKE '%00:00:00' group by date, host order by host;
