mysql按天,按周,按月,按年统计订单数

京漂大叔 2019-08-08 AM 2970℃ 0条

1、按天统计订单数据

SELECT COUNT(1) AS total, FROM_UNIXTIME(order_date,'%Y-%m-%d') AS days FROM order_list GROUP BY days DESC;

QQ截图20190808084757.png

2、按周统计订单数据

SELECT COUNT(1) AS total, WEEK(FROM_UNIXTIME(order_date)) AS weeks FROM order_list GROUP BY weeks DESC;

3、按月统计订单数据

SELECT COUNT(1) AS total, MONTH(FROM_UNIXTIME(order_date)) AS months FROM order_list GROUP BY months DESC;

4、按年统计订单数据

SELECT COUNT(1) AS total, YEAR(FROM_UNIXTIME(order_date)) AS years FROM order_list GROUP BY years DESC;

备注:
DATE_ADD(createTime,INTERVAL 1 DAY) 得到指定日期后一天的日期/把1改为任意数字就可以得到后N天的日期
DATE_SUB(createTime,INTERVAL 1 DAY) 得到指定日期前一天的日期/把1改为任意数字就可以得到前N天的日期
1.日期转时间戳
UNIX_TIMESTAMP(unix_timestamp)--unix_timestamp为时间戳
2.时间戳转日期:
FROM_UNIXTIME(unix_timestamp) --unix_timestamp为时间戳

标签: mysql, 时间戳转换

非特殊说明,文章均为原创。

评论啦~