程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql經常使用日期時光/數值函數詳解(必看)

mysql經常使用日期時光/數值函數詳解(必看)

編輯：MySQL綜合教程

mysql經常使用日期時光/數值函數詳解(必看)。本站提示廣大學習愛好者：（mysql經常使用日期時光/數值函數詳解(必看)）文章只能為提供參考，不一定能成為您想要的結果。以下是mysql經常使用日期時光/數值函數詳解(必看)正文

1.日期時光函數

時光轉化秒函數:time_to_sec

MySQL> select time_to_sec('01:01:01');
+-------------------------+
| time_to_sec('01:01:01') |
+-------------------------+
| 3661 |
+-------------------------+
1 row in set (0.00 sec)

秒轉化時光函數:sec_to_time

mysql> select sec_to_time(3661);
+-------------------+
| sec_to_time(3661) |
+-------------------+
| 01:01:01 |
+-------------------+
1 row in set (0.00 sec)

日期轉為天數函數:to_days

mysql> select to_days('0000-00-00');
+-----------------------+
| to_days('0000-00-00') |
+-----------------------+
| NULL |
+-----------------------+
1 row in set, 1 warning (0.01 sec)

mysql> select to_days('0001-01-01');
+-----------------------+
| to_days('0001-01-01') |
+-----------------------+
| 366 |
+-----------------------+
1 row in set (0.00 sec)

天數轉化日期函數：from_days

mysql> select from_days(0);
+--------------+
| from_days(0) |
+--------------+
| 0000-00-00 |
+--------------+
1 row in set (0.00 sec)

mysql> select from_days(366);
+----------------+
| from_days(366) |
+----------------+
| 0001-01-01 |
+----------------+
1 row in set (0.00 sec)

字符串轉換為日期函數：str_to_date

mysql> select str_to_date('2013-01-01 01:21:01','%Y-%m-%d %H:%i:%s');
+--------------------------------------------------------+
| str_to_date('2013-01-01 01:21:01','%Y-%m-%d %H:%i:%s') |
+--------------------------------------------------------+
| 2013-01-01 01:21:01 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

日期轉換為字符串函數：date_format

mysql> select date_format('2013-01-01 01:21:01','%Y%m%d %H%i%s');
+----------------------------------------------------+
| date_format('2013-01-01 01:21:01','%Y%m%d %H%i%s') |
+----------------------------------------------------+
| 20130101 012101 |
+----------------------------------------------------+
1 row in set (0.00 sec)

時光轉換為字符串函數：time_format

mysql> select time_format('01:21:01','%H%i%s');
+----------------------------------+
| time_format('01:21:01','%H%i%s') |
+----------------------------------+
| 012101 |
+----------------------------------+
1 row in set (0.00 sec)

解釋：

日期時光格局參數以下：

%M 月名字(January……December)
%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(now());
+-------------+
| date(now()) |
+-------------+
| 2013-05-16 |
+-------------+
1 row in set (0.00 sec)

前往表達式的禮拜索引(0=禮拜一，1=禮拜二, ……6= 禮拜天)。

mysql> select weekday(now());
+----------------+
| weekday(now()) |
+----------------+
| 3 |
+----------------+
1 row in set (0.00 sec)

前往表達式是一年的第幾周

mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
| 19 |
+-------------+
1 row in set (0.00 sec)

WEEK()許可指定禮拜能否開端於禮拜天或禮拜一。假如第二個參數是0，禮拜從禮拜天開端，假如第二個參數是1，從禮拜一開端，以下所示：

mysql> select week(now(),0);
+---------------+
| week(now(),0) |
+---------------+
| 19 |
+---------------+
1 row in set (0.00 sec)

mysql> select week(now(),1);
+---------------+
| week(now(),1) |
+---------------+
| 20 |
+---------------+
1 row in set (0.00 sec)

前往表達式一年中季度

mysql> select quarter(now());
+----------------+
| quarter(now()) |
+----------------+
| 2 |
+----------------+
1 row in set (0.00 sec)

前往表達式一周的第一天

mysql> select dayofweek(now());
+------------------+
| dayofweek(now()) |
+------------------+
| 5 |
+------------------+
1 row in set (0.00 sec)

前往表達式一個月的第幾天

mysql> select dayofmonth(now());
+-------------------+
| dayofmonth(now()) |
+-------------------+
| 16 |
+-------------------+
1 row in set (0.00 sec)

前往表達式一年的第幾天

mysql> select dayofyear(now());
+------------------+
| dayofyear(now()) |
+------------------+
| 136 |
+------------------+
1 row in set (0.00 sec)

前往表達式的禮拜名字

mysql> select dayname(now());
+----------------+
| dayname(now()) |
+----------------+
| Thursday |
+----------------+
1 row in set (0.00 sec)

前往表達式月份的名字

mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| May |
+------------------+
1 row in set (0.00 sec)

mysql>

提取表達式的年份

mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2013 |
+-------------+
1 row in set (0.00 sec)

提取表達式的月份

mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
| 5 |
+--------------+
1 row in set (0.01 sec)

提取表達式的天數

mysql> select day(now());
+------------+
| day(now()) |
+------------+
| 16 |
+------------+
1 row in set (0.00 sec)

提取表達式的小時

mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
| 16 |
+-------------+
1 row in set (0.00 sec)

提取表達式的分鐘

mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
| 31 |
+---------------+
1 row in set (0.00 sec)

提取表達式的秒數

mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
| 34 |
+---------------+
1 row in set (0.00 sec)

將以後日期依照'YYYY-MM-DD' 或YYYYMMDD 格局的值前往，詳細格局依據函數用在字符串或是數字語境中而定。

mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2013-05-16 |
+------------+
1 row in set (0.00 sec)

mysql> select curdate()+1;
+-------------+
| curdate()+1 |
+-------------+
| 20130517 |
+-------------+
1 row in set (0.00 sec)

將以後時光以'HH:MM:SS'或 HHMMSS的格局前往，詳細格局依據函數用在字符串或是數字語境中而定。

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 16:43:10 |
+-----------+
1 row in set (0.00 sec)

mysql> select curtime()+1;
+---------------+
| curtime()+1 |
+---------------+
| 164420.000000 |
+---------------+
1 row in set (0.00 sec)

獲得以後日期時光：sysdate(),now()

mysql> select sysdate(),sleep(2),sysdate();
+---------------------+----------+---------------------+
| sysdate() | sleep(2) | sysdate() |
+---------------------+----------+---------------------+
| 2013-05-16 17:16:04 | 0 | 2013-05-16 17:16:06 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

mysql> select now(),sleep(2),now();
+---------------------+----------+---------------------+
| now() | sleep(2) | now() |
+---------------------+----------+---------------------+
| 2013-05-16 17:16:18 | 0 | 2013-05-16 17:16:18 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)
從下面可以看到sysdate和now的差別，now表現語句開端的時光，而sysdate及時的獲得時光

將以後日期依照'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格局的值前往，詳細格局依據函數用在字符串或是數字語境中而定。

mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2013-05-16 17:19:51 |
+---------------------+
1 row in set (0.00 sec)

mysql> select current_timestamp+1;
+-----------------------+
| current_timestamp+1 |
+-----------------------+
| 20130516172008.000000 |
+-----------------------+
1 row in set (0.00 sec)

unix_timestamp()，unix_timestamp(date)

假如沒有參數挪用，前往一個Unix時光戳記(從'1970-01-01 00:00:00'GMT開端的秒數)。假如UNIX_TIMESTAMP()用一
個date參數被挪用，它前往從'1970-01-01 00:00:00' GMT開端的秒數值。date可所以一個DATE字符串、一個DATETIME
字符串、一個TIMESTAMP或以YYMMDD或YYYYMMDD格局的當地時光的一個數字。
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1368696216 |
+------------------+
1 row in set (0.00 sec)

mysql> select unix_timestamp('2013-05-16 01:01:01');
+---------------------------------------+
| unix_timestamp('2013-05-16 01:01:01') |
+---------------------------------------+
| 1368637261 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql>

FROM_UNIXTIME(unix_timestamp)

以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格局前往unix_timestamp參數所表現的值，詳細格局依據函數用在字符串或是數字語境中而定

mysql> select from_unixtime(1368637261);
+---------------------------+
| from_unixtime(1368637261) |
+---------------------------+
| 2013-05-16 01:01:01 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1368637261)+1;
+-----------------------------+
| from_unixtime(1368637261)+1 |
+-----------------------------+
| 20130516010102.000000 |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select from_unixtime(1368637261,'%Y-%m-%d %h:%i:%s');
+-----------------------------------------------+
| from_unixtime(1368637261,'%Y-%m-%d %h:%i:%s') |
+-----------------------------------------------+
| 2013-05-16 01:01:01 |
+-----------------------------------------------+
1 row in set (0.00 sec)

前往表達式地點月的最初一天

mysql> select last_day(now());
+-----------------+
| last_day(now()) |
+-----------------+
| 2013-05-31 |
+-----------------+
1 row in set (0.00 sec)

日期加減運算

DATE_ADD(date,INTERVAL expr type) --加法
DATE_SUB(date,INTERVAL expr type) --減法

mysql> select date_add('2013-05-16 01:01:01',interval 1 second);
+---------------------------------------------------+
| date_add('2013-05-16 01:01:01',interval 1 second) |
+---------------------------------------------------+
| 2013-05-16 01:01:02 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2013-05-16 01:01:01',interval 1 day);
+------------------------------------------------+
| date_add('2013-05-16 01:01:01',interval 1 day) |
+------------------------------------------------+
| 2013-05-17 01:01:01 |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2013-05-16 01:01:01',interval 1 minute);
+---------------------------------------------------+
| date_add('2013-05-16 01:01:01',interval 1 minute) |
+---------------------------------------------------+
| 2013-05-16 01:02:01 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2013-05-16 01:01:01',interval 1 hour);
+-------------------------------------------------+
| date_add('2013-05-16 01:01:01',interval 1 hour) |
+-------------------------------------------------+
| 2013-05-16 02:01:01 |
+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2013-05-16 01:01:01',interval '1:1' minute_second);
+--------------------------------------------------------------+
| date_add('2013-05-16 01:01:01',interval '1:1' minute_second) |
+--------------------------------------------------------------+
| 2013-05-16 01:02:02 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2013-05-16 01:01:01', interval '1 1:1:1' day_second);
+----------------------------------------------------------------+
| date_add('2013-05-16 01:01:01', interval '1 1:1:1' day_second) |
+----------------------------------------------------------------+
| 2013-05-17 02:02:02 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

type值格局：

SECOND 秒 SECONDS
MINUTE 分鐘 MINUTES
HOUR 時光 HOURS
DAY 天 DAYS
MONTH 月 MONTHS
YEAR 年 YEARS
MINUTE_SECOND 分鐘和秒 "MINUTES:SECONDS"
HOUR_MINUTE 小時和分鐘 "HOURS:MINUTES"
DAY_HOUR 天和小時 "DAYS HOURS"
YEAR_MONTH 年和月 "YEARS-MONTHS"
HOUR_SECOND 小時, 分鐘， "HOURS:MINUTES:SECONDS"
DAY_MINUTE 天, 小時, 分鐘 "DAYS HOURS:MINUTES"
DAY_SECOND 天, 小時, 分鐘, 秒 "DAYS HOURS:MINUTES:SECONDS"

DATEDIFF(expr, expr2)

前往肇端時光 expr和停止時光expr2之間的天數。Expr和expr2為日期或 date-and-time 表達式。盤算中只用到這些值的日期部門。

mysql> select datediff('2013-05-16 06:01:01', '2013-05-17 01:01:01');
+--------------------------------------------------------+
| datediff('2013-05-16 06:01:01', '2013-05-17 01:01:01') |
+--------------------------------------------------------+
| -1 |
+--------------------------------------------------------+
1 row in set (0.00 sec)

表現日期時光的數據類型：

date
time
year
datetime
timestamp

在應用日期時光數據比擬經常用以下

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2013-05-14 17:10:26 |
+---------------------+
1 row in set (0.00 sec)

mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2013-05-14 17:10:49 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from tab where createtime between str_to_date('2013-05-14 00:00:00','%Y-%m-%d %H:%i:%s') and str_to_date('2013-05-15 00:00:00','%Y-%m-%d %H:%i:%s')
-> ;
+------+---------------------+
| name | createtime |
+------+---------------------+
| aaaa | 2013-05-14 17:20:19 |
+------+---------------------+
1 row in set (0.00 sec)

mysql>

2.數值函數

ABS(X) ：前往表達式X的相對值

mysql> select abs(-2);
+---------+
| abs(-2) |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)

FLOOR(X) ：前往不年夜於X的最年夜整數值

mysql> select floor(-2.45);
+--------------+
| floor(-2.45) |
+--------------+
| -3 |
+--------------+
1 row in set (0.00 sec)

MOD(N,M)：模操作，前往N被M除後的余數。

mysql> select mod(3,2);
+----------+
| mod(3,2) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

RAND()/RAND(N) ：前往一個隨機浮點值數a，規模在 0 到1 之間 (即, 其規模為 0 ≤ a ≤ 1.0)。若已指定一個整數參數 N，則它被用作種子值，用來發生反復序列。

mysql> select rand();
+-------------------+
| rand() |
+-------------------+
| 0.294932589209576 |
+-------------------+
1 row in set (0.00 sec)

mysql> select rand(2);
+-------------------+
| rand(2) |
+-------------------+
| 0.655586646549019 |
+-------------------+
1 row in set (0.00 sec)

ROUND(X)/ROUND(X,D) ：前往參數X, 其值接近於比來似的整數。在有兩個參數的情形下，前往 X ，其值保存到小數點後D位，而第D位的保存方法為四捨五入。若要接保存X值小數點右邊的D位，可將D設為負值。

mysql> select round(2.4 );
+-------------+
| round(2.4 ) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)

mysql> select round(2.432,2 );
+-----------------+
| round(2.432,2 ) |
+-----------------+
| 2.43 |
+-----------------+
1 row in set (0.00 sec)

mysql> select round(12.432,-1 );
+-------------------+
| round(12.432,-1 ) |
+-------------------+
| 10 |
+-------------------+
1 row in set (0.00 sec)

以上就是小編為年夜家帶來的mysql經常使用日期時光/數值函數詳解(必看)全體內容了，願望年夜家多多支撐~