因为要做定时任务跑定时脚本,查询某时间的数据需要用sql函数来解决。
我把我用到的mysql函数和函数怎么使用的记录下来。
1.MySQL查询今天,本周,上周,本月,上个月,本季度,上季度,今年,去年的数据的sql
如果你的字段存的数据是2018-12-17 15:17:34这种格式或者是20181217这种都可以用:
今天:select * from `you_table` where to_days(`column`) = to_days(now());
昨天:select * from `you_table` where to_days(now()) – to_days(`column`) <= 1;
七天内:select * from `you_table` where date_sub(curdate(), INTERVAL 7 DAY) <= date(`column`);
30天内:select * from `you_table` where date_sub(curdate(), INTERVAL 30 DAY) <= date(`column`);
本月:select * from `you_table` where date_format(`column`, '%Y%m') = date_format(curdate() , '%Y%m');
上个月:select * from `you_table` where period_diff(date_format(now() , '%Y%m') , date_format(`column`, '%Y%m')) =1;
PERIOD_DIFF(P1,P2)
返回周期P1和P2之间的月数。 P1和P2格式为YYMM或YYYYMM。注意周期参数 P1 和 P2 都不是日期值。
mysql> SELECT PERIOD_DIFF(1812,201811);
#返回的是相差几个月,上面这个返回1
select * from `you_table` where period_diff(date_format(now() , '%Y%m') , date_format(`column`, '%Y%m')) =1;这句sql的意思就是距离当前一个月的数据。
TO_DAYS(date)
基于日期参数
date
,返回一个天数(自年份0开始的天数)。mysql> SELECT TO_DAYS(950501); +---------------------------------------------------------+ | TO_DAYS(950501) | +---------------------------------------------------------+ | 728779 | +---------------------------------------------------------+
SUBDATE(date,INTERVAL expr unit) 与 SUBDATE(expr,days)
当第二个参数采用 INTERVAL 格式时,
SUBDATE()
等同于DATE_SUB()
。要想详细了解 INTERVAL 单元参数,请参考DATE_ADD()
。mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY); +---------------------------------------------------------+ | DATE_SUB('1998-01-02', INTERVAL 31 DAY) | +---------------------------------------------------------+ | 1997-12-02 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY); +---------------------------------------------------------+ | SUBDATE('1998-01-02', INTERVAL 31 DAY) | +---------------------------------------------------------+ | 1997-12-02 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
DATE_FORMAT(date,format)
该函数会根据 format 字符串来格式化 date 值。
下表中列出了一些可用于 format 字符串的标识符。格式标识符第一个字符必须是
%
字符。
格式标识符 说明 %a
一星期中每天名称的缩写(Sun...Sat) %b
月份的缩写(Jan...Dec) %c
月份的数字表现形式(0...12) %D
带有英语后缀的一个月中的每一天的名称(0th、1st、2nd、3rd) %d
用数字形式表现的每月中的每一天(00...31) %e
用数字形式表现的每月中的每一天(0...31) %f
毫秒(000000...999999) %H
24时制显示的小时(00...23) %h
12时制显示的小时(01...12) %I
12时制显示的小时(01...12) %i
以数字形式表现的分钟数(00...59) %j
一年中的每一天(001...366) %k
24时制小时的另一种表现格式(0...23) %l
12时制小时的另一种表现格式(1...12) %M
用完整英文名称表示的月份(January...December) %m
用数字表现的月份(00...12) %p
上午(AM)或下午(PM) %r
12时制的时间值(hh:mm:ss,后跟 AM 或 PM) %S
秒(00...59) %s
秒(00...59) %T
24时制的小时(hh:mm:ss) %U
星期(00...53),其中星期天是每星期的开始日 %u
星期(00...53),其中星期一是每星期的开始日 %V
星期(01...53),其中星期天是每星期的开始日,和 %X
一起使用%v
星期(01...53),其中星期一是每星期的开始日,和 %x
一起使用%W
一星期中各日名称(Sunday...Saturday) %w
一星期中各日名称(0代表星期日,6代表星期六,以此类推) %X
某星期所处年份。其中,星期天是每星期的开始日,采用4位数字形式表现,和 %V
一起使用%x
某星期所处年份。其中,星期一是每星期的开始日,采用4位数字形式表现,和 %V
一起使用%Y
4位数字表示的年份 %y
2位数字表示的年份 %%
符号 %
的字面值%x(x为斜体) 字符x的字面值,x指以上未列出的任何字符 mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y'); +---------------------------------------------------------+ | DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') | +---------------------------------------------------------+ | Saturday October 1997 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00' -> '%H %k %I %r %T %S %w'); +---------------------------------------------------------+ | DATE_FORMAT('1997-10-04 22:23:00....... | +---------------------------------------------------------+ | 22 22 10 10:23:00 PM 22:23:00 00 6 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
2.MySQL查询当前时间2个月前,15天前,15天后,一个月后的数据的sql
下面这个可以查询准确的距离当前时间一个月后那天的数据,不是一个月内的数据
SELECT * FROM `you_table` WHERE (`column` = date_sub(CURDATE(),interval 2 month));//2个月前
SELECT * FROM `you_table` WHERE (`column` = date_sub(CURDATE(),interval 15 day));//15天前
SELECT * FROM `you_table` WHERE (`column` = date_add(CURDATE(),interval 15 day)) ;//15天后
SELECT * FROM `you_table` WHERE (`column` = date_add(CURDATE(),interval 1 month));//1个月后
DATE_ADD() 给日期添加指定的时间间隔 DATE_SUB() 从日期减去指定的时间间隔
3.按月查询每个月的收支
SELECT SUM(money) AS money,FROM_UNIXTIME(date/1000,'%Y-%m') AS months,CASE WHEN type='1' THEN '支出' WHEN type='2' THEN '收入' END AS '收支' FROM asset_flow GROUP BY months,type