-- string转时间戳
select unix_timestamp('2021-10-13 15:15:00');
-- 时间转时间
select from_unixtime(1634109300);
-- date转时间戳
select unix_timestamp('2021-10-13 15:15:00');
-- string转时区（最后得到的还是string类型）
select convert_tz('2021-10-13 15:15:00', '+00:00', '-08:00');
-- date转时区（最后得到的还是date）
select convert_tz('2021-10-13 15:15:00', '+00:00', '-08:00');
-- 计算时间 (参与计算的两个值可以是 时间类型 时间格式的string类型)
select TIMESTAMPDIFF(MINUTE, '2021-10-13 15:15:00', now());
-- 计算时间 (参与计算的两个值可以是 时间类型 时间格式的string类型)
select datediff('2021-10-11 15:15:00', now());
-- date转string
select date_format(now(), '%Y-%m-%d %H:%i:%s');
-- string转date
select str_to_date('2021-10-11 15:15:00','%Y-%m-%d %H:%i:%s');
-- 延迟报警with查询的sql
WITH log_time_tab as (
SELECT substr(time, 1, 16) AS time,
glib_id,
hostname,
metric_item,
metric_origin,
max(metric_value) as metric_value
FROM t_dhc_metric_20211014
WHERE metric_origin = 11
and TIMESTAMPDIFF(MINUTE, time, now()) < 60
and metric_item in ('sdk_heatbeat', 'online')
GROUP BY substr(time, 1, 16),
glib_id,
hostname,
metric_item,
metric_origin
),
db_time_tab as (
SELECT substr(time, 1, 16) AS time,
glib_id,
hostname,
metric_item,
metric_origin,
max(metric_value) as metric_value
FROM t_dhc_metric_20211014
WHERE metric_origin = 12
and TIMESTAMPDIFF(MINUTE, time, now()) < 60
and metric_item in ('sdk_heatbeat', 'online')
GROUP BY substr(time, 1, 16),
glib_id,
hostname,
metric_item,
metric_origin
),
delay_log_tab as (
SELECT t1.time,
t1.glib_id,
t1.hostname,
t1.metric_item,
COALESCE(t1.metric_value, 0) as metric_value1,
COALESCE(t2.metric_value, 0) as metric_value2,
COALESCE(t1.metric_value, 0) - COALESCE(t2.metric_value, 0) AS diff
FROM db_time_tab t1
left join
log_time_tab t2
ON t1.time = t2.time
AND t1.glib_id = t2.glib_id
AND t1.hostname = t2.hostname
AND t1.metric_item = t2.metric_item
where t1.metric_value - t2.metric_value > 2
)
select *
from delay_log_tab;
-- 延迟报警子查询查询的sql
SELECT t1.time,
t1.glib_id,
t1.hostname,
t1.metric_item,
COALESCE(t1.metric_value, 0) as metric_value1,
COALESCE(t2.metric_value, 0) as metric_value2,
COALESCE(t1.metric_value, 0) - COALESCE(t2.metric_value, 0) AS diff
FROM (
SELECT substr(time, 1, 16) AS time,
glib_id,
hostname,
metric_item,
metric_origin,
max(metric_value) as metric_value
FROM t_dhc_metric_20211014
WHERE metric_origin = 12
and TIMESTAMPDIFF(MINUTE, time, now()) < 60
and metric_item in ('sdk_heatbeat', 'online')
GROUP BY substr(time, 1, 16),
glib_id,
hostname,
metric_item,
metric_origin
) t1
LEFT JOIN (
SELECT substr(time, 1, 16) AS time,
glib_id,
hostname,
metric_item,
metric_origin,
max(metric_value) as metric_value
FROM t_dhc_metric_20211014
WHERE metric_origin = 11
and TIMESTAMPDIFF(MINUTE, time, now()) < 60
and metric_item in ('sdk_heatbeat', 'online')
GROUP BY substr(time, 1, 16),
glib_id,
hostname,
metric_item,
metric_origin
) t2
ON t1.time = t2.time
AND t1.glib_id = t2.glib_id
AND t1.hostname = t2.hostname
AND t1.metric_item = t2.metric_item
where t1.metric_value - t2.metric_value > 2;
-- 心跳with查询sql
with max_item_value_tab as (
SELECT glib_id,
hostname,
metric_item,
metric_origin,
max(metric_value) as max_metric_value
FROM t_dhc_metric_20211014
WHERE metric_origin = 11
and TIMESTAMPDIFF(MINUTE, time, now()) < 60
and metric_item not in ('sdk_heatbeat', 'online')
GROUP BY glib_id,
hostname,
metric_item,
metric_origin
),
max_time_tab as (
select *
from t_dhc_metric_20211014
where metric_origin = 11
and TIMESTAMPDIFF(MINUTE, time, now()) < 60
and metric_item not in ('sdk_heatbeat', 'online')
),
max_data_tab as (
select a.glib_id,
a.hostname,
a.metric_item,
a.metric_origin,
a.max_metric_value,
b.metric_value,
b.time
from max_item_value_tab as a
left join
max_time_tab as b
on a.glib_id = b.glib_id and
a.hostname = b.hostname and
a.metric_item = b.metric_item and
a.metric_origin = b.metric_origin and
a.max_metric_value = b.metric_value
),
distinct_max_time_tab as (
select glib_id,
hostname,
metric_item,
metric_origin,
max_metric_value,
metric_value,
max(time) as time
from max_data_tab
group by glib_id,
hostname,
metric_item,
metric_origin,
max_metric_value,
metric_value
),
heartbeat_tab as (
select glib_id,
hostname,
metric_item,
metric_origin,
max_metric_value,
metric_value,
time
from distinct_max_time_tab
where unix_timestamp(time) - max_metric_value > 2
)
select *
from heartbeat_tab;
-- 心跳子查询sql
select glib_id,
hostname,
metric_item,
metric_origin,
max_metric_value,
metric_value,
time
from (
select glib_id,
hostname,
metric_item,
metric_origin,
max_metric_value,
metric_value,
max(time) as time
from (
select a.glib_id,
a.hostname,
a.metric_item,
a.metric_origin,
a.max_metric_value,
b.metric_value,
b.time
from (
SELECT glib_id,
hostname,
metric_item,
metric_origin,
max(metric_value) as max_metric_value
FROM t_dhc_metric_20211013
WHERE metric_origin = 11
and TIMESTAMPDIFF(MINUTE, time, now()) < 60
and metric_item not in ('sdk_heatbeat', 'online')
GROUP BY glib_id,
hostname,
metric_item,
metric_origin
) a
left join
(select *
from t_dhc_metric_20211013
where metric_origin = 11
and TIMESTAMPDIFF(MINUTE, time, now()) < 60
and metric_item not in ('sdk_heatbeat', 'online')) b
on a.glib_id = b.glib_id and
a.hostname = b.hostname and
a.metric_item = b.metric_item and
a.metric_origin = b.metric_origin and
a.max_metric_value = b.metric_value
) as b2
group by glib_id,
hostname,
metric_item,
metric_origin,
max_metric_value,
metric_value) as d
where unix_timestamp(time) - max_metric_value > 2;
|