select count(*) count,type
from snap_info
where time_stamp >= UNIX_TIMESTAMP(date_sub(NOW(), interval 1 MONTH))
group by type;
月:
date_sub(NOW(), INTERVAL 1 MONTH)
周:(7天)
date_sub(NOW(), INTERVAL 7 DAY)
日:
date_sub(NOW(), INTERVAL 1 DAY)
1.日期转时间戳
-- 转十位
> select UNIX_TIMESTAMP('2021-03-18 16:47:49') time;
+------------+
| time |
+------------+
| 1616057269 |
+------------+
-- 转十三位
SELECT REPLACE(unix_timestamp(current_timestamp(3)),'.','') timestamp;
+---------------+
| timestamp |
+---------------+
| 1616057528497 |
+---------------+
2.时间戳转日期:FROM_UNIXTIME(unix_timestamp) --unix_timestamp为时间戳
> select FROM_UNIXTIME('1571852885');
+-----------------------------+
| FROM_UNIXTIME('1571852885') |
+-----------------------------+
| 2019-10-24 01:48:05.000000 |
+-----------------------------+
当前日期 加减 一个月 一天 一分
date_add() 增加
date_sub()减少
month 月份
day 天数
minute 分钟
second 秒
网友评论