mysql 查询当天、本周，本月，上一个月的数据

by · 2016年02月26日 · 1982 Words · ~4min reading time | Improve on


原文链接： mysql 查询当天、本周，本月，上一个月的数据

format格式说明：

%M 月名字(January～December)
%W 星期名字(Sunday～Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）

  • %Y 年, 数字, 4 位
    %y 年, 数字, 2 位
    %a 缩写的星期名字(Sun～Sat)
    %b 缩写的月份名字(Jan～Dec)
    %c 月, 数字(1～12)
  • %d 月份中的天数, 数字(00～31)
    %e 月份中的天数, 数字(0～31)
  • %m 月, 数字(01～12)
    %j 一年中的天数(001～366)
  • %H 小时(00～23)
    %k 小时(0～23)
    %h 小时(01～12)
    %I 小时(01～12)
    %l 小时(1～12)
  • %i 分钟, 数字(00～59)
    %r 时间,12 小时(hh:mm:ss [AP]M)
  • %T 时间,24 小时(hh:mm:ss)
    %S 秒(00～59)

  • %s 秒(00～59)
    %p AM或PM
    %w 一个星期中的天数(0=Sunday ～6=Saturday ）
    %U 星期(0～52), 这里星期天是星期的第一天
    %u 星期(0～52), 这里星期一是星期的第一天
    %% 一个文字%

    mysql中的 DATE_FORMAT()

    按周
    select DATE_FORMAT(create_time,'%Y%u') weeks,count(caseid) count from tc_case group by weeks;

按月
select DATE_FORMAT(create_time,'%Y%m') months,count(caseid) count from tc_case group by months;

按天
select DATE_FORMAT(create_time,'%Y%m%d') days,count(caseid) count from tc_case group by days;
按小时
select DATE_FORMAT(create_time,'%Y%m%d%H') hours,count(caseid) count from tc_case group by hours;

按年汇总，统计：
select sum(mymoney) as totalmoney, count() as sheets from mytable group by date_format(col, '%Y');
按月汇总，统计：
select sum(mymoney) as totalmoney, count() as sheets from mytable group by date_format(col, '%Y-%m');
按季度汇总，统计：
select sum(mymoney) as totalmoney,count() as sheets from mytable group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));
select sum(mymoney) as totalmoney,count() as sheets from mytable group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));
按小时：
select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by date_format(col, '%Y-%m-%d %H ');
查询 本年度的数据:
SELECT * FROM mytable WHERE year(FROM_UNIXTIME(my_time)) = year(curdate())
查询数据附带季度数:
SELECT id, quarter(FROM_UNIXTIME(my_time)) FROM mytable;
查询 本季度的数据:
SELECT * FROM mytable WHERE quarter(FROM_UNIXTIME(my_time)) = quarter(curdate());
本月统计:
select * from mytable where month(my_time1) = month(curdate()) and year(my_time2) = year(curdate())
本周统计:
select * from mytable where month(my_time1) = month(curdate()) and week(my_time2) = week(curdate())
N天内记录:
WHERE TO_DAYS(NOW())-TO_DAYS(时间字段)<=N

mysql 中 unix_timestamp和from_unixtime 时间戳函数

  1. unix_timestamp 将时间转化为时间戳。
    将 date 类型数据转换成 timestamp 形式整数

当没传时间参数则取当前时间的时间戳
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1361586358 |
+------------------+
mysql>select unix_timestamp('2013-01-01 10:10:10');
+---------------------------------------+
| unix_timestamp('2013-01-01 10:10:10') |
+---------------------------------------+
|                            1357006210 |
+---------------------------------------+
1 row in set (0.00 sec)

 

  1. from_unixtime 将 timestamp (int64) 转化为 date 类型
    如果format为空默认会使用%Y-%m-%d %H:%i:%s的格式

mysql>  select from_unixtime(1355272360);
+---------------------------+
| from_unixtime(1355272360) |
+---------------------------+
| 2012-12-12 08:32:40       |
+---------------------------+
1 row in set (0.00 sec)

当然也可以指定输出的时间格式：

mysql>  select from_unixtime(1355272360,'%Y%m%d');
+------------------------------------+
| from_unixtime(1355272360,'%Y%m%d') |
+------------------------------------+
| 20121212                           |
+------------------------------------+

 

3.关于mysql 时间戳的限制

目前timestamp 所能表示的范围在 1970  -  2038之间 。

超过这个范围 得到的时间将会溢出 得到的时间是null.

mysql>  select from_unixtime(0);
+---------------------+
| from_unixtime(0)    |
+---------------------+
| 1970-01-01 08:00:00 |
+---------------------+

mysql> select from_unixtime(2147483647);
+---------------------------+
| from_unixtime(2147483647) |
+---------------------------+
| 2038-01-19 11:14:07       |
+---------------------------+
1 row in set (0.00 sec)

 

SET @b_date='2018-07-01';
SET @e_date=date_add(curdate(),interval -1 day); -- 获取昨天日期
SET @days= timestampdiff(days,'2018-11-1',curdate()); -- 获取当天到2018-11-1日相差的天数
SET @days= day(curdate()); -- 获取当天到月初的天数,其实就是今天是本月的几号 20

计算两个日期的时间差(天数)

timestampdiff(day,'2018-11-1','2018-11-2'); -- 1

当月第一天(日期) 12/01/2018

select DATE_ADD(curdate(),interval -day(curdate())+1 day); --获取本月第一天 2018-12-01
select DATE_SUB(curdate(),interval day(curdate())-1 day);

当月最后一天(日期) 12/31/2018

select last_day(now()); -- 获取本月最后一天的日志 2018-12-31

上月第一天(日期) 11/30/2018

select date_add(curdate()-day(curdate())+1,interval -1 month);

上月最后一天(日期) 11/30/2018

select last_day(date_sub(now(),interval 1 month));

下月第一天(日期) 2019-01-01

select date_add(curdate()-day(curdate())+1,interval 1 month); -- 获取下个月的第一天 2019-01-01

下月最后一天(日期) 1/31/2019

select last_day(date_sub(now(),interval -1 month));

本月(天数) 31

select day(last_day(curdate()));

上月今天的当前(日期) 11/20/2018

select date_sub(curdate(), interval 1 month);

上月今天的当前时间（时间戳） 1542696803

select unix_timestamp(date_sub(now(),interval 1 month));

获取当前时间与上个月之间的(天数) 30

select datediff(curdate(), date_sub(curdate(), interval 1 month));

Mysql时间加减函数为date_add（）、date_sub（）
定义和用法
DATE_ADD() 函数向日期添加指定的时间间隔。
DATE_SUB() 函数向日期减少指定的时间间隔。
语法
DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
date 参数是合法的日期表达式。
expr参数是您希望添加的时间间隔。
type 参数可以是下列值：
Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

  1. MySQL 为日期增加一个时间间隔：date_add()
    set @dt = now();
    select date_add(@dt, interval 1 day); - 加1天
    select date_add(@dt, interval 1 hour); -加1小时
    select date_add(@dt, interval 1 minute); - 加1分钟
    select date_add(@dt, interval 1 second); -加1秒
    select date_add(@dt, interval 1 microsecond);-加1毫秒
    select date_add(@dt, interval 1 week);-加1周
    select date_add(@dt, interval 1 month);-加1月
    select date_add(@dt, interval 1 quarter);-加1季
    select date_add(@dt, interval 1 year);-加1年
    例：更新某个时间，每个时间加上一个星期
    UPDATE comment c set c.time = DATE_ADD(c.time, INTERVAL 7 DAY) ;

  2. MySQL 为日期减去一个时间间隔：date_sub()，格式同date_add（）类似
    例子：更新某个时间，使每个时间减少一个月
    UPDATE comment c set c.time = DATE_SUB(c.time, INTERVAL 1 MONTH)

今天
select * from 表名 where to_days(时间字段名) = to_days(now());
昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
近7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
上一月
SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1
查询本季度数据
select * from ht_invoice_information where QUARTER(create_date)=QUARTER(now());
查询上季度数据
select * from ht_invoice_information where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
查询本年数据
select * from ht_invoice_information where YEAR(create_date)=YEAR(NOW());
查询上年数据
select * from ht_invoice_information where year(create_date)=year(date_sub(now(),interval 1 year));
查询当前这周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
查询上周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;
查询上个月的数据

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
select * from user where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ;
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())
select * from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now())
select * from user where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now())
select * from user where pudate between 上月最后一天 and 下月第一天

查询当前月份的数据
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')
查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

Tags:

`