MySQL中常用函数之日期函数使用详解

关联博文：
MySQL中日期时间类型与格式化

该篇总结MySQL 中常用的日期时间函数。

【1】DATE_FORMAT (datetime ,FormatCodes )

将日期转换成字符，示例如下：

SELECT DATE_FORMAT(SYSDATE(),'%W'),
DATE_FORMAT(SYSDATE(),'%w'),
DATE_FORMAT(SYSDATE(),'%Y-%m-%d');

date_format格式参考MySQLdateFormat格式

---

【2】EXTRACT(unit FROM date)：提取日期的指定部分

语法格式如下：

SELECT EXTRACT(unit FROM date)

示例如下：

SELECT 
EXTRACT(second FROM SYSDATE()),
EXTRACT(minute FROM SYSDATE()),
EXTRACT(hour FROM SYSDATE()),
EXTRACT(day FROM SYSDATE()),
EXTRACT(week FROM SYSDATE()),
EXTRACT(month FROM SYSDATE()),
EXTRACT(year FROM SYSDATE())

---

【3】DAY

语法格式如下：

DAY(date) //返回日期的天；DAY() 和DAYOFMONTH()的意义相同。
DAYNAME (date) //英文星期
DAYOFWEEK (date) //一周中的第几天,1为星期日，范围1-7
DAYOFMONTH(date)//一月中的第几天，一号为第一天,范围1-31
DAYOFYEAR (date) //一年中的第几天，范围1-366

示例如下：

SELECT 
DAY(SYSDATE()),
DAYNAME(SYSDATE()),
DAYOFWEEK(SYSDATE()),
DAYOFMONTH(SYSDATE()),
DAYOFYEAR(SYSDATE());

---

【4】week

语法格式如下：

WEEK(date[,mode])

该函数返回date 对应的星期数。WEEK() 的双参数形式允许你指定该星期是否起始于周日或周一， 以及返回值的范围是否为从0 到53 或从1 到53。若 mode参数被省略，则使用default_week_format系统自变量的值(不改变默认为0)。

以下为mode的说明：

---

WEEKDAY(date)
//返回date (0 = 周一, 1 = 周二, ... 6 = 周日)对应的工作日索引 


WEEKOFYEAR(date)
//将该日期的阳历周以数字形式返回，范围是从1到53。
//它是一个兼容度函数，相当于WEEK(date,3)。

---

【5】year

语法格式如下：

YEAR(date) 

返回date 对应的年份,范围是从1000到9999。

YEARWEEK(date), YEARWEEK(date,start) 

返回一个日期对应的年或周。start参数的工作同 start参数对 WEEK()的工作相同。结果中的年份可以和该年的第一周和最后一周对应的日期参数有所不同。

注意，周数和WEEK()函数都可选参数0或 1。可能会返回的week有所不同，原因是此时 WEEK() 返回给定年份的语境中的周。

---

【6】CURRENT_X

语法格式如下：

CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
NOW ( ) //当前时间

示例如下：

SELECT 
CURRENT_DATE(),
CURRENT_TIME(),
CURRENT_TIMESTAMP(),
NOW();

---

【7】日期加减

① 求差：date1-date2(只算日期，不算时间)

DATEDIFF (date1 ,date2 ) //两个日期差

示例如下 :

SELECT DATEDIFF(SYSDATE(),'2017-08-04') ;

---

TIMEDIFF(datetime1,datetime2) : 两个日期时间型相减：

SELECT TIMEDIFF(SYSDATE(),'2017-08-07 20:20:20'),TIMEDIFF('2017-08-07 20:20:20',SYSDATE());

---

② DATE_ADD(date2 , INTERVAL d_value d_type )

在date2中加上日期或时间

SELECT DATE_ADD(SYSDATE(),INTERVAL 3 HOUR)

type:可为second,minute,hour,day,week,month and year;

同ADDDATE(date,INTERVAL expr type)。

---

③ DATE_SUB (date2 , INTERVAL d_value d_type )

在date2上减去一个时间

SELECT DATE_SUB(SYSDATE(),INTERVAL 3 hour);

其他同②。

同SUBDATE(date,INTERVAL expr type)。

---

【8】根据日期判断秒，分，时，天，周，月，年

语法格式如下：

SECOND(datetime)//返回datetime的秒；
MINUTE(datetime)//返回datetime的分钟；
HOUR(datetime )//返回datetime的小时；
DAY (date) //返回日期的天；
WEEK (datetime [,start_of_week ]) //第几周
MONTH(datetime)//月份
YEAR(datetime)//年份
DATE (datetime ) //返回datetime的日期部分；

示例如下 :

SELECT 
SECOND(SYSDATE()), 
MINUTE(SYSDATE()),
HOUR(SYSDATE()),
DAY(SYSDATE()),
WEEK(SYSDATE()),
MONTH(SYSDATE()),
YEAR(SYSDATE())
DATE(SYSDATE());

---

【9】时间相加减

① 时间相加

将time_interval加到date2

ADDTIME (date2 ,time_interval ) 

示例如下 :

SELECT ADDTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');

---

② 时间相减

SELECT SUBTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');

---

【10】秒数,时间转换

① 秒数转时间

SEC_TO_TIME (seconds )

---

② 时间转秒数

TIME_TO_SEC(time)

---

【11】datetime的微秒部分

MICROSECOND(datetime) //微秒

---

【12】datetime的月的最后日期

即，隶属的月的最后一天是几号。

LAST_DAY(date) //date的月的最后日期

---

【13】month

语法格式如下：

MONTH(date)
//返回date 对应的月份，范围时从 1 到 12。

MONTHNAME(date)

//返回date 对应月份的全名。

---

【14】日期时间串拼接

语法格式如下：

MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串

示例如下 ：

SELECT MAKEDATE(2017,1),MAKETIME (20,20 ,20); 

---

【15】JavaScript中中的Date对象

用法如下所示：

var myDate=new Date();
myDate.getYear(); //获取当前年份(2位)
myDate.getFullYear(); //获取完整的年份(4位,1970-????)
myDate.getMonth(); //获取当前月份(0-11,0代表1月)
myDate.getDate(); //获取当前日(1-31)
myDate.getDay(); //获取当前星期X(0-6,0代表星期天)
myDate.getTime(); //获取当前时间(从1970.1.1开始的毫秒数)
myDate.getHours(); //获取当前小时数(0-23)
myDate.getMinutes(); //获取当前分钟数(0-59)
myDate.getSeconds(); //获取当前秒数(0-59)
myDate.getMilliseconds(); //获取当前毫秒数(0-999)
myDate.toLocaleDateString(); //获取当前日期
var mytime=myDate.toLocaleTimeString(); //获取当前时间
myDate.toLocaleString( ); //获取日期与时间