mysql时间日期函数例子

下面本文章给各位整理了不少的mysql时间日期函数例子了,下面我们一起来看看这些日期时间函数的一些应用例子,希望文章对各位会有帮助.

1.DAYOFWEEK(date);返回data时间是周几(1 => 星期日,2 => 星期一,3 => 星期二,……，7 => 星期六).

1. mysql>selectDAYOFWEEK('2013-06-0914:00:00');
2. +----------------------------------+
3. |DAYOFWEEK('2013-06-0914:00:00')|
4. +----------------------------------+
5. |1|
6. +----------------------------------+

2.DAYOFMONTH(date);返回date是一个月的第几天 1——31

1. mysql>selectDAYOFMONTH('2013-06-0914:00:00');
2. +-----------------------------------+
3. |DAYOFMONTH('2013-06-0914:00:00')|
4. +-----------------------------------+
5. |9|
6. +-----------------------------------+

3.DAYOFYEAR(date);返回date是一年中的第几天 1———366

1. mysql>selectDAYOFYEAR('2013-06-0914:00:00');
2. +----------------------------------+
3. |DAYOFYEAR('2013-06-0914:00:00')|
4. +----------------------------------+
5. |160|
6. +----------------------------------+

4.WEEKDAY(date);返回date是周几(0 => 星期一，1 => 星期二，2 => 星期三，……, 6=> 星期日).

1. mysql>selectWEEKDAY('2013-06-0914:00:00');
2. +--------------------------------+
3. |WEEKDAY('2013-06-0914:00:00')|
4. +--------------------------------+
5. |6|
6. +--------------------------------+

5.MONTH(date);返回date的月份 1—–12

1. mysql>selectMONTH('2013-06-0914:00:00');
2. +------------------------------+
3. |MONTH('2013-06-0914:00:00')|
4. +------------------------------+
5. |6|
6. +------------------------------+

6.DAYNAME(date);返回date的星期英文名

1. mysql>selectDAYNAME('2013-06-0914:00:00');
2. +--------------------------------+
3. |DAYNAME('2013-06-0914:00:00')|
4. +--------------------------------+
5. |Sunday|
6. +--------------------------------+

7.MONTHNAME(date);返回date的月份的英文名

1. mysql>selectMONTHNAME('2013-06-0914:00:00');
2. +----------------------------------+
3. |MONTHNAME('2013-06-0914:00:00')|
4. +----------------------------------+
5. |June|
6. +----------------------------------+

8.QUARTER(date);返回date在季度中的排序 1——-4

1. mysql>selectQUARTER('2013-06-0914:00:00');
2. +--------------------------------+
3. |QUARTER('2013-06-0914:00:00')|
4. +--------------------------------+
5. |2|
6. +--------------------------------+

9.WEEK(date,first);返回date是一年的第几个周,first = 0从星期日开始算，first = 1从星期一开始算 默认为00——–52

1. mysql>selectWEEK('2013-06-0914:00:00',0);
2. +-------------------------------+
3. |WEEK('2013-06-0914:00:00',0)|
4. +-------------------------------+
5. |23|
6. +-------------------------------+

10.HOUR(date);返回date的小时部分

1. mysql>selectHOUR('2013-06-0914:00:00');
2. +-----------------------------+
3. |HOUR('2013-06-0914:00:00')|
4. +-----------------------------+
5. |14|
6. +-----------------------------+

11.YEAR(date);返回date的年份部分

1. mysql>selectYEAR('2013-06-0914:00:00');
2. +-----------------------------+
3. |YEAR('2013-06-0914:00:00')|
4. +-----------------------------+
5. |2013|
6. +-----------------------------+

12.MINUTE(date);返回date的分钟部分

1. mysql>selectMINUTE('2013-06-0914:22:22');
2. +-------------------------------+
3. |MINUTE('2013-06-0914:22:22')|
4. +-------------------------------+
5. |22|
6. +-------------------------------+

13.SECOND(date);返回date的秒部分

1. mysql>selectSECOND('2013-06-0914:22:22');
2. +-------------------------------+
3. |SECOND('2013-06-0914:22:22')|
4. +-------------------------------+
5. |22|
6. +-------------------------------+

14.PERIOD_ADD(date,num);date加上num后的日期 date的日期格式为 yyyymmdd 或者yyyymm，若精确到日num单位是日，若是月则num为月单位增加

1. mysql>selectPERIOD_ADD(201306,3);
2. +----------------------+
3. |PERIOD_ADD(201306,3)|
4. +----------------------+
5. |201309|
6. +----------------------+
7. mysql>selectPERIOD_ADD(20130609,3);
8. +------------------------+
9. |PERIOD_ADD(20130609,3)|
10. +------------------------+
11. |20130612|
12. +------------------------+

15.PERIOD_DIFF(date1,date2);date1减去date2的差值月数

1. mysql>selectPERIOD_DIFF(201306,201309);
2. +----------------------------+
3. |PERIOD_DIFF(201306,201309)|
4. +----------------------------+
5. |-3|
6. +----------------------------+

16.DATE_ADD(date, INTERVAL num type) == ADDDATE(date, INTERVAL num type);DATE_SUB(date, INTERVAL num type) == SUBDATE(date, INTERVAL num type)；date相加或者相减一个制定的单位时间.

type的值:SECOND,MINUTE,HOUR,DAY,MONTH,WEEK,MONTH,YEAR

1. mysql>selectDATE_ADD('2013-06-0914:22:22',INTERVAL1DAY);
2. +------------------------------------------------+
3. |DATE_ADD('2013-06-0914:22:22',INTERVAL1DAY)|
4. +------------------------------------------------+
5. |2013-06-1014:22:22|
6. +------------------------------------------------+
7. mysql>selectADDDATE('2013-06-0914:22:22',INTERVAL1DAY);
8. +-----------------------------------------------+
9. |ADDDATE('2013-06-0914:22:22',INTERVAL1DAY)|
10. +-----------------------------------------------+
11. |2013-06-1014:22:22|
12. +-----------------------------------------------+

16.TO_DAYS(date);给定一个日期返回一个天数（从0年开始的天数）

1. mysql>selectTO_DAYS('2013-06-0914:22:22');
2. +--------------------------------+
3. |TO_DAYS('2013-06-0914:22:22')|
4. +--------------------------------+
5. |735393|
6. +--------------------------------+

17.FROM_DAYS(num);给定一个天数 返回一个日期

1. mysql>selectFROM_DAYS(752341);
2. +-------------------+
3. |FROM_DAYS(752341)|
4. +-------------------+
5. |2059-11-03|
6. +-------------------+

18.DATE_FORMAT(date,format);格式化日期