mysql 统计按天、星期、按月数据的各种 sql 语句 (转录)

最新推荐文章于 2023-06-29 15:00:51 发布

weixin_33701617

最新推荐文章于 2023-06-29 15:00:51 发布

阅读量319

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/tristana/p/6552659.html

版权

文章主要是作为知识整理，内容略有修改，方便以后查阅，内容转摘至陈宇衡的个人博客，欢迎前去围观。

作为演示效果，先创建一个测试表，在插入两条数据（注：时间为 datetime 类型，unix 时间戳需要转换）

1. 创建表：

    create table if not exists t
    (
        id int,
        addTime datetime default '0000-00-00 00:00:00'
    )

2. 添加两条初始数据：

    insert t values(1, '2017-03-12 02:00:00');
    insert t values(2, '2017-03-22 02:00:00');

当天或当日插入的数据：

传统对比判断：SELECT count(*) FROM t WHERE DATE_FORMAT(addTime, '%Y-%m-%d') = date_format(now(),'%Y-%m-%d'));
第一点的简写：SELECT * FROM t WHERE addTime >= date_format(NOW(), '%Y-%m-%d');
利用函数判断：SELECT * FROM t WHERE DATEDIFF(addTime, NOW()) =0;（推荐此方法）

注：返回当天时间的mysql函数有
CURDATE()、CURRENT_DATE()、CURRENT_DATE 返回格式 0000-00-00 （年-月-日）,
NOW() 返回格式 0000-00-00 00:00:00 （年-月-日时：分：秒）几种；

当周的相关数据写法：

获取今天是一周第几天或星期几：SELECT WEEKDAY(now()); (返回的是数字：0为周一，6为周日)
获取本周的第一天日期：SELECT DATE_SUB(now(), INTERVAL WEEKDAY(now()) day);(或SELECT DATE_ADD(now(),INTERVAL -WEEKDAY(now()) day);或SELECT CURDATE() – WEEKDAY(CURDATE());)
再写一个上周的第一天至现在的数据：（以表t为数据表）
SELECT * FROM t WHERE addTime >= date_format(date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK), ‘%Y-%m-%d’);是不是有些感觉了！
注：若你是以时间戳保存的时间字段，那么请用from_unixtime()函数转换为时间格式，如：from_unixtime($timestamp)
INTERVAL的用户说明：
1、当函数使用时，即INTERVAL(),为比较函数，如：INTERVAL(10,1,3,5,7); 结果为4；
原理：10为被比较数，后面1,3,5,7为比较数，将后面四个依次与10比较，看后面数字组有多少个少于10，则返回其个数。前提是后面数字组为从小到大排列，否则返回结果0。
2、当关键词使用时，表示为设置时间间隔，常用在date_add()与date_sub()函数里，如：INTERVAL 1 DAY，解释为将时间间隔设置为1天。
弄清楚了上面几个重要的日期运算函数，下面再来一个混合的日期运算。
3、上个月的第一天时间：SELECT DATE_SUB(DATE_SUB(NOW(), INTERVAL DAYOFMONTH(NOW()) -1 DAY), INTERVAL 1 MONTH); 是不是一目了然了！

当月的相关数据写法：

今天是当月的第几天：SELECT DAYOFMONTH(NOW());
获取当月的第一天时间：SELECT DATE_SUB(NOW(), INTERVAL DAYOFMONTH(NOW()) –1 DAY);
日期运算函数，句型：date_add(date,INTERVAL expr type)和date_sub(date,INTERVAL expr type)，date为一个datetime或date值，可当作起始时间，expr为一个表达式，用来指定增加或减少的时间间隔数目，可为一个字符串–若为负值，则在表达式前加个"-"符号。type为关键词，它表示了表达式被解释的方式，通常用年(year)、月(month)、日(day)、周(week)等。

Mysql中将日期转为时间戳

前三点的方法，讲的都是将日期转为相应时间常量，并没有提到时间戳与时间的转换，这里把它写出来，
1、时间戳转日期，方法是select from_unixtime(1336542121);
2、日期转时间戳，方法是：select unix_timestamp('2013-04-08');
例如按天统计新增记录：select count(*) as count, from_unixtime(addTime, '%Y-%m-%d') as datetime from t;
结合前面例子，对时间戳进行相应的转换就可以了。

转载于:https://www.cnblogs.com/tristana/p/6552659.html