[大数据]关于sql中处理日期的相关函数

注：本文仅仅是我工作用常用到sql，只是冰山一角，后续会持续更新，逐渐补全，先总结出来一起学习共同进步

热门SQL

处理时间的函数

先了解几个常用的获取当前时间的函数输出

#根据你想要的时间格式选择对应函数
select curdate(); -- 2021-11-11;
select CURRENT_DATE(); -- 2021-11-11;
select CURRENT_TIME(); -- 17:13:58;
select CURRENT_TIMESTAMP(); -- 2021-11-11 17:13:58;
select NOW(); -- 2021-11-11 17:13:58;
select sysdate();  -- 2021-11-11 17:13:58; 一般情况下很少用到。需要时分秒时不建议使用
SELECT DATE(SYSDATE());--格式为年月日 上面的都可以放到里面
-- sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。
select now(), sleep(3), now();-- 2021-11-25 12:40:38	0	2021-11-25 12:40:38
select sysdate(), sleep(3), sysdate(); -- 2021-11-25 12:40:41	0	2021-11-25 12:40:44
#MySQL 获得当前时间戳函数：current_timestamp, current_timestamp()
select current_timestamp,sleep(3), current_timestamp();-- 2021-11-25 12:43:23	0	2021-11-25 12:43:23

select utc_timestamp(), utc_date(), utc_time(), now(),CURRENT_TIME;-- 2021-11-26 19:04:54	2021-11-26	19:04:54	2021-11-27 03:04:54	03:04:54  相差8小时
select utc_timestamp(),DATE_FORMAT(utc_timestamp(),'%Y-%m-%d %I:%i:%s'),now();-- 2021-11-26 19:16:06	2021-11-26 07:16:06	2021-11-27 03:16:06
#因为我国位于东八时区，所以本地时间 = UTC 时间 + 8 小时。UTC 时间在业务涉及多个国家和地区的时候，非常有用。
#注：返回当前UTC日期和时间作为'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式的一个值，根据函数是否用在字符串或数字语境中。
#一个使用技巧 注意结合使用
SELECT CURDATE() + 0; -- 20211125  直接得到纯数字
SELECT '2021-11-26 18:45:28' + 0; -- 2021   字符串只能从头截取到非数字部分的数值

求天数：两段日期之间的天数差

#方法一： to_days()函数  返回日期和年份0之间的天数（日期“0000-00-00”）注：只能用于公历中的日期
#语法：TO_DAYS(date)  参数必须项，给定的日期。
SELECT NOW();-- 2021-11-25 10:15:44
SELECT TO_DAYS('2021-11-25'); -- 738484
SELECT TO_DAYS( NOW()); -- 738484
-- 注意：MySQL将日期中的两位数年份值转换为四位数形式 。 例如， '2021-11-25'、 '21-11-25'和211125被看作是相同的日期：
#TO_DAYS()不适用于公历日历（1582）出现之前的值，因为它不考虑日历更改时丢失的日期。在1582年之前的日期（可#能在其他地区的其他年份），此功能的结果不可靠。
SELECT TO_DAYS(20211125); -- 738484  
SELECT TO_DAYS(211125); -- 738484
-- 注意：月份跟天数必须有才会算出天数 否则为空
SELECT TO_DAYS('0000-00-00'); -- NULL 
SELECT TO_DAYS('0000-00-01'); -- NULL
SELECT TO_DAYS('0000-01-01'); -- 1
SELECT TO_DAYS('0001-00-00'); -- NULL
SELECT TO_DAYS('0001-01-00'); -- NULL
SELECT TO_DAYS('0001-01-01'); -- 366
#使用如下：
SELECT TO_DAYS('2021-11-25') -TO_DAYS('2021-11-25')  ;-- 0
SELECT TO_DAYS(now()) -TO_DAYS('2021-11-25')  ; -- 0 
SELECT TO_DAYS(now()) -TO_DAYS('2021-11-24')  ; -- 1
SELECT TO_DAYS(now()) -TO_DAYS(20211124)  ; -- 1
SELECT TO_DAYS(now()) -TO_DAYS(211124)  ; -- 1

#还可以这样玩：但是不建议这么玩 除非适合这么用的时候，了解即可 一种思路
#有一说一，这个更强大毕竟精确到天数 日期范围的事它都能处理 这块不插入过多练习，获取更多练习看下面详细练习
-- 把日期转换为一个数字
select * from 表 where to_days（'字段名'）＝'2021-11-25'
-- 如果要查询当前表中今天、昨天的数据
select * from admin where to_days(now())-to_days(create_time)=0; -- 今天的
select * from admin where to_days(now())-to_days(create_time)=1; -- 昨天的 依次类推
#联合使用求某个时间段的天数 如昨天数据还可以这么求
select * from admin where to_days(now())-to_days(create_time)<2 -- 今天及昨天数据
select * from admin where to_days(now())-to_days(create_time)>=1  -- 昨天之前的全部数据 包括昨天
select * from admin where to_days(now())-to_days(create_time)<2
and to_days(now())-to_days(create_time)>=1  -- 求个交集获取到昨天独有数据

#获取两段时间天数还可以如下：
SELECT DATEDIFF(now(),'2018-11-13')
SELECT day(now())-DAY('2018-11-13') -- 这个可不行会忽略年

这块扩展 注意： to_days()函数此函数与FROM_DAYS()函数相反 。

FROM_DAYS()函数使用方法及示例

#FROM_DAYS()函数 从数字日期值返回一个日期。仅与公历中的日期一起使用。
#语法：FROM_DAYS(number) 参数需要，要转换为日期的数字日
SELECT FROM_DAYS(now()); -- 0000-00-00
SELECT FROM_DAYS("2021-11-25"); -- 0005-07-14
SELECT FROM_DAYS(2021); -- 0005-07-14
# 正规使用如下：
SELECT FROM_DAYS(738484); -- 2021-11-25 

#指定日期1秒后：
SELECT DATE_ADD('2021-11-25 23:59:59', INTERVAL 1 SECOND);-- 2021-11-26 00:00:00
select DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND);-- 2011-01-01 00:00:00
#指定日期1天后：
select DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY);-- 2011-01-01 23:59:59
#指定日期减去10小时：
SELECT DATE_ADD('2011-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);-- 2010-12-30 14:00:00
#指定日期的一个月前：
SELECT DATE_SUB('2011-01-02', INTERVAL 31 DAY);-- 2010-12-02
#指定日期的前一天：
SELECT date_add('2011-01-01', INTERVAL -1 DAY);-- 2010-12-31
#将日期时间转换成指定格式：   DATE_FORMAT(date,format)
#根据format 字符串安排date 值的格式。所有其它字符都被复制到结果中，无需作出解释。注意，'%'字符要求在格式#指定符之前。月份和日期说明符的范围从零开始，原因是 MySQL允许存储诸如 '2004-00-00'的不完全日期。
SELECT FROM_UNIXTIME( 1290996580 ) -- 2010-11-29 10:09:40
SELECT DATE_FORMAT( FROM_UNIXTIME( 1290996580 ), '%Y-%m-%d %H:%i:%s' );-- 2010-11-29 10:09:40
SELECT DATE_FORMAT( FROM_UNIXTIME( 1290996580 ) , '%Y-%m-%d' );-- 2010-11-29
-- 下面两个函数
 FROM_UNIXTIME(unix_timestamp) ;
 FROM_UNIXTIME(unix_timestamp,format);
 #语法： VARCHAR FROM_UNIXTIME(BIGINT unixtime[, VARCHAR format])
 #参数unixtime为长整型，是以秒为单位的时间戳。
 #参数format可选，为日期格式，默认格式为yyyy-MM-dd HH:mm:ss，表示返回VARCHAR类型的符合指定格式的日期，如果有参数为null或解析错误，则返回null。
 #返回值为VARCHAR类型的日期值，默认日期格式：yyyy-MM-dd HH:mm:ss，若指定日期格式按指定格式输出任一输入参数是NULL，返回NULL。
-- 返回'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS 格式值的unix_timestamp参数表示，具体格式取决于该函数是否用在字符串中或是数字语境中。
-- 若format 已经给出，则结果的格式是根据format 字符串而定。 format 可以包含同DATE_FORMAT() 函数输入项列表中相同的说明符。

字符串转换为日期格式

可以看到，str_to_date(str,format) 转换函数，可以把一些杂乱无章的字符串转换为日期格式。

select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');-- 20080808222301 
select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09
select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09
select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09
select str_to_date('08,2008,09', '%m,%Y,%d'); -- 2008-08-09
select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

将时间、日期转换为字符串 日期转换函数、时间转换函数

（日期/时间转换为字符串）函数：date_format(date,format), time_format(time,format)

能够把一个日期/时间转换成各种各样的字符串格式。它是 （字符串转换为日期）str_to_date(str,format) 函数的 一个逆转换。

date_format，一种是time_format。
先说一下这两者，time_format 只支持时分秒的格式化，对年月日时不起作用的，而 date_format对于年月日时分秒或者两者分开都起作用。  所以使用date_format的即可知道有这个函数就行。
语法：select time_format(time, format_mask)  time 必须项。格式化的时间  format_mask 必须项。要使用的格式。可以是以下之一或组合：
SELECT now();                -- 2021-11-25 17:14:16
SELECT TIME_FORMAT(now(), "%H %i %s");  -- 17 14 16
SELECT DATE_FORMAT(now(), "%H %i %s");  -- 17 14 16

time_format 规定日时间的输出格式。

format 规定日期/时间的输出格式。

MySQL （日期、天数）转换函数：to_days(date), from_days(days) 上面有不再赘述

TO_DAYS(date)给出一个日期  `date`，返回一个天数(从 0 年开始的天数)：
FROM_DAYS(N)给出一个天数  `N`，返回一个  `DATE` 值：

MySQL （时间、秒）转换函数：time_to_sec(time), sec_to_time(seconds)

time_to_sec 的作用 将指定时间转换为秒
语法：TIME_TO_SEC(time)
time：传入时间，如果传入了日期部分，也不会管，只将时间部分转换成秒
重点：是指将传入的时间转换成距离当天00:00:00的秒数，00:00:00为基数，等于 0 秒

---------时间转秒---------
select now(); -- 2021-11-25 17:32:39
select TIME_TO_SEC(now()); -- 63159
select TIME_TO_SEC('2021-11-25 17:32:39'); --  63159
select TIME_TO_SEC('17:32:39'); --  63159
select TIME_TO_SEC('00:00:00'); --  0
select TIME_TO_SEC('00:01:00'); --  60
#验证：select 17*3600+32*60+39 -- 63159
---------秒转时间---------
select sec_to_time(63159); -- 17:32:39

MySQL 拼凑日期、时间函数：makdedate(year,dayofyear), maketime(hour,minute,second)

select makedate(2021,31); -- 2021-01-31
select makedate(2021,32); -- 2021-02-01
select makedate(2021,36); -- 2021-02-05

select maketime(12,15,30); -- 12:15:30
select maketime( 17,32,39); -- 17:32:39

MySQL （Unix 时间戳、日期）转换函数

-- unix_timestamp(),
-- unix_timestamp(date),
-- from_unixtime(unix_timestamp),
-- from_unixtime(unix_timestamp,format)
-- 这个非常有用，做自动化的同学都能看到，当然如果直接linux中比对，使用shell 中的date函数也可以。

select unix_timestamp(); -- 1218169790
select unix_timestamp('2008-08-08'); -- 1218124800
select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800
 
select from_unixtime(1218169790); -- 2008-08-08 12:29:50
select from_unixtime(1218124800); -- 2008-08-08 00:00:00
select from_unixtime(1218169800); -- 2008-08-08 12:30:00

select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'

 

MySQL 日期时间计算函数

-- month 月份 minute 分钟 second 秒 hour 小时 week 周 quarter 刻 year 年
select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) -- 1997-12-30 22:58:59
select date_sub('2021-11-25 17:32:39', interval '1 1:1:1' day_second) -- 2021-11-24 16:31:38

#MySQL 为日期增加一个时间间隔：date_add()
set @dt = now();  
select date_add(@dt, interval 1 day); -- 2021-11-26 19:43:19
select date_add(@dt, interval 1 hour); -- 2021-11-25 20:43:19 
select date_add(@dt, interval 1 minute);-- 2021-11-25 19:44:19   
select date_add(@dt, interval 1 second); -- 2021-11-25 19:43:20 
select date_add(@dt, interval 1 microsecond);-- 2021-11-25 19:43:19.000001  
select date_add(@dt, interval 1 week);-- 2021-12-02 19:43:19  
select date_add(@dt, interval 1 month);-- 2021-12-25 19:43:19 
select date_add(@dt, interval 1 quarter);  -- 2022-02-25 19:43:19
select date_add(@dt, interval 1 year);-- 2022-11-25 19:43:19
select date_add(@dt, interval -1 day); -- 2021-11-24 19:43:19

#MySQL adddate(), addtime()函数，可以用 date_add() 来替代。下面是 date_add() 实现 addtime() 功能示例：
 set @dt = '2008-08-09 12:12:33';
 select date_add(@dt, interval '01:15:30' hour_second);--  2008-08-09 13:28:03
 select date_add(@dt, interval '1 01:15:30' day_second);-- 2008-08-10 13:28:03 
 
#MySQL 为日期减去一个时间间隔：date_sub()
select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);-- 1997-12-30 22:58:59

#对于某个日期加上n分钟n秒
date_add('2018-06-26 23:59:59',INTERVAL '1:1' MINUTE_SECOND)--  2018-06-27 00:01:00 
#对于某个日期加上n小时n分钟n秒
select date_add('2018-06-26 23:59:59',INTERVAL '1:1:1' HOUR_SECOND)-- 2018-06-27 01:01:00 
#对某个日期加上n小时n分钟
select date_add('2018-06-26 23:59:59',INTERVAL '1:1' HOUR_MINUTE);-- 2018-06-27 01:00:59  
#对某个日期加上几天几小时几分钟几秒钟
select date_add('2018-06-26 23:59:59',INTERVAL '2 2:1:1' DAY_SECOND);-- 2018-06-29 02:01:00  

#ADDTIME函数：时间加法运算 将 expr2添加至expr 然后返回结果。 expr 是一个时间或时间日期表达式，而expr2 是一个时间表达式。
SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002'); -- 2008-01-02 01:01:01.000001
SELECT ADDTIME('2007-12-31 23:59:58', '1 1:1:1'); -- 2008-01-02 01:00:59
SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');  -- 03:00:01.999997
SELECT ADDTIME('01:00:01', '02:00:01');  -- 03:00:02
select ADDTIME('2015-01-31 16:27:08',1);-- 2015-01-31 16:27:09
select ADDTIME('2015-01-31 16:27:08',59);  -- 2015-01-31 16:28:07
select ADDTIME('10:30:59','5:10:37') -- 15:41:36

 
 -- date_add函数的type属性可以动态么？  下面这么写会报错 
select num,DATE_ADD(now(),INTERVAL 2 (CASE num WHEN 1 THEN DAY WHEN 2 THEN MONTH WHEN 3 THEN YEAR ELSE YEAR END)) 
-- 换个思路修改为：
select num , CASE num 
    WHEN 1 THEN  DATE_ADD(now(),INTERVAL 2 DAY)
    WHEN 2 THEN  DATE_ADD(now(),INTERVAL 2 MONTH)
    WHEN 3 THEN  DATE_ADD(now(),INTERVAL 2 YEAR)
    ELSE  DATE_ADD(now(),INTERVAL 2 YEAR)
END from admin

   

日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)

前面操作日期得到天数 后面操作时间的 且参数都必须是相同的类型，不然结果为空

而TIMESTAMPDIFF比较有意思 能设置返回值属性 并且是第二个日期减去第一个日期

select datediff('2008-08-01', '2008-08-08'); -- -7
select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
select timediff('2008-08-09 08:08:08', '2008-08-08 00:00:00'); -- 32:08:08
select timediff('08:08:08', '00:00:00'); -- 08:08:08

#datediff(date1,date2) 函数 值为date1- date2 得到一个天数整数差 时分秒会忽略掉
SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1,-- 1
 DATEDIFF('2017-11-30','2017-12-15') AS col2, -- -15
 DATEDIFF('2017-11-30 08:08:08','2017-12-15') AS col3, -- -15
 DATEDIFF('2017-11-30 08:08:08','2017-12-15 08:08:06') AS col4; -- -15
 #timestampdiff函数日期或日期时间表达式之间的整数差。
 #语法：TIMESTAMPDIFF(interval，datetime1，datetime2)，比较的单位interval可以为以下数值
 #FRAC_SECOND。表示间隔是毫秒     SECOND。秒      MINUTE。分钟     HOUR。小时   DAY。天
 #WEEK。星期                    MONTH。月       QUARTER。季度    YEAR。年
 #请注意：DATEDIFF，TIMESTAMPDIFF对日期差值的计算方式刚好是相反的。
 #计算方式为datetime2-datetime1的差值。
 
 #留意跟时分秒挂钩 会影响结果  少一秒你的结果就得减去1
 -- 根据需求 如果可以建议去掉时分秒
select TIMESTAMPDIFF(DAY,'2018-07-01 23:59:59','2018-07-04 23:59:59'); -- 3
select TIMESTAMPDIFF(DAY,'2018-07-01 23:59:59','2018-07-04 23:59:58'); -- 2
select TIMESTAMPDIFF(second,'2018-07-04 23:59:59','2018-07-04 23:59:58'); -- -1
select TIMESTAMPDIFF(second,'2018-07-04 23:59:58','2018-07-04 23:59:59'); -- 1
 
select TIMESTAMPDIFF(MONTH,'2018-07-01 00:00:00','2018-07-31 23:59:59'); -- 0
select TIMESTAMPDIFF(MONTH,'2018-07-01 00:00:01','2018-08-01 00:00:00'); -- 0
select TIMESTAMPDIFF(MONTH,'2018-07-01 23:59:59','2018-08-01 23:59:58'); -- 0
select TIMESTAMPDIFF(MONTH,'2018-07-01 00:00:00','2018-08-01 00:00:00'); -- 1

select TIMESTAMPDIFF(HOUR,'2018-07-04 23:00:00','2018-07-04 23:59:59'); -- 0
select TIMESTAMPDIFF(HOUR,'2018-07-04 23:00:00','2018-07-05 00:00:00'); -- 1

select TIMESTAMPDIFF(WEEK,'2018-07-04 23:59:59','2018-07-11 23:59:58'); -- 0
select TIMESTAMPDIFF(WEEK,'2018-07-04 23:59:59','2018-07-11 23:59:59'); -- 1
select TIMESTAMPDIFF(WEEK,'2018-07-05 23:59:59','2018-07-12 00:00:00'); -- 0
select TIMESTAMPDIFF(WEEK,'2018-07-05 00:00:00','2018-07-12 00:00:00'); -- 1
select TIMESTAMPDIFF(WEEK,'2018-07-05 00:00:01','2018-07-12 00:00:00'); -- 0
-- 如果需要获取大于时分秒的类型就用年月日格式  时分秒的依旧可以获取到对应的值但值意义不大这时用时分秒年月日格式即可
select TIMESTAMPDIFF(HOUR,'2018-07-04','2018-07-05'); -- 24
select TIMESTAMPDIFF(WEEK,'2018-07-04','2018-07-11'); -- 1
-- 且两者都必须是相同的类型，不然结果为空。
SELECT TIMEDIFF('2018-05-21 14:51:43','2018-05-19 12:54:43');-- 49:57:00
SELECT TIMEDIFF('2018-05-21','2018-05-19');-- 00:00:00
SELECT TIMEDIFF('14:51:43','12:54:43');-- 01:57:00

	

time_to_sec(timediff (time1，time2))

-- TIMESTAMPDIFF 跟  time_to_sec(timediff (time1，time2)) 都可以用来求秒数 但是有点不同 
#但如果时间差比较大，超出timediff 的限制(timediff最大值：838:59:59即：34天22小时59分59秒)，得出的结果就是一样的，因为timediff只计算时分秒，不计算 年月日
select time_to_sec(timediff('08:08:08','00:00:00')) -- 29288 = 8*60*60+8*60+8
 select time_to_sec(timediff('23:59:58','23:59:59')) -- -1
 SELECT TIMEDIFF('2018-05-21 14:51:43','2018-05-19 12:54:43');-- 49:57:00
 select time_to_sec(timediff('2018-05-21 14:51:43','2018-05-19 12:54:43')) -- 179820 = 49*60*60+57*60 
 
 select time_to_sec(TIMESTAMPDIFF(second,'2018-07-04 23:59:59','2018-07-04 23:59:58')) -- -1
 select TIMESTAMPDIFF(second,'2018-07-04 23:59:59','2018-07-04 23:59:58');--  -1
 
  select time_to_sec(TIMESTAMPDIFF(second,'2018-07-04 23:59:59','2018-07-04 23:58:58')) -- null  只要年月日时分不一致就是空 跟TIMESTAMPDIFF配合使用的话年月日时分必须的保持一致 换句话TIMESTAMPDIFF函数我都已经求出秒数了 也没必要在搭配time_to_sec 画蛇添足
  select  TIMESTAMPDIFF(second,'2018-07-04 23:59:59','2018-07-05 23:59:58') -- 86399
  
 select timediff('2018-06-21 23:59:59','2018-05-18 01:01:01') --  838:58:58  这个多一秒永远的值都是 838:59:59
 select time_to_sec(timediff('2018-06-21 23:59:59','2018-05-18 01:01:01')) ;--   3020338 = 838*60*60+58*60+58
 select time_to_sec(timediff('2018-06-21 23:59:59','2018-05-18 01:01:00')) ;--  3020339 这个多一秒永远的值都是 3020339
 select time_to_sec(timediff('2018-06-21 23:59:59','2018-05-18 01:00:00')) ;--  3020399  
  
  

#注：sql很活 都能拿到一致的结果情况下优先使用最优解函数
-- 计算日期是星期几 DAYOFWEEK(date)
SELECT DAYOFWEEK('2021-11-26') AS '今天是星期几'; -- 6 实际星期5
-- 计算本月是第几天 DAYOFMONTH(date)
SELECT DAYOFMONTH('2021-11-26') AS '本月第几天';-- 26
-- 计算本年是第几天DAYOFYEAR(date)
SELECT DAYOFYEAR('2021-11-26') AS '本年第几天';-- 330 最优解 
SELECT TO_DAYS('2021-11-26')-TO_DAYS('2021-01-01')+1 AS '本年第几天';-- 330
SELECT DATEDIFF('2021-11-26','2021-01-01')+1 AS '本年第几天';-- 330
SELECT TIMESTAMPDIFF(day,'2021-01-01','2021-11-26')+1 AS '本年第几天';-- 330
--   计算本年是第几月份 MONTH(date)
SELECT MONTH('2021-11-26') AS '本年第几月份'; -- 11
-- 计算本年是第几季度 QUARTER(date)
SELECT QUARTER('2021-11-26') AS '本年第几季度'; -- 4

mysql 判断闰年

#判断2月份是不是28天
SELECT DAY
	(
	LAST_DAY(
	DATE_ADD(
	DATE_ADD(
	DATE_ADD( CURRENT_DATE, INTERVAL - DAYOFYEAR( CURRENT_DATE ) DAY ),
	INTERVAL 1 DAY 
	),
	INTERVAL 1 MONTH 
	) 
	) 
	) 

#if判断法：
select IF(((YEAR(now()) % 4 = 0 AND YEAR(now()) % 100 != 0) OR YEAR(now()) % 400 = 0) ,0,1)
		 

date_add(date,interval if(day(birthday)=29&&day(date)=28,1,0) day)

MySQL根据出生日期计算年龄

#方法一：缺陷就是当日期为未来日期时结果为0，而不是负数；这里使用了5个函数和两个运算符。
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS('1994-04-28')), '%Y')+0 AS age 
#方法二：解决了方法一为负数的问题，但看起来更复杂；这里使用了6个函数和3个运算符。
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT('1994-04-28', '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT('1994-04-28', '00-%m-%d')) AS age
# 综合一、二版本：=======================建议使用=========================
SELECT year( from_days( datediff( now( ), '1994-04-28')));--   一
SELECT YEAR(CURDATE())-YEAR('1994-04-28')-(RIGHT(CURDATE(),5)<RIGHT('1994-04-28',5));--   二
#方法三：取生日和当前日期之前的天数除以一年的实际天数（365天5小时48分46秒），然后取整。这样只用了三个函数和一个运算符就搞定了。
SELECT FLOOR(DATEDIFF(CURDATE(), '1994-04-28')/365.2422) 
#方法四
SELECT  TIMESTAMPDIFF(YEAR,'1994-04-28', CURDATE()) 
#方法五
SELECT ROUND(DATEDIFF(CURDATE(), '1994-04-28')/365.2422) 


	

时间戳（timestamp）转换、增、减函数：

时间戳的更多知识点击获取

#注：踩坑点 TIMESTAMP值不能比1970早，也不能比2037晚，这意味着，一个日期例如'1968-01-01'，当作为一个 DATETIME或DATE值时它是合法的，但它不是一个正确TIMESTAMP值！并且如果将这样的一个对象赋值给TIMESTAMP列，它将被变换为 0。  

#当指定日期值时，当心某些缺陷：
#1.允许作为字符串指定值的宽松格式能被欺骗。例如，因为“:”分隔符的使用，值'10:11:12'可能看起来像时间值，但是如果在一个日期中使用，上下文将作为年份被解释成'2010-11-12'。值'10:45:15'将被变换到'0000-00-00'，因为'45'不是一个合法的月份。
#2.以2位数字指定的年值是模糊的，因为世纪是未知的。MySQL使用下列规则解释2位年值：在00-69范围的年值被变换到2000-2069。 在范围70-99的年值被变换到1970-1999。三、当指定日期值时，当心某些缺陷：

#时间戳是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起至现在的总秒数。
#在MySQL上述三个大版本中，默认时间戳(Timestamp)类型的取值范围为'1970-01-01 00:00:01' UTC 至'2038-01-19 03:14:07' UTC，数据精确到秒级别，该取值范围包含约22亿个数值，因此在MySQL内部使用4个字节INT类型来存放时间戳数据：

#1、在存储时间戳数据时，先将本地时区时间转换为UTC时区时间，再将UTC时区时间转换为INT格式的毫秒值(使用UNIX_TIMESTAMP函数)，然后存放到数据库中。
#2、在读取时间戳数据时，先将INT格式的毫秒值转换为UTC时区时间(使用FROM_UNIXTIME函数)，然后再转换为本地时区时间，最后返回给客户端。
#在MySQL上述三个大版本中，默认时间戳(Timestamp)类型的取值范围为'1970-01-01 00:00:01' UTC 至'2038-01-19 03:14:07' UTC，数据精确到秒级别，该取值范围包含约22亿个数值，因此在MySQL内部使用4个字节INT类型来存放时间戳数据：

#1、在存储时间戳数据时，先将本地时区时间转换为UTC时区时间，再将UTC时区时间转换为INT格式的毫秒值(使用UNIX_TIMESTAMP函数)，然后存放到数据库中。
#2、在读取时间戳数据时，先将INT格式的毫秒值转换为UTC时区时间(使用FROM_UNIXTIME函数)，然后再转换为本地时区时间，最后返回给客户端。

时间戳字段定义主要影响两类操作：在创建新记录和修改现有记录的时候都对这个数据列刷新
插入记录时，时间戳字段包含DEFAULT CURRENT_TIMESTAMP，如插入记录时未指定具体时间数据则将该时间戳字段值设置为当前时间
更新记录时，时间戳字段包含ON UPDATE CURRENT_TIMESTAMP，如更新记录时未指定具体时间数据则将该时间戳字段值设置为当前时间

#语法1： timestamp(date) -- date to timestamp 语法2：timestamp(dt,time)-- dt + time
select timestamp(now()); -- 2021-11-27 01:21:48 
select timestamp(CURRENT_DATE); --  2021-11-27 00:00:00 -- 时间部分被设置为'00:00:00'，因为DATE值中不包含有时间信息。
select timestamp('2008-08-08'); -- 2008-08-08 00:00:00
select timestamp('2008-08-08 08:00:00', '01:01:01'); -- 2008-08-08 09:01:01
select timestamp('2008-08-08 08:00:00', '10 01:01:01'); -- 2008-08-18 09:01:01
#语法：timestampadd(unit,interval,datetime_expr) unit值跟TIMESTAMPDIFF一致
select timestampadd(day, 1, '2008-08-08 08:00:00'); -- 2008-08-09 08:00:00
select timestampadd(day, 1, '2008-08-08'); -- 2008-08-09
#语法 timestampdiff(unit,datetime_expr1,datetime_expr2) -- 上文已存在
#MySQL timestampadd() 函数类似于 date_add()。
select date_add('2008-08-08 08:00:00', interval 1 day); -- 2008-08-09 08:00:00
#MySQL timestampdiff() 函数就比 datediff() 功能强多了，datediff() 只能计算两个日期（date）之间相差的天数。

select timestampdiff(year,'2002-05-01','2001-01-01'); -- -1
select timestampdiff(day ,'2002-05-01','2001-01-01'); -- -485
select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); -- -12

select datediff('2008-08-08 23:59:59', '2008-08-01 00:00:00'); -- 7  只看年月日
select datediff('2008-08-08', '2008-08-01'); -- 7


 

MySQL 时区（timezone）转换函数

#语法：convert_tz(dt,from_tz,to_tz)
select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00'); -- 2008-08-08 04:00:00
SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');-- 2004-01-01 22:00:00
SELECT CONVERT_TZ('2020-11-19 19:59:00', '+00:00', '+05:30') -- 2020-11-20 01:29:00
SELECT CONVERT_TZ('2020-11-19', '+00:00', '+05:30') -- 2020-11-19 05:30:00

select  CONVERT_TZ('2020-11-19 10:53:00', '+00:00', '+05:30') ;-- 2020-11-19 16:23:00
select  CONVERT_TZ('2020-11-19 10:53:00', '-05:00', '+05:30') ;-- 2020-11-19 21:23:00

select  CONVERT_TZ('2020-11-19 10:53:00' , '+00:00', '-05:00');-- 2020-11-19 05:53:00
select  CONVERT_TZ('2020-11-19 10:53:00' , '+05:30', '-05:00');-- 2020-11-19 00:23:00
-- 总结：先算后再算前 后边符号不变 前边取相反符号  算出来是多少就是多少
#时区转换也可以通过 date_add, date_sub, timestampadd 来实现。
select date_add('2008-08-08 12:00:00', interval -8 hour); -- 2008-08-08 04:00:00
select date_sub('2008-08-08 12:00:00', interval 8 hour); -- 2008-08-08 04:00:00
select timestampadd(hour, -8, '2008-08-08 12:00:00'); -- 2008-08-08 04:00:00
 
 

关于 date_add()等type的取值

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

type 参数可以是下列值：

日期和时间函数图鉴

日期类总结

获得当前时间：NOW(); curdate(); CURRENT_DATE();(sysdate()用的少,在需要函数执行时动态得到值在用它)
CURTIME();获取时分秒
获得当前时间戳函数： CURRENT_TIME(); CURRENT_TIMESTAMP();
日期/时间 转换为字符串函数：date_format(date,format), time_format(time,format)
混乱字符串转换为日期函数：str_to_date(str, format)
日期、天数 转换函数：to_days(date), from_days(days)
时间、秒 转换函数：time_to_sec(time), sec_to_time(seconds)

冷门SQL

MySQL 格式化日期函数 DATE_FORMAT(), FROM_UNIXTIME() 和 UNIX_TIMESTAMP() 之间区别

DATE_FORMAT() 把日期进行格式化，FROM_UNIXTIME() 把时间戳格式化成一个日期，UNIX_TIMESTAMP() 正好相反，把日期格式化成时间戳。

注：FROM_UNIXTIME 默认 年月日时分秒格式 如果需要其他日期格式需要在加上日期格式

#互转 
SELECT UNIX_TIMESTAMP(20101129100940), UNIX_TIMESTAMP(101129100940);-- 都为：1290996580
SELECT FROM_UNIXTIME(1290996580),FROM_UNIXTIME(1290996580,'%Y-%m-%d %H:%i:%s') as var10 ;-- 都为：2010-11-29 10:09:40

SELECT UNIX_TIMESTAMP(20101129), UNIX_TIMESTAMP(101129);-- 都为：1290960000
SELECT FROM_UNIXTIME(1290960000),-- 2010-11-29 00:00:00
FROM_UNIXTIME(1290960000,'%Y-%m-%d')   ;-- 2010-11-29

#FROM_UNIXTIME(unix_timestamp,format)函数
#该参数是Unix 时间戳
#验证
SELECT FROM_UNIXTIME(1290996580) as var1,  -- 2010-11-29 10:09:40	
 FROM_UNIXTIME(1290996580,'MMdd-yyyy') as var2,-- MMdd-yyyy	
 FROM_UNIXTIME(1290996580,'') as var3,-- 空
 FROM_UNIXTIME(1290996580,'nullstr') as var4, -- nullstr
 FROM_UNIXTIME(1290996580,null) as var5, -- 空
 FROM_UNIXTIME(1290996580,'%Y%m%d') as var6 ,-- 20101129
 FROM_UNIXTIME(1290996580,'%Y年%m月%d') as var7 ;-- 2010年11月29
 #UNIX_TIMESTAMP() 是与之相对正好相反的时间函数  
 #若无参数调用，则返回一个 Unix timestamp ('1970-01-01 00:00:00' GMT 之后的秒数) 作为无符号整数。若用date 来调用 UNIX_TIMESTAMP()，它会将参数值以'1970-01-01 00:00:00' GMT后的秒数的形式返回。date 可以是一个 DATE 字符串、一个 DATETIME字符串、一个 TIMESTAMP或一个当地时间的YYMMDD 或YYYMMDD格式的数字。
 SELECT FROM_UNIXTIME( 1290996580 ) -- 2010-11-29 10:09:40
 
 SELECT UNIX_TIMESTAMP() ,-- 1637823867  
 UNIX_TIMESTAMP('2010-11-29'),-- 1290960000
 UNIX_TIMESTAMP(2010-11-29),-- 0
 UNIX_TIMESTAMP(20101129),-- 1290960000
 UNIX_TIMESTAMP(101129),-- 1290960000
--  UNIX_TIMESTAMP(2010-11-29 10:09:40),-- 报错
 UNIX_TIMESTAMP(20101129100940),-- 1290996580
 UNIX_TIMESTAMP(101129100940);-- 1290996580
 
 SELECT FROM_UNIXTIME(1290960000) as var1,  -- 2010-11-29 00:00:00
 FROM_UNIXTIME(1290960000,'MMdd-yyyy') as var2,-- MMdd-yyyy	
 FROM_UNIXTIME(1290960000,'') as var3,-- 空
 FROM_UNIXTIME(1290960000,'nullstr') as var4, -- nullstr
 FROM_UNIXTIME(1290960000,null) as var5 ,-- 空
 FROM_UNIXTIME(1290960000,'%Y%m%d') as var6 ,-- 20101129
 FROM_UNIXTIME(1290960000,'%Y年%m月%d') as var7 ,-- 2010年11月29
 FROM_UNIXTIME(1290960000,'%Y-%m-%d %H:%m:%s') as var8 ,-- 2010-11-29 00:11:00
 FROM_UNIXTIME(1290960000,'%Y-%m-%d %H:%i:%s') as var9 ,-- 2010-11-29 00:00:00
 FROM_UNIXTIME(1290996580,'%Y-%m-%d %H:%i:%s') as var10 ;-- 2010-11-29 10:09:40
	 

时间时间戳互转工具

参考网址推荐

详细练习

时间函数练习

Mysql查询“当天、昨天、上周、本月、上一季、本年”的SQL语句

今天  
select * from 表名 where to_days(时间字段名) = to_days(now());
或者
select * from 表名 where to_days(时间字段名) -to_days(now())=0;
昨天 
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1  
and TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) != 0
上面固然可以实现毕竟很麻烦 建议使用更贴切的函数 
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 1 DAY) = DATE_FORMAT(时间字段名,'%Y-%m-%d') -- 昨天数据   (注意前后日期格式不一致时注意格式化一致)
 select DATE_SUB(CURDATE(), INTERVAL 1 DAY)-- 2021-11-24 --  昨天
 select DATE_SUB(CURDATE(), INTERVAL 0 DAY)-- 2021-11-25 -- 今天
 select DATE_SUB(CURDATE(), INTERVAL -1 DAY)-- 2021-11-26 -- 后天
 
近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();

动态sql语句基本语法：https://bbs.csdn.net/topics/280007702

https://bbs.csdn.net/topics/310162964?list=9107182

动态sql语句基本语法：https://bbs.csdn.net/topics/280007702

https://bbs.csdn.net/topics/310162964?list=9107182

作者：远看寒山石径斜链接：https://www.imooc.com/article/71440