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)