SELECT
date_format(Time, '%Y-%m-%d %H:%i:%S') AS DATE,
-- 日期分組
date_format(Time, '%H') AS HOUR,
-- 小時分組
count(*) AS TOTAL -- 統計條數
FROM
timeseries
GROUP BY
date_format(Time, '%H')
ORDER BY
date_format(Time, '%Y-%m-%d %H:%i:%S'),
date_format(Time, '%Y-%m-%d %H:%i:%S') AS DATE,
-- 日期分組
date_format(Time, '%H') AS HOUR,
-- 小時分組
count(*) AS TOTAL -- 統計條數
FROM
timeseries
GROUP BY
date_format(Time, '%H')
ORDER BY
date_format(Time, '%Y-%m-%d %H:%i:%S'),
date_format(Time, '%H') ASC
时间戳形式:
SELECT
from_unixtime(start_time/1000, '%Y-%m-%d %H:%i:%S') AS DATE,
-- 日期分組
from_unixtime(start_time/1000, '%H') AS HOUR,
-- 小時分組
count(*) AS TOTAL -- 統計條數
FROM
dcsec_event_alarm_log
WHERE
start_time > 1520856880316 AND start_time < 1523977919047
GROUP BY
from_unixtime(start_time/1000 , '%H')
ORDER BY
from_unixtime(start_time/1000, '%Y-%m-%d %H:%i:%S'),
from_unixtime(start_time/1000, '%H') ASC