mysql日期和时间函数总结

文章收藏了大量的关于mysql日期和时间的用法总结，有需要的同学可参考本教程。

NOW()函数获得当前的日期和时间：
select now();
/*
+---------------------+
| now()               |
+---------------------+
| 2009-05-18 20:11:24 |
+---------------------+
1 row in set (0.02 sec)
*/呵，这个函数是我经常用的，没事就看一下当前时间。看离下班还有多久，呵呵。 
CURTIME()和CURDATE()比NOW()更为具体化，返回当前的时间和日期：
select curtime(),curdate();
/*
+-----------+------------+
| curtime() | curdate()  |
+-----------+------------+
| 20:13:28  | 2009-05-18 |
+-----------+------------+
1 row in set (0.02 sec)
*/UNIX_TIMESTAMP()函数返回UNIX：
select unix_timestamp();
/*
+------------------+
| unix_timestamp() |
+------------------+
|       1242648913 |
+------------------+
1 row in set (0.00 sec)
*/呃，时间戳咋转换为时间呢？难道一定要用PHP的strtotime()？呵，那太麻烦了吧？别急，这就来了。
FROM_UNIXTIME()函数把UNIX时间戳转换为MySQL易读的日期和时间：
select from_unixtime(unix_timestamp());
/*
+---------------------------------+
| from_unixtime(unix_timestamp()) |
+---------------------------------+
| 2009-05-18 20:17:23             |
+---------------------------------+
1 row in set (0.02 sec)
*/我感觉这个例子，比直接读取一个from_unixtime()更容易让你理解不是吗？ 
YEAR()——年,MONTH(),MONTHNAME()——月,DAYOFYEAR(),DAYOFWEEK(),DAYOFMONTH()——这天在年，星期，月份里的序数：
select year(20030415012345),year(2009-05-18);
/*
+----------------------+------------------+
| year(20030415012345) | year(2009-05-18) |
+----------------------+------------------+
|                 2003 |             NULL |
+----------------------+------------------+
1 row in set, 1 warning (0.02 sec)
*/
select year(20030415012345),year('2009-05-18');
/*
+----------------------+--------------------+
| year(20030415012345) | year('2009-05-18') |
+----------------------+--------------------+
|                 2003 |               2009 |
+----------------------+--------------------+
1 row in set (0.02 sec)
*/看到没？如果我不把带有格式的日期放入引号内，返回来的可是NULL哦。
另外，如果year()不带参数的话，是会出错的。嗯。刚才有试验过了。
select month(20030414012345),month('2009-05-18'),month(2009-05-18),month('2009-13-18');
/*
+-----------------------+---------------------+-------------------+---------------------+
| month(20030414012345) | month('2009-05-18') | month(2009-05-18) | month('2009-13-18') |
+-----------------------+---------------------+-------------------+---------------------+
|                     4 |                   5 |              NULL |                NULL |
+-----------------------+---------------------+-------------------+---------------------+
1 row in set, 2 warnings (0.09 sec)
*/看到没？同样，如果月份不合法的话也会返回NULL的哈，并且带有格式的参数一定要放入引号内。
select monthname('20090518'),monthname('2009-05-18'),monthname(2009-05-18),monthname('2009-13-18');
/*
+-----------------------+-------------------------+-----------------------+-------------------------+
| monthname('20090518') | monthname('2009-05-18') | monthname(2009-05-18) | monthname('2009-13-18') |
+-----------------------+-------------------------+-----------------------+-------------------------+
| May                   | May                     | NULL                  | NULL                    |
+-----------------------+-------------------------+-----------------------+-------------------------+
1 row in set, 2 warnings (0.02 sec)
*/这个我个人感觉与MONTH()相同哈。只是返回结果是月份的名字，所以当然是英文的啦。 
select dayofyear(20090518) as day1,dayofyear('20090518') as day2,dayofyear('2009-05-18') as day3,dayofyear(20090518203000) as day4,dayofyear(20091318) as day5;
/*
+------+------+------+------+------+
| day1 | day2 | day3 | day4 | day5 |
+------+------+------+------+------+
|  138 |  138 |  138 |  138 | NULL |
+------+------+------+------+------+
1 row in set, 1 warning (0.02 sec)
*/呃，DAYOFMONTH()与DAYOFWEEK()同此一样，就不再多举例子了。嗯。
同时，与MONTHNAME()一样，也有DAYNAME()获得该日期所代表的星期名（即星期几）。。
WEEK()函数返回指定日期是一年的第几个星期，YEERWEEK()函数返回指定的日期是哪一年的哪个星期：
select week(20090301) as week1,week(2009-03-01) as week2,week('2009-03-01') as week3,yearweek(20090518) as week4,yearweek(2009-05-18) as week5,yearweek('2009-05-18') as week6;
/*
+-------+-------+-------+--------+-------+--------+
| week1 | week2 | week3 | week4  | week5 | week6  |
+-------+-------+-------+--------+-------+--------+
|     9 |  NULL |     9 | 200920 |  NULL | 200920 |
+-------+-------+-------+--------+-------+--------+
1 row in set, 2 warnings (0.02 sec)
*/HOUR(),MINUTE()和SECOND()函数分析时间值，并且分别返回小时、分钟和秒的部分：
select hour(182300),second(123400),minute('20:56');
/*
+--------------+----------------+-----------------+
| hour(182300) | second(123400) | minute('20:56') |
+--------------+----------------+-----------------+
|           18 |              0 |              56 |
+--------------+----------------+-----------------+
1 row in set (0.00 sec)
*/
由WEEK()和YEARWEEK()返回的值通常都是在0到53之间（不明白的算一下一年有多少个星期），但是，
我们可以把它修改到1到54之间，而且还可以通过选择函数的第二个参数来决定一个星期是从周日开始还是周一开始
TIME_TO_SEC()——把时间转为秒,SEC_TO_TIME()——把秒数转为一个易读的时间：
select sec_to_time(80),sec_to_time(3720),time_to_sec('24:01:10');
/*
+-----------------+-------------------+-------------------------+
| sec_to_time(80) | sec_to_time(3720) | time_to_sec('24:01:10') |
+-----------------+-------------------+-------------------------+
| 00:01:20        | 01:02:00          |                   86470 |
+-----------------+-------------------+-------------------------+
1 row in set (0.00 sec)
*/日期相加减也是我们常要用到的，那么MySQL给了我们哪些现成的方法呢？让我来告诉你。
DATE_ADD(),DATE_SUB()——日期相加减：
select date_add('2009-05-18 00:00:00',INTERVAL 6 MONTH);
/*
+--------------------------------------------------+
| date_add('2009-05-18 00:00:00',INTERVAL 6 MONTH) |
+--------------------------------------------------+
| 2009-11-18 00:00:00                              |
+--------------------------------------------------+
1 row in set (0.04 sec)
*/

select date_add('2009-05-18 00:00:00', interval '12 03:45' day_minute);
/*
+-----------------------------------------------------------------+
| date_add('2009-05-18 00:00:00', interval '12 03:45' day_minute) |
+-----------------------------------------------------------------+
| 2009-05-30 03:45:00                                             |
+-----------------------------------------------------------------+
1 row in set (0.02 sec)
*/

select date_sub('2009-05-18 00:00:00',interval 6 hour);
/*
+-------------------------------------------------+
| date_sub('2009-05-18 00:00:00',interval 6 hour) |
+-------------------------------------------------+
| 2009-05-17 18:00:00                             |
+-------------------------------------------------+
1 row in set (0.00 sec)
*/

select date_sub('2009-05-18 00:00:00', interval '13-4' year_month);
/*
+-------------------------------------------------------------+
| date_sub('2009-05-18 00:00:00', interval '13-4' year_month) |
+-------------------------------------------------------------+
| 1996-01-18 00:00:00                                         |
+-------------------------------------------------------------+
1 row in set (0.02 sec)
*/

DAYOFWEEK(date)
　返回日期date是星期几(1=星期天,2=星期一,……7=星期六,ODBC标准)
mysql> select DAYOFWEEK('1998-02-03');
　　-> 3
WEEKDAY(date)
　返回日期date是星期几(0=星期一,1=星期二,……6= 星期天)。
mysql> select WEEKDAY('1997-10-04 22:23:00');
　　-> 5
mysql> select WEEKDAY('1997-11-05');
　　-> 2
DAYOFMONTH(date)
　返回date是一月中的第几日(在1到31范围内)
mysql> select DAYOFMONTH('1998-02-03');
　　-> 3
DAYOFYEAR(date)
　返回date是一年中的第几日(在1到366范围内)
mysql> select DAYOFYEAR('1998-02-03');
　　-> 34
MONTH(date)
　返回date中的月份数值
mysql> select MONTH('1998-02-03');
　　-> 2
DAYNAME(date)
　返回date是星期几(按英文名返回)
mysql> select DAYNAME("1998-02-05");
　　-> 'Thursday'
MONTHNAME(date)
　返回date是几月(按英文名返回)
mysql> select MONTHNAME("1998-02-05");
　　-> 'February'
QUARTER(date)
　返回date是一年的第几个季度
mysql> select QUARTER('98-04-01');
　　-> 2
WEEK(date,first)
　返回date是一年的第几周(first默认值0,first取值1表示周一是周的开始,0从周日开始)
mysql> select WEEK('1998-02-20');
　　-> 7
mysql> select WEEK('1998-02-20',0);
　　-> 7
mysql> select WEEK('1998-02-20',1);
　　-> 8
YEAR(date)
　返回date的年份(范围在1000到9999)
mysql> select YEAR('98-02-03');
　　-> 1998
HOUR(time)
　返回time的小时数(范围是0到23)
mysql> select HOUR('10:05:03');
　　-> 10
MINUTE(time)
　返回time的分钟数(范围是0到59)
mysql> select MINUTE('98-02-03 10:05:03');
　　-> 5
SECOND(time)
　返回time的秒数(范围是0到59)
mysql> select SECOND('10:05:03');
　　-> 3
PERIOD_ADD(P,N)
　增加N个月到时期P并返回(P的格式YYMM或YYYYMM)
mysql> select PERIOD_ADD(9801,2);
　　-> 199803
PERIOD_DIFF(P1,P2)
　返回在时期P1和P2之间月数(P1和P2的格式YYMM或YYYYMM)
mysql> select PERIOD_DIFF(9802,199703);
　　-> 11
DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
ADDDATE(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type)