一、date_format () 、date_sub()、YEERWEEK()、WEEKDAY()
1) “date_format()”:格式化日期格式,“date_format(时间戳,时间格式)”
2) “date_sub()”:从日期减去指定的时间间隔;函数形式DATE_SUB(date,INTERVAL expr type),date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔,时间间隔参数非常全面,常用的为 年月日时分秒;
举例如减天数如“date_sub(时间,INTERVAL 1 DAY)”,减月份“date_sub(时间,INTERVAL 1 MONTH)”
3) YEERWEEK()函数返回指定的日期是哪一年的哪个星期,函数形式为 YEARWEEK(date[,mode]) ,其中date的格式一般为‘年-月-日’,mode为1代表一个星期从星期一开始;
4)WEEKDAY函数接受1
个参数,即DATE
或DATETIME
值,函数形式为WEEKDAY(date);返回一个整数,范围从0
到6
,表示星期一到星期日,即星期一为0,星期二为1,星期日为6;
二、一些常用的时间如下:
本周:YEARWEEK(date_format(t.date,'%Y-%m-%d'),1) = YEARWEEK(now(),1) # t.date所在日期为当前时间所在星期,星期一为一周的第一天,其中t.date使用date_format函数将时间戳的格式统一为‘年-月-日’
上周:YEARWEEK(date_format(t.date,'%Y-%m-%d'),1) = YEARWEEK(now(),1)-1 # 与上一条相同,不同之处在于等式右边为 当前时间所在星期再减一星期,为上星期
本月:date_format(t.date,'%Y-%m')=date_format(curdate(),'%Y-%m') # t.date所在月份为当前日期所在月份
上月:date_format(t.date,'%Y-%m')=date_format(date_sub(NOW(), interval 1 MONTH),'%Y-%m') # t.date所在月份为当前时间所在月份的上一个月
本年:date_format(t.date,'%Y')=date_format(curdate(),'%Y') # t.date所在年份为当前日期所在月份
近12个月:t.date>=DATE_SUB(CURDATE(), INTERVAL 12 MONTH) # t.date大于当前日期往前推12个月
当前时间:select NOW(); #结果形如 2020-12-08 14:21:19
当前日期:select CURDATE(); #结果形如 2020-12-08
本周一:select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) DAY) # 查询结果形如 2020-12-07,为当前日期所在星期的星期一
本周日:select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 6 DAY) # 查询结果形如 2020-12-13,为当前日期所在星期的星期日
上周一:select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 7 DAY) # 查询结果形如 2020-11-30,为当前日期所在星期的上一个星期的星期一
上周日:select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY) # 查询结果形如 2020-11-30,为当前日期所在星期的上一个星期的星期日
上个月底(上个月最后一天):SELECT date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now()) day),interval 0 month) #将当前日期减去当前天数,月份不减2
三、一些常用的用法如下:
1、select DATE_FORMAT(CURDATE(),'%Y-%m') #查询当前时间,格式显示为“年-月”,查询结果形如 2020-12
2、SELECT DATE_SUB(CURDATE(), INTERVAL 12 MONTH) #当前日期往前推12个月,查询结果形如2019-12-08
3、SELECT YEARWEEK(NOW(),1) #查询当前时间为哪一年的第几个星期,星期一为一周的第一天(mode=1) ,查询结果形如202050
4、sum(case WEEKDAY(t.date) when 0 then t.consumed else 0 end ) w1 # 当t.date为0时,将t.consumed 加和,别名为w1,即为星期一的t.consumed
5、sum(case t.date when CONCAT(date_format(t.date,'%Y-%m'),'-01') then t.consumed else 0 end ) m1 #当t.date为 t.date所在月份如2020-11-01时,将t.consumed 加和,别名为m1,即为“YYYY-MM-01”(某月份1号)的t.consumed加和