MySQL查询日期和时间¶
查询当前日期和时间:
select now(); --日期和时间
select curdate(); --日期
select curtime(); --24小时格式的时间
找出两个时间之间的记录:
select * from events where event_date between '2018-01-01' and '2018-01-31';
select * from events where event_date between '2018-01-01 12:00:00' and '2018-01-01 23:30:00';
统计¶
统计一定时间范围内按照天的告警计数,这里使用
date()
可以按天合并统计:
SELECT count(alert) as 告警数量, date(gmt_create) as 日期 FROM notifier.notifier_alert_statistics
where gmt_create between '2023-07-01 00:00:00' and '2023-07-31 23:59:59'
GROUP BY date( gmt_create ) order by date(gmt_create);
输出案例:
+--------------+------------+
| 告警数量 | 日期 |
+--------------+------------+
| 1 | 2023-07-01 |
| 6 | 2023-07-03 |
| 2 | 2023-07-05 |
| 7 | 2023-07-06 |
......
| 9 | 2023-07-27 |
| 3 | 2023-07-28 |
| 1 | 2023-07-29 |
+--------------+------------+
25 rows in set (0.00 sec)
如果要按照小时进行统计也类似,只不过需要注意小时数据排序时默认是ASCII排序,需要转换成数值排序:
SELECT count(alert) as 告警数量, hour(gmt_create) as 小时 FROM notifier.notifier_alert_statistics
where gmt_create between '2023-07-01 00:00:00' and '2023-07-31 23:59:59'
GROUP BY hour(gmt_create) order by cast(hour(gmt_create) as unsigned);
输出案例:
+--------------+--------+
| 告警数量 | 小时 |
+--------------+--------+
| 3 | 0 |
| 3 | 1 |
| 2 | 2 |
......
| 4 | 23 |
+--------------+--------+
24 rows in set (0.00 sec)
参考¶
SQL order string as number 数据查询按字段排讯默认是ASCII顺序,有时候我们需要按照数字大小排序,这里提供了很好的案例