mysql时间函数总结

/*
mysql 官网 时间函数 参考  https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
*/
/*
一、获取当前时间
1.1 获得当前日期+时间（date + time）函数：now()
这些日期时间函数，都等同于 now()。鉴于 now() 函数简短易记，建议总是使用 now() 来替代上面列出的函数。
*/
SELECT NOW()  -- 2019-02-12 17:30:18
SELECT CURRENT_TIMESTAMP() -- 2019-02-12 17:30:55
SELECT LOCALTIME()  -- 2019-02-12 17:31:20
SELECT LOCALTIMESTAMP() -- 2019-02-12 17:31:43

/*
1.2 获得当前日期+时间（date + time）函数：sysdate()
sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。
*/
SELECT SYSDATE() -- 2019-02-12 17:33:51

/*
2. 获得当前日期（date）函数：curdate()
其中，下面的两个日期函数等同于 curdate()： current_date(),current_date
*/
SELECT CURDATE() -- 2019-02-12
SELECT CURRENT_DATE() -- 2019-02-12
SELECT CURRENT_DATE -- 2019-02-12

/*
3. 获得当前时间（time）函数：curtime()
其中，下面的两个时间函数等同于 curtime()：current_time(),current_time
*/
SELECT CURTIME() -- 17:36:47

/*
4. 获得当前 UTC 日期时间函数：utc_date(), utc_time(), utc_timestamp()
因为我国位于东八时区，所以本地时间 = UTC 时间 + 8 小时。UTC 时间在业务涉及多个国家和地区的时候，非常有用。
*/
SELECT UTC_DATE() -- 2019-02-12
SELECT UTC_TIME() -- 09:38:19
SELECT UTC_TIMESTAMP() -- 2019-02-12 09:38:40

/*
二、MySQL 日期时间函数。
1. 选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒
*/

SET @dt = '2008-09-10 07:15:30.123456'; -- 声明变量
  
SELECT DATE(@dt); -- 2008-09-10
SELECT TIME(@dt); -- 07:15:30.123456
SELECT YEAR(@dt); -- 2008
SELECT QUARTER(@dt); -- 3
SELECT MONTH(@dt); -- 9
SELECT WEEK(@dt); -- 36
SELECT DAY(@dt); -- 10
SELECT HOUR(@dt); -- 7
SELECT MINUTE(@dt); -- 15
SELECT SECOND(@dt); -- 30
SELECT MICROSECOND(@dt); -- 123456

SELECT YEAR( NOW() ) -- 2019
SELECT MONTH( NOW() ) -- 2

/*
2. MySQL Extract() 函数，可以上面实现类似的功能：
MySQL Extract() 函数除了没有date(),time() 的功能外，其他功能一应具全。并且还具有选取‘day_microsecond’ 等功能。
注意这里不是只选取 day 和 microsecond，而是从日期的 day 部分一直选取到 microsecond 部分。
MySQL Extract() 函数唯一不好的地方在于：你需要多敲几次键盘。

*/
SET @dt = '2008-09-10 07:15:30.123456'; 
  
SELECT EXTRACT(YEAR FROM @dt); -- 2008
SELECT EXTRACT(QUARTER FROM @dt); -- 3
SELECT EXTRACT(MONTH FROM @dt); -- 9
SELECT EXTRACT(WEEK FROM @dt); -- 36
SELECT EXTRACT(DAY FROM @dt); -- 10
SELECT EXTRACT(HOUR FROM @dt); -- 7
SELECT EXTRACT(MINUTE FROM @dt); -- 15
SELECT EXTRACT(SECOND FROM @dt); -- 30
SELECT EXTRACT(MICROSECOND FROM @dt); -- 123456
SELECT EXTRACT(YEAR_MONTH FROM @dt); -- 200809
SELECT EXTRACT(DAY_HOUR FROM @dt); -- 1007
SELECT EXTRACT(DAY_MINUTE FROM @dt); -- 100715
SELECT EXTRACT(DAY_SECOND FROM @dt); -- 10071530
SELECT EXTRACT(DAY_MICROSECOND FROM @dt); -- 10071530123456
SELECT EXTRACT(HOUR_MINUTE FROM @dt); -- 715
SELECT EXTRACT(HOUR_SECOND FROM @dt); -- 71530
SELECT EXTRACT(HOUR_MICROSECOND FROM @dt); -- 71530123456
SELECT EXTRACT(MINUTE_SECOND FROM @dt); -- 1530
SELECT EXTRACT(MINUTE_MICROSECOND FROM @dt); -- 1530123456
SELECT EXTRACT(SECOND_MICROSECOND FROM @dt); -- 30123456

SELECT EXTRACT(YEAR FROM NOW()); -- 2019

/*
3. MySQL dayof… 函数：dayofweek(), dayofmonth(), dayofyear()
分别返回日期参数，在一周、一月、一年中的位置。
日期 ‘2008-08-08′ 是一周中的第 6 天（1 = Sunday, 2 = Monday, …, 7 = Saturday）；一月中的第 8 天；一年中的第 221 天。

*/
SET @dt = '2008-08-08';
SELECT DAYOFWEEK(@dt); -- 6
SELECT DAYOFMONTH(@dt); -- 8
SELECT DAYOFYEAR(@dt); -- 221

SELECT DAYOFWEEK(NOW()); -- 3 \ 实际是星期二
SELECT DAYOFYEAR(NOW()); -- 43

/*
4. MySQL week… 函数：week(), weekofyear(), dayofweek(), weekday(), yearweek()

MySQL week() 函数，可以有两个参数，具体可看官方手册。 weekofyear() 和 week() 一样，都是计算“某天”是位于一年中的第几周。 weekofyear(@dt) 等价于 week(@dt,3)。

MySQL weekday() 函数和 dayofweek() 类似，都是返回“某天”在一周中的位置。不同点在于参考的标准， 
weekday：(0 = Monday, 1 = Tuesday, …, 6 = Sunday)； 
dayofweek：（1 = Sunday, 2 = Monday, …, 7 = Saturday）

MySQL yearweek() 函数，返回 year(2008) + week 位置(31)。

*/

SET @dt = '2008-08-08';
SELECT WEEK(@dt); -- 31  默认情况下是把每周日作为一周的开始
SELECT WEEK(@dt,0); -- 31  第二个参数是指每周的第一天是星期几 0为星期天
/*
第一周是怎么计算的？
模式	星期的第一天	范围	
0	Sunday	        0-53   
遇到本年的第一个星期天开始是第一周，前面的计算为第0周
1	Monday	        0-53	
假如第一周能超过3天，那么计算为本年的第一周。否则为第0周
可以理解为：
如果1月1号～4号是星期一，那么本年第一周开始。（之前的是第零周）
如果1月5号以后是星期一，那么现在开始，是本年的第二周了，之前的是第一周。
也可以这样理解：
如果包含1月1日的一周在新的一年中有4天或更多天，则为第1周。
否则，它是上一年的最后一周，下一周是第1周。
*/
SELECT WEEK(@dt,1); -- 32  
SELECT WEEK(@dt,3); -- 32 
SELECT WEEKOFYEAR(@dt); -- 32

SELECT DAYOFWEEK(@dt); -- 6
SELECT WEEKDAY(@dt); -- 4

SELECT YEARWEEK(@dt); -- 200831

SELECT DAYOFWEEK(NOW()); -- 3 /星期二
SELECT WEEKDAY(NOW());   -- 1 /星期二 

/*
5. MySQL 返回星期和月份名称函数：dayname(), monthname()
*/
SET @dt = NOW(); -- 2019-2-12  星期二 
SELECT DAYNAME(@dt); -- Tuesday
SELECT MONTHNAME(@dt); -- February

/*
6. MySQL last_day() 函数：返回月份中的最后一天。
*/
SELECT LAST_DAY('2008-02-01'); -- 2008-02-29
SELECT LAST_DAY('2008-08-08'); -- 2008-08-31

/**
三、MySQL 日期时间计算函数
*/

/*1. MySQL 为日期增加一个时间间隔：date_add()*/
SET @dt = NOW();
SELECT DATE_ADD(@dt, INTERVAL 1 DAY); -- add 1 day
SELECT DATE_ADD(@dt, INTERVAL 1 HOUR); -- add 1 hour
SELECT DATE_ADD(@dt, INTERVAL 1 MINUTE); -- ...
SELECT DATE_ADD(@dt, INTERVAL 1 SECOND);
SELECT DATE_ADD(@dt, INTERVAL 1 MICROSECOND);
SELECT DATE_ADD(@dt, INTERVAL 1 WEEK);
SELECT DATE_ADD(@dt, INTERVAL 1 MONTH);
SELECT DATE_ADD(@dt, INTERVAL 1 QUARTER);
SELECT DATE_ADD(@dt, INTERVAL 1 YEAR);
SELECT DATE_ADD(@dt, INTERVAL -1 DAY); -- sub 1 day
  
/*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
/**
date_add() 函数，分别为 @dt 增加了“1小时 15分 30秒” 和 “1天 1小时 15分 30秒”。
建议：总是使用 date_add() 日期时间函数来替代 adddate(), addtime()。
**/
  
/**
2. MySQL 为日期减去一个时间间隔：date_sub()
MySQL date_sub() 日期时间函数 和 date_add() 用法一致，不再赘述。另外，MySQL 中还有两个函数 subdate(), subtime()，建议，用 date_sub() 来替代。
*/

/*
3. MySQL 另类日期函数：period_add(P,N), period_diff(P1,P2)
函数参数“P” 的格式为“YYYYMM” 或者 “YYMM”，第二个参数“N” 表示增加或减去 N month（月）。
MySQL period_add(P,N)：日期加/减去N月。**/
SET @dt = '201902';
SELECT	PERIOD_ADD(@dt,-1) -- 201901

/*
4. MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)
MySQL datediff(date1,date2)：两个日期相减 date1 - date2，返回天数。*/
SELECT DATEDIFF('2008-08-08', '2008-08-01'); -- 7
SELECT DATEDIFF('2008-08-01', '2008-08-08'); -- -7
/*MySQL timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值。*/
SELECT TIMEDIFF('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
SELECT TIMEDIFF('08:08:08', '00:00:00'); -- 08:08:08
/*注意：timediff(time1,time2) 函数的两个参数类型必须相同。*/

/*四、MySQL 日期转换函数、时间转换函数*/
/*1. MySQL （时间、秒）转换函数：time_to_sec(time), sec_to_time(seconds)*/
SELECT TIME_TO_SEC('01:00:05'); -- 3605
SELECT SEC_TO_TIME(3605); -- '01:00:05'
  
/*2. MySQL （日期、天数）转换函数：to_days(date), from_days(days)*/
SELECT TO_DAYS('0000-00-00'); -- 0
SELECT TO_DAYS('2008-08-08'); -- 733627
SELECT FROM_DAYS(0); -- '0000-00-00'
SELECT FROM_DAYS(733627); -- '2008-08-08'
SELECT FROM_DAYS(370); -- '0001-01-05'

/*3. MySQL Str to Date （字符串转换为日期）函数：str_to_date(str, format)*/
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: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
/*可以看到，str_to_date(str,format) 转换函数，可以把一些杂乱无章的字符串转换为日期格式。另外，它也可以转换为时间。“format” 可以参看 MySQL 手册。*/
  
/*4. MySQL Date/Time to Str（日期/时间转换为字符串）函数：date_format(date,format), time_format(time,format)
MySQL 日期、时间转换函数：date_format(date,format), time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。它是 str_to_date(str,format) 函数的 一个逆转换。
 */
 SELECT DATE_FORMAT(NOW(),'%y-%m-%d %h:%i:%s') -- 19-02-12 08:38:39
 SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i:%s') -- 2019-02-12 08:39:06
  
/*5. MySQL 拼凑日期、时间函数：makdedate(year,dayofyear), maketime(hour,minute,second)*/
SELECT MAKEDATE(2001,31); -- '2001-01-31'
SELECT MAKEDATE(2001,32); -- '2001-02-01'
SELECT MAKETIME(12,15,30); -- '12:15:30'

/*五、MySQL 时间戳（Timestamp）函数*/
/*1. MySQL 获得当前时间戳函数：current_timestamp, current_timestamp()*/
SELECT CURRENT_TIMESTAMP() -- 2019-02-12 20:41:02
SELECT CURRENT_TIMESTAMP  --  2019-02-12 20:41:02

/*2. MySQL （Unix 时间戳、日期）转换函数：*/
SELECT UNIX_TIMESTAMP() -- 1549975329
SELECT UNIX_TIMESTAMP(NOW()) -- 1549975356
SELECT FROM_UNIXTIME(1549975329) -- 2019-02-12 20:42:09
SELECT FROM_UNIXTIME(1549975329,'%Y年%m月%d日 %h:%i:%s') -- 2019年02月12日 08:42:09