mysql日期和時(shí)間函數(shù)總結(jié)

文章收藏了大量的關(guān)于mysql日期和時(shí)間的用法總結(jié)，有需要的同學(xué)可參考本教程。

NOW()函數(shù)獲得當(dāng)前的日期和時(shí)間：
select now();
/*
+---------------------+
| now() |
+---------------------+
| 2009-05-18 20:11:24 |
+---------------------+
1 row in set (0.02 sec)
*/呵，這個(gè)函數(shù)是我經(jīng)常用的，沒事就看一下當(dāng)前時(shí)間?？措x下班還有多久，呵呵。
CURTIME()和CURDATE()比NOW()更為具體化，返回當(dāng)前的時(shí)間和日期：
select curtime(),curdate();
/*
+-----------+------------+
| curtime() | curdate() |
+-----------+------------+
| 20:13:28 | 2009-05-18 |
+-----------+------------+
1 row in set (0.02 sec)
*/UNIX_TIMESTAMP()函數(shù)返回UNIX：
select unix_timestamp();
/*
+------------------+
| unix_timestamp() |
+------------------+
| 1242648913 |
+------------------+
1 row in set (0.00 sec)
*/呃，時(shí)間戳咋轉(zhuǎn)換為時(shí)間呢？難道一定要用PHP的strtotime()？呵，那太麻煩了吧？別急，這就來了。
FROM_UNIXTIME()函數(shù)把UNIX時(shí)間戳轉(zhuǎn)換為MySQL易讀的日期和時(shí)間：
select from_unixtime(unix_timestamp());
/*
+---------------------------------+
| from_unixtime(unix_timestamp()) |
+---------------------------------+
| 2009-05-18 20:17:23 |
+---------------------------------+
1 row in set (0.02 sec)
*/我感覺這個(gè)例子，比直接讀取一個(gè)from_unixtime()更容易讓你理解不是嗎？
YEAR()——年,MONTH(),MONTHNAME()——月,DAYOFYEAR(),DAYOFWEEK(),DAYOFMONTH()——這天在年，星期，月份里的序數(shù)：
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)
*/看到?jīng)]？如果我不把帶有格式的日期放入引號(hào)內(nèi)，返回來的可是NULL哦。
另外，如果year()不帶參數(shù)的話，是會(huì)出錯(cuò)的。嗯。剛才有試驗(yàn)過了。
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)
*/看到?jīng)]？同樣，如果月份不合法的話也會(huì)返回NULL的哈，并且?guī)в懈袷降膮?shù)一定要放入引號(hào)內(nèi)。
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)
*/這個(gè)我個(gè)人感覺與MONTH()相同哈。只是返回結(jié)果是月份的名字，所以當(dāng)然是英文的啦。
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()同此一樣，就不再多舉例子了。嗯。
同時(shí)，與MONTHNAME()一樣，也有DAYNAME()獲得該日期所代表的星期名（即星期幾）。
WEEK()函數(shù)返回指定日期是一年的第幾個(gè)星期，YEERWEEK()函數(shù)返回指定的日期是哪一年的哪個(gè)星期：
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()函數(shù)分析時(shí)間值，并且分別返回小時(shí)、分鐘和秒的部分：
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之間（不明白的算一下一年有多少個(gè)星期），但是，
我們可以把它修改到1到54之間，而且還可以通過選擇函數(shù)的第二個(gè)參數(shù)來決定一個(gè)星期是從周日開始還是周一開始
TIME_TO_SEC()——把時(shí)間轉(zhuǎn)為秒,SEC_TO_TIME()——把秒數(shù)轉(zhuǎn)為一個(gè)易讀的時(shí)間：
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給了我們哪些現(xiàn)成的方法呢？讓我來告訴你。
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標(biāo)準(zhǔn))
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范圍內(nèi))
mysql> select DAYOFMONTH('1998-02-03');
　　-> 3
DAYOFYEAR(date)
　返回date是一年中的第幾日(在1到366范圍內(nèi))
mysql> select DAYOFYEAR('1998-02-03');
　　-> 34
MONTH(date)
　返回date中的月份數(shù)值
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是一年的第幾個(gè)季度
mysql> select QUARTER('98-04-01');
　　-> 2
WEEK(date,first)
　返回date是一年的第幾周(first默認(rèn)值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的小時(shí)數(shù)(范圍是0到23)
mysql> select HOUR('10:05:03');
　　-> 10
MINUTE(time)
　返回time的分鐘數(shù)(范圍是0到59)
mysql> select MINUTE('98-02-03 10:05:03');
　　-> 5
SECOND(time)
　返回time的秒數(shù)(范圍是0到59)
mysql> select SECOND('10:05:03');
　　-> 3
PERIOD_ADD(P,N)
　增加N個(gè)月到時(shí)期P并返回(P的格式Y(jié)YMM或YYYYMM)
mysql> select PERIOD_ADD(9801,2);
　　-> 199803
PERIOD_DIFF(P1,P2)
　返回在時(shí)期P1和P2之間月數(shù)(P1和P2的格式Y(jié)YMM或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)

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com