mysql常用日期與時間函數詳解

在mysql數據庫中日期與時間是我們常用的一些東西，下面我來給各位朋友總結一下在mysql中常用日期與時間函數一些用法句語，各位朋友可參考。

 

MySQL 日期類型：日期格式、所占存儲空間、日期范圍 比較。
日期類型        存儲空間       日期格式                 日期范圍
------------  ---------   --------------------- -----------------------------------------
datetime       8 bytes   YYYY-MM-DD HH:MM:SS   1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp      4 bytes   YYYY-MM-DD HH:MM:SS   1970-01-01 00:00:01 ~ 2038
date           3 bytes   YYYY-MM-DD            1000-01-01          ~ 9999-12-31
year           1 bytes   YYYY                  1901                ~ 2155

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)
*/

下面的查詢選擇了所有記錄，其date_col的值是在最後30天以內：

 代碼如下 復制代碼

mysql> SELECT something FROM table
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col)<= 30;

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的月份，范圍1到12。

 代碼如下 復制代碼 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一年中的季度，范圍1到4。

 代碼如下 復制代碼 mysql> select QUARTER('98-04-01');
-> 2

WEEK(date) 　
WEEK(date,first)
對於星期天是一周的第一天的地方，有一個單個參數，返回date的周數，范圍在0到52。2個參數形式WEEK()允許你指定星期是否開始於星期天或星期一。如果第二個參數是0，星期從星期天開始，如果第二個參數是1，從星期一開始。

 代碼如下 復制代碼

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（以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意階段參數P不是日期值。

 代碼如下 復制代碼 mysql> select PERIOD_ADD(9801,2);
-> 199803

PERIOD_DIFF(P1,P2)
返回在時期P1和P2之間月數，P1和P2應該以格式YYMM或YYYYMM。注意，時期參數P1和P2不是日期值。

 代碼如下 復制代碼

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)