萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql時間日期函數例子
mysql時間日期函數例子
1.DAYOFWEEK(date);返回data時間是周幾(1 => 星期日，2 => 星期一，3 => 星期二，……，7 => 星期六)。
mysql> select DAYOFWEEK('2013-06-09 14:00:00');
+----------------------------------+
| DAYOFWEEK('2013-06-09 14:00:00') |
+----------------------------------+
| 1 |
+----------------------------------+
2.DAYOFMONTH(date);返回date是一個月的第幾天 1——31
mysql> select DAYOFMONTH('2013-06-09 14:00:00');
+-----------------------------------+
| DAYOFMONTH('2013-06-09 14:00:00') |
+-----------------------------------+
| 9 |
+-----------------------------------+
3.DAYOFYEAR(date);返回date是一年中的第幾天 1———366
mysql> select DAYOFYEAR('2013-06-09 14:00:00');
+----------------------------------+
| DAYOFYEAR('2013-06-09 14:00:00') |
+----------------------------------+
|160 |
+----------------------------------+
4.WEEKDAY(date);返回date是周幾(0 => 星期一，1 => 星期二，2 => 星期三，……, 6=> 星期日)。
mysql> select WEEKDAY('2013-06-09 14:00:00');
+--------------------------------+
| WEEKDAY('2013-06-09 14:00:00') |
+--------------------------------+
|6 |
+--------------------------------+
5.MONTH(date);返回date的月份 1—–12
mysql> select MONTH('2013-06-09 14:00:00');
+------------------------------+
| MONTH('2013-06-09 14:00:00') |
+------------------------------+
| 6 |
+------------------------------+
6.DAYNAME(date);返回date的星期英文名
mysql> select DAYNAME('2013-06-09 14:00:00');
+--------------------------------+
| DAYNAME('2013-06-09 14:00:00') |
+--------------------------------+
| Sunday|
+--------------------------------+
7.MONTHNAME(date);返回date的月份的英文名
mysql> select MONTHNAME('2013-06-09 14:00:00');
+----------------------------------+
| MONTHNAME('2013-06-09 14:00:00') |
+----------------------------------+
| June |
+----------------------------------+
8.QUARTER(date);返回date在季度中的排序 1——-4
mysql> select QUARTER('2013-06-09 14:00:00');
+--------------------------------+
| QUARTER('2013-06-09 14:00:00') |
+--------------------------------+
|2 |
+--------------------------------+
9.WEEK(date,first);返回date是一年的第幾個周,first = 0從星期日開始算，first = 1從星期一開始算 默認為00——–52
mysql> select WEEK('2013-06-09 14:00:00',0);
+-------------------------------+
| WEEK('2013-06-09 14:00:00',0) |
+-------------------------------+
| 23 |
+-------------------------------+
10.HOUR(date);返回date的小時部分
mysql> select HOUR('2013-06-09 14:00:00');
+-----------------------------+
| HOUR('2013-06-09 14:00:00') |
+-----------------------------+
| 14 |
+-----------------------------+
11.YEAR(date);返回date的年份部分
mysql> select YEAR('2013-06-09 14:00:00');
+-----------------------------+
| YEAR('2013-06-09 14:00:00') |
+-----------------------------+
| 2013 |
+-----------------------------+
12.MINUTE(date);返回date的分鐘部分
mysql> select MINUTE('2013-06-09 14:22:22');
+-------------------------------+
| MINUTE('2013-06-09 14:22:22') |
+-------------------------------+
| 22 |
+-------------------------------+
13.SECOND(date);返回date的秒部分
mysql> select SECOND('2013-06-09 14:22:22');
+-------------------------------+
| SECOND('2013-06-09 14:22:22') |
+-------------------------------+
| 22 |
+-------------------------------+
14.PERIOD_ADD(date,num);date加上num後的日期 date的日期格式為 yyyymmdd 或者yyyymm，若精確到日num單位是日，若是月則num為月單位增加
mysql> select PERIOD_ADD(201306,3);
+----------------------+
| PERIOD_ADD(201306,3) |
+----------------------+
|201309 |
+----------------------+
mysql> select PERIOD_ADD(20130609,3);
+------------------------+
| PERIOD_ADD(20130609,3) |
+------------------------+
|20130612 |
+------------------------+
15.PERIOD_DIFF(date1,date2);date1減去date2的差值月數
mysql> select PERIOD_DIFF(201306,201309);
+----------------------------+
| PERIOD_DIFF(201306,201309) |
+----------------------------+
|-3 |
+----------------------------+
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
mysql> select DATE_ADD('2013-06-09 14:22:22',INTERVAL 1 DAY);
+------------------------------------------------+
| DATE_ADD('2013-06-09 14:22:22',INTERVAL 1 DAY) |
+------------------------------------------------+
| 2013-06-10 14:22:22 |
+------------------------------------------------+
mysql> select ADDDATE('2013-06-09 14:22:22',INTERVAL 1 DAY);
+-----------------------------------------------+
| ADDDATE('2013-06-09 14:22:22',INTERVAL 1 DAY) |
+-----------------------------------------------+
| 2013-06-10 14:22:22 |
+-----------------------------------------------+
16.TO_DAYS(date);給定一個日期返回一個天數（從0年開始的天數）
mysql> select TO_DAYS('2013-06-09 14:22:22');
+--------------------------------+
| TO_DAYS('2013-06-09 14:22:22') |
+--------------------------------+
|735393 |
+--------------------------------+
17.FROM_DAYS(num);給定一個天數 返回一個日期
mysql> select FROM_DAYS(752341);
+-------------------+
| FROM_DAYS(752341) |
+-------------------+
| 2059-11-03 |
+-------------------+
18.DATE_FORMAT(date,format);格式化日期
%W 星期名字(Sunday……Saturday)
%D 有英語後綴的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 數字, 4 位
%y 年, 數字, 2 位
%a 縮寫的星期名字(Sun……Sat)
%d 月份中的天數, 數字(00……31)
%e 月份中的天數, 數字(0……31)
%m 月, 數字(01……12)
%c 月, 數字(1……12)
%b 縮寫的月份名字(Jan……Dec)
%j 一年中的天數(001……366)
%H 小時(00……23)
%k 小時(0……23)
%h 小時(01……12)
%I 小時(01……12)
%l 小時(1……12)
%i 分鐘, 數字(00……59)
%r 時間,12 小時(hh:mm:ss [AP]M)
%T 時間,24 小時(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一個星期中的天數(0=Sunday ……6=Saturday ）
%U 星期(0……52), 這裡星期天是星期的第一天
%u 星期(0……52), 這裡星期一是星期的第一天
%% 一個文字“%”。
mysql> select DATE_FORMAT('2013-06-09 14:22:22','%Y-%m-%d');
+-----------------------------------------------+
| DATE_FORMAT('2013-06-09 14:22:22','%Y-%m-%d') |
+-----------------------------------------------+
| 2013-06-09 |
+-----------------------------------------------+
19.CURDATE() == CURRENT_DATE();分為數字性返回和字符串返回
mysql> select CURDATE();
+------------+
| CURDATE() |
+------------+
| 2013-08-22 |
+------------+
mysql> select CURDATE() + 0;
+---------------+
| CURDATE() + 0 |
+---------------+
| 20130822 |
+---------------+
mysql> select CURDATE() + 1;
+---------------+
| CURDATE() + 1 |
+---------------+
| 20130823 |
+---------------+
20.CURTIME() == CURRENT_TIME();分為數字性返回和字符串返回
mysql> select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 14:08:37 |
+-----------+
mysql> select CURTIME() + 0.;
+----------------+
| CURTIME() + 0. |
+----------------+
| 140841.000000 |
+----------------+
21.NOW(),UNIX_TIMESTAMP();現在時刻的時間和時間戳
mysql> SELECT NOW();
+---------------------+
| NOW()|
+---------------------+
| 2013-08-22 14:13:59 |
+---------------------+
mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| 1377152057 |
+------------------+
22.FROM_UNIXTIME(unix);將unix時間戳轉為日期
mysql> select FROM_UNIXTIME(1377152057);
+---------------------------+
| FROM_UNIXTIME(1377152057) |
+---------------------------+
| 2013-08-22 14:14:17 |
+---------------------------+
補充
--返回當前時間
mysql> select curdate(),curtime(),now(),DATE(now()),sysdate();
+------------+-----------+---------------------+-------------+---------------------+
| curdate() | curtime() | now() | DATE(now()) | sysdate() |
+------------+-----------+---------------------+-------------+---------------------+
| 2008-12-02 | 10:11:36 | 2008-12-02 10:11:36 | 2008-12-02 | 2008-12-02 10:11:36 |
+------------+-----------+---------------------+-------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT CURDATE(),CURDATE()+0,CURTIME(),CURTIME()+0;
+------------+-------------+-----------+---------------+
| CURDATE() | CURDATE()+0 | CURTIME() | CURTIME()+0 |
+------------+-------------+-----------+---------------+
| 2008-12-02 | 20081202 | 10:00:33 | 100033.000000 |
+------------+-------------+-----------+---------------+
1 row in set (0.00 sec)
--返回日期當月最後一天
mysql> select last_day('2008-12-02');
+------------------------+
| last_day('2008-12-02') |
+------------------------+
| 2008-12-31 |
+------------------------+
1 row in set (0.00 sec)
--返回日期的星期幾
mysql> select dayname('2008-12-02'),dayofweek('2008-12-02');
+-----------------------+-------------------------+
| dayname('2008-12-02') | dayofweek('2008-12-02') |
+-----------------------+-------------------------+
| Tuesday | 3 |
+-----------------------+-------------------------+
1 row in set (0.00 sec)
--返回日期的年，月，日
mysql> select month('2008-12-02'),year('2008-12-02'),day('2008-12-02');
+---------------------+--------------------+-------------------+
| month('2008-12-02') | year('2008-12-02') | day('2008-12-02') |
+---------------------+--------------------+-------------------+
| 12 | 2008 | 2 |
+---------------------+--------------------+-------------------+
1 row in set (0.00 sec)
--返回日期的小時，分，秒
mysql> SELECT HOUR('10:05:03'),MINUTE('10:05:03'),SECOND('10:05:03');
+------------------+--------------------+--------------------+
| HOUR('10:05:03') | MINUTE('10:05:03') | SECOND('10:05:03') |
+------------------+--------------------+--------------------+
| 10 | 5 | 3 |
+------------------+--------------------+--------------------+
1 row in set (0.00 sec)
1.SUBDATE(d,t)：起始時間加上一段時間
--返回起始時間加上N天
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY),ADDDATE('1998-01-02', 31);
+-----------------------------------------+---------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY) | ADDDATE('1998-01-02', 31) |
+-----------------------------------------+---------------------------+
| 1998-02-02 | 1998-02-02 |
+-----------------------------------------+---------------------------+
1 row in set (0.00 sec)
--返回起始時間加上年，月
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 2 YEAR), DATE_ADD('1998-01-02', INTERVAL 2 MONTH);
+-----------------------------------------+------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 2 YEAR) | DATE_ADD('1998-01-02', INTERVAL 2 MONTH) |
+-----------------------------------------+------------------------------------------+
| 2000-01-02 | 1998-03-02 |
+-----------------------------------------+------------------------------------------+
1 row in set (0.00 sec)
--返回起始時間加上小時，加上分鐘
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 2 hour), DATE_ADD('1998-01-02', INTERVAL 2 minute);
+-----------------------------------------+-------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 2 hour) | DATE_ADD('1998-01-02', INTERVAL 2 minute) |
+-----------------------------------------+-------------------------------------------+
| 1998-01-02 02:00:00 | 1998-01-02 00:02:00 |
+-----------------------------------------+-------------------------------------------+
1 row in set (0.00 sec)
2.SUBDATE(d,t)：起始時間減去一段時間
mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY),SUBDATE('1998-01-02', 31);
+----------------------------------------+---------------------------+
| SUBDATE('1998-01-02', INTERVAL 31 DAY) | SUBDATE('1998-01-02', 31) |
+----------------------------------------+---------------------------+
| 1997-12-02 | 1997-12-02 |
+----------------------------------------+---------------------------+
1 row in set (0.00 sec)
3.ADDTIME(d,t):起始時間d加入時間t
mysql> SELECT ADDTIME('1997-12-31 23:59:50','00:00:05'), ADDTIME('23:59:50','00:00:05') ;
+-------------------------------------------+--------------------------------+
| ADDTIME('1997-12-31 23:59:50','00:00:05') | ADDTIME('23:59:50','00:00:05') |
+-------------------------------------------+--------------------------------+
| 1997-12-31 23:59:55 | 23:59:55 |
+-------------------------------------------+--------------------------------+
1 row in set (0.00 sec)
4.SUBTIME(d,t):起始時間d減去時間t
mysql> SELECT SUBTIME('1997-12-31 23:59:50','00:00:05'), SUBTIME('23:59:50','00:00:05');
+-------------------------------------------+--------------------------------+
| SUBTIME('1997-12-31 23:59:50','00:00:05') | SUBTIME('23:59:50','00:00:05') |
+-------------------------------------------+--------------------------------+
| 1997-12-31 23:59:45 | 23:59:45 |
+-------------------------------------------+--------------------------------+
1 row in set (0.00 sec)
5.DATEDIFF(d1,d2):返回起始時間d1和結束時間d2之間的天數
mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
+----------------------------------------------+
| DATEDIFF('1997-12-31 23:59:59','1997-12-30') |
+----------------------------------------------+
| 1 |
+----------------------------------------------+
1 row in set (0.00 sec)
6.DATE_FORMAT(date,format):根據format字符串顯示date值的格式
mysql> SELECT DATE_FORMAT('2008-12-02 22:23:00', '%Y %m %m %H:%i:%s');
+---------------------------------------------------------+
| DATE_FORMAT('2008-12-02 22:23:00', '%Y %m %m %H:%i:%s') |
+---------------------------------------------------------+
| 2008 12 12 22:23:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
7.STR_TO_DATE(str,format) 字符串轉化為時間
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y %H:%i:s');
+-----------------------------------------------+
| STR_TO_DATE('04/31/2004', '%m/%d/%Y %H:%i:s') |
+-----------------------------------------------+
| 2004-04-31 00:00:00 |
+-----------------------------------------------+
1 row in set (0.00 sec)
8.TIMESTAMP(expr) , TIMESTAMP(expr,expr2) :
對於一個單參數,該函數將日期或日期時間表達式 expr 作為日期時間值返回.對於兩個參數, 它將時間表達式 expr2添加到日期或日期時間表達式 expr 中，將theresult作為日期時間值返回
mysql> SELECT TIMESTAMP('2003-12-31'), TIMESTAMP('2003-12-31 12:00:00','12:00:00');
+-------------------------+---------------------------------------------+
| TIMESTAMP('2003-12-31') | TIMESTAMP('2003-12-31 12:00:00','12:00:00') |
+-------------------------+---------------------------------------------+
| 2003-12-31 00:00:00 | 2004-01-01 00:00:00 |
+-------------------------+---------------------------------------------+
1 row in set (0.00 sec)
--取當天0點0分，下一天0點0分
mysql> select timestamp(date(sysdate())),timestamp(adddate(date(sysdate()),1));
+----------------------------+---------------------------------------+
| timestamp(date(sysdate())) | timestamp(adddate(date(sysdate()),1)) |
+----------------------------+---------------------------------------+
| 2008-12-02 00:00:00 | 2008-12-03 00:00:00 |
+----------------------------+---------------------------------------+
1 row in set (0.00 sec)
