mysql函數日期和時間函數彙總

2022-07-26 14:03:25

前言
獲取當前日期的函數
獲取當前時間的函數
獲取當前日期和時間的函數
UNIX時間戳函數
返回UTC日期的函數
返回UTC時間的函數
獲取月份的函數MONTH(date)和MONTHNAME(date)
獲取星期的函數DAYNAME(d)、DAYOFWEEK(d)和WEEKDAY(d)
獲取星期的函數WEEK(d)和WEEKOFYEAR(d)
獲取天數的函數DAYOFYEAR(d)和DAYOFMONTH(d)
獲取年份、季度、小時、分鐘和秒鐘的函數
獲取日期的指定值的函數EXTRACT(type from date)
時間和秒鐘轉換的函數
計算日期和時間的函數
將日期和時間格式化的函數

前言

日期和時間函數主要用來處理日期和時間值，一般的日期函數除了使用date型別的引數外，也可以使用datetime或者timestamp型別的引數，但會忽略這些值的時間部分。

獲取當前日期的函數

curdate()和current_date()函數的作用相同，將當前日期按照yyyy-mm-dd或yyyymmdd格式的值返回。

mysql> select curdate(), current_date(), curdate() + 0;
+------------+----------------+---------------+
| curdate() | current_date() | curdate() + 0 |
+------------+----------------+---------------+
| 2022-07-12 | 2022-07-12 | 20220712 |
+------------+----------------+---------------+
1 row in set (0.00 sec)

mysql>

從上面看到，兩個函數的作用是相同的，都返回了相同的系統當前日期。

curdate()+0將當前日期轉換為數值型。

獲取當前時間的函數

curtime()和current_time()函數的作用相同，將當前時間以hh:mm:ss或者hhmmss的格式返回。

mysql> select curtime(), current_time(), curtime()+0;
+-----------+----------------+-------------+
| curtime() | current_time() | curtime()+0 |
+-----------+----------------+-------------+
| 21:32:25 | 21:32:25 | 213225 |
+-----------+----------------+-------------+
1 row in set (0.00 sec)

mysql>

從上面看到，兩個函數的作用是相同的，都反悔了相同的系統當前時間。

獲取當前日期和時間的函數

current_timestamp()、localtime()、now()和sysdate()這4個函數的作用相同，均為返回當前日期和時間的值。

mysql> select current_timestamp(), localtime(), now(), sysdate();
+---------------------+---------------------+---------------------+---------------------+
| current_timestamp() | localtime() | now() | sysdate() |
+---------------------+---------------------+---------------------+---------------------+
| 2022-07-12 21:34:52 | 2022-07-12 21:34:52 | 2022-07-12 21:34:52 | 2022-07-12 21:34:52 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

從上面看到，4個函數返回的結果是一樣的。

UNIX時間戳函數

UNIX_TIMESTAMP(date)若無引數呼叫，則返回一個UNIX時間戳（‘1970-01-01 00:00:00’GMT之後的秒數）作為無符號整數。

其中，GMT（Green wich mean time）為格林尼治標準時間。若用date來呼叫UNIX_TIMESTAMP()，它會將引數值以1970-01-0100:00:00GMT後的秒數的形式返回。

date可以是一個DATE字串、DATETIME字串、TIMESTAMP或一個當地時間的YYMMDD或YYYYMMDD格式的數位。

mysql> select unix_timestamp(), unix_timestamp(now()), now();
+------------------+-----------------------+---------------------+
| unix_timestamp() | unix_timestamp(now()) | now() |
+------------------+-----------------------+---------------------+
| 1657633074 | 1657633074 | 2022-07-12 21:37:54 |
+------------------+-----------------------+---------------------+
1 row in set (0.00 sec)

mysql>

from_unixtime(date)函數把unix時間戳轉換為普通格式時間，與unix_timestamp(date)函數互為反函數。範例：

mysql> select from_unixtime('1657633074');
+-----------------------------+
| from_unixtime('1657633074') |
+-----------------------------+
| 2022-07-12 21:37:54.000000 |
+-----------------------------+
1 row in set (0.00 sec)

mysql>

返回UTC日期的函數

utc_date()函數返回當前utc(世界標準時間)日期值，其格式為yyyy-mm-dd或者yyyymmdd；

mysql> select utc_date(), utc_date()+0;
+------------+--------------+
| utc_date() | utc_date()+0 |
+------------+--------------+
| 2022-07-12 | 20220712 |
+------------+--------------+
1 row in set (0.00 sec)

mysql>

返回UTC時間的函數

utc_time()返回當前utc時間值，格式為hh:mm:ss或者hhmmss；

mysql> select utc_time(), utc_time()+0;
+------------+--------------+
| utc_time() | utc_time()+0 |
+------------+--------------+
| 13:43:24 | 134324 |
+------------+--------------+
1 row in set (0.00 sec)

mysql>

獲取月份的函數MONTH(date)和MONTHNAME(date)

month(date)函數返回date對應的月份。

mysql> select month('2022-12-12') as coll, month('20221212') as coll_1, month('221212') as coll_2;
+------+--------+--------+
| coll | coll_1 | coll_2 |
+------+--------+--------+
| 12 | 12 | 12 |
+------+--------+--------+
1 row in set (0.00 sec)

mysql>

monthname(date)函數返回日期date對應月份的英文全名；

mysql> select monthname('2022-12-12'), monthname('20221212'), monthname('221212');
+-------------------------+-----------------------+---------------------+
| monthname('2022-12-12') | monthname('20221212') | monthname('221212') |
+-------------------------+-----------------------+---------------------+
| December | December | December |
+-------------------------+-----------------------+---------------------+
1 row in set (0.00 sec)

mysql>

獲取星期的函數DAYNAME(d)、DAYOFWEEK(d)和WEEKDAY(d)

dayname(d)函數返回d對應的工作日的英文名稱；

mysql> select dayname('2022-07-12');
+-----------------------+
| dayname('2022-07-12') |
+-----------------------+
| Tuesday |
+-----------------------+
1 row in set (0.00 sec)

mysql>

dayofweek(d)函數返回d對應的一週中的索引（位置，1表示週日，2表示週一，以此類推，7表示週六）；

mysql> select dayofweek('2022-07-12') as coll, dayofweek('2022-07-13') as coll_1;
+------+--------+
| coll | coll_1 |
+------+--------+
| 3 | 4 |
+------+--------+
1 row in set (0.00 sec)

mysql>

weekday(d)返回d對應的工作日索引：0表示週一，1表示週二，以此類推，6表示週日。

mysql> select weekday('2022-07-12') as coll, weekday('2022-07-13') as coll_1;
+------+--------+
| coll | coll_1 |
+------+--------+
| 1 | 2 |
+------+--------+
1 row in set (0.00 sec)

mysql>

獲取星期的函數WEEK(d)和WEEKOFYEAR(d)

week(d)計算日期d是一年中的第幾周。

week()的雙引數形式允許指定該星期是否始於週日或者週一，以及返回值的範圍是否為0~53或者1~53.若mode引數被省略，則使用default_week_format系統自變數的值，如下圖：

例如：使用week()函數查詢指定日期是一年中的第幾周；

mysql> select week('2022-07-13'), week('2022-01-01'), week('2022-09-18');
+--------------------+--------------------+--------------------+
| week('2022-07-13') | week('2022-01-01') | week('2022-09-18') |
+--------------------+--------------------+--------------------+
| 28 | 0 | 38 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)

mysql>

weekofyear(d)計算某一天位於一年中的第幾周，範圍是1~53，相當於week(d,3)；

例如：使用weekofyear(d)查詢指定日期是一年中的第幾周；

mysql> select weekofyear('2022-07-13'), week('2022-07-13', 3);
+--------------------------+-----------------------+
| weekofyear('2022-07-13') | week('2022-07-13', 3) |
+--------------------------+-----------------------+
| 28 | 28 |
+--------------------------+-----------------------+
1 row in set (0.00 sec)

mysql>

從上面可以看出，兩個函數返回結果相同。

獲取天數的函數DAYOFYEAR(d)和DAYOFMONTH(d)

dayofyear(d)函數返回d是一年中的第幾天，範圍是1~366；

例如：使用dayofyear()函數返回指定日期在一年中的位置；

mysql> select dayofyear('2022-07-13'), dayofyear('2022-01-01');
+-------------------------+-------------------------+
| dayofyear('2022-07-13') | dayofyear('2022-01-01') |
+-------------------------+-------------------------+
| 194 | 1 |
+-------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

dayofmonth(d)函數返回d是一個月中的第幾天，範圍是1~31；

例如：使用dayofmonth()函數返回指定日期在一個月中的位置，必須有具體年份才可以。

mysql> select dayofmonth('2022-07-13'), dayofmonth('220713'), dayofmonth('0713');
+--------------------------+----------------------+--------------------+
| dayofmonth('2022-07-13') | dayofmonth('220713') | dayofmonth('0713') |
+--------------------------+----------------------+--------------------+
| 13 | 13 | NULL |
+--------------------------+----------------------+--------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

獲取年份、季度、小時、分鐘和秒鐘的函數

year(date)返回date對應的年份，範圍是1970~2069；

mysql> select year('2022-07-13'), year('20330909');
+--------------------+------------------+
| year('2022-07-13') | year('20330909') |
+--------------------+------------------+
| 2022 | 2033 |
+--------------------+------------------+
1 row in set (0.00 sec)

mysql>

小提示：

00~69轉換為2000~2069，70~99轉換為1970~1999。

quarter(date)返回date對應的一年中的季度值，範圍是1~4；

mysql> select quarter('2022-07-13'), quarter('20330101');
+-----------------------+---------------------+
| quarter('2022-07-13') | quarter('20330101') |
+-----------------------+---------------------+
| 3 | 1 |
+-----------------------+---------------------+
1 row in set (0.00 sec)

mysql>

minute(time)返回time對應的分鐘數，範圍是0~59。

mysql> select minute('2022-07-13 09:09:09'), minute('06:06:06');
+-------------------------------+--------------------+
| minute('2022-07-13 09:09:09') | minute('06:06:06') |
+-------------------------------+--------------------+
| 9 | 6 |
+-------------------------------+--------------------+
1 row in set (0.00 sec)

mysql>

second(time)返回time對應的秒數，範圍是0~59。

mysql> select second('2022-07-13 09:09:09'), second('06:06:06');
+-------------------------------+--------------------+
| second('2022-07-13 09:09:09') | second('06:06:06') |
+-------------------------------+--------------------+
| 9 | 6 |
+-------------------------------+--------------------+
1 row in set (0.00 sec)

mysql>

獲取日期的指定值的函數EXTRACT(type from date)

extract(type from date)函數所使用的時間間隔型別說明符與date_add()或date_sub()相同，但它從日期中提取一部分，而不是執行日期運算。

mysql> select extract(year from '2022-07-13') as coll, extract(year_month from '2022-07-13') as coll_1, extract(day_minute from '2022-07-13 09:08:07') as coll_2;
+------+--------+--------+
| coll | coll_1 | coll_2 |
+------+--------+--------+
| 2022 | 202207 | 130908 |
+------+--------+--------+
1 row in set (0.00 sec)

mysql>

type為不同值時，返回的值不同：

year：只返回年值
year_month：返回年與月份
day_minute：返回日、小時和分鐘值

時間和秒鐘轉換的函數

time_to_sec(time)返回已轉化為秒的time引數。

轉換公式為：小時*3600+分鐘*60+秒

mysql> select time_to_sec('09:09:09');
+-------------------------+
| time_to_sec('09:09:09') |
+-------------------------+
| 32949 |
+-------------------------+
1 row in set (0.00 sec)

mysql>

sec_to_time(seconds)返回被轉化為小時、分鐘和秒數的seconds引數值，其格式為hh:mm:ss或者hhmmss。

mysql> select time_to_sec('09:09:09'), sec_to_time(32949);
+-------------------------+--------------------+
| time_to_sec('09:09:09') | sec_to_time(32949) |
+-------------------------+--------------------+
| 32949 | 09:09:09 |
+-------------------------+--------------------+
1 row in set (0.00 sec)

mysql>

從上面可以看到，time_to_sec和sec_to_time互為反函數。

計算日期和時間的函數

計算日期和時間的函數有：

date_add()
adddate()
date_sub()
subdate()
addtime()
subtime()
date_diff()

在date_add(date, interval expr type)和date_sub(date, interval expr type)中：

date是一個datetime或者date值，用來指定起始時間。
expr是一個表示式，用來指定從起始日期新增或減去的時間間隔值。對於負值的時間間隔，expr可以以一個符號-開頭。
type為關鍵詞，指示了表示式被解釋的方式。

若date引數是一個date值，計算只會包含year、month和day部分（沒有時間部分），其結果是一個date值，否則，結果將是一個datetime值。

date_add(date, interval expr type)和adddate(date, intervar expr type)兩個函數的作用相同，執行日期的加運算。

mysql> select date_add('2022-07-13 09:09:09', interval 1 second) as coll, adddate('2022-07-13 09:09:09', interval 1 second) as coll_1, date_add('2022-07-13 09:09:09', interval '1:1' minute_second) as coll_2;
+---------------------+---------------------+---------------------+
| coll | coll_1 | coll_2 |
+---------------------+---------------------+---------------------+
| 2022-07-13 09:09:10 | 2022-07-13 09:09:10 | 2022-07-13 09:10:10 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

date_sub(date, interval expr type)或者subdate(date, interval expr type)兩個函數的作用相同，執行日期的減運算。

mysql> select date_sub('2022-07-13 09:09:09', interval 31 day) as coll, subdate('2022-07-13 09:09:09', interval 31 day) as coll_1, date_sub('2022-07-13 09:09:09', interval '0 0:1:1' day_second) as coll_2;
+---------------------+---------------------+---------------------+
| coll | coll_1 | coll_2 |
+---------------------+---------------------+---------------------+
| 2022-06-12 09:09:09 | 2022-06-12 09:09:09 | 2022-07-13 09:08:08 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

addtime(date, expr)函數將expr值新增到date，並返回修改後的值，date值一個日期或者日期時間表示式，而expr是一個時間表示式。

mysql> select adddate('2022-07-13 09:09:09', '1:1:1'), addtime('09:09:09', '1:1:20');
+-----------------------------------------+-------------------------------+
| adddate('2022-07-13 09:09:09', '1:1:1') | addtime('09:09:09', '1:1:20') |
+-----------------------------------------+-------------------------------+
| 2022-07-14 09:09:09 | 10:10:29 |
+-----------------------------------------+-------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

subtime(date,expr)函數將date減去expr值，並返回修改後的值。

其中，date是一個日期或者日期時間表示式，而expr是一個時間表示式。

mysql> select subtime('2020-07-13 09:09:09', '1:1:1 1:1:1'), subtime('2022-07-13 09:09:09', '1:1:1');
+-----------------------------------------------+-----------------------------------------+
| subtime('2020-07-13 09:09:09', '1:1:1 1:1:1') | subtime('2022-07-13 09:09:09', '1:1:1') |
+-----------------------------------------------+-----------------------------------------+
| 2020-07-13 08:08:08 | 2022-07-13 08:08:08 |
+-----------------------------------------------+-----------------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql>

從上面可以看到，只計算了時間，日期並沒有計算進去。

使用datediff()函數計算兩個日期之間的間隔天數；

mysql> select datediff('2022-07-13 09:09:09', '2022-01-01') as coll, datediff('2022-07-13 09:09:09', '2022-07-10 08:08:08');
+------+--------------------------------------------------------+
| coll | datediff('2022-07-13 09:09:09', '2022-07-10 08:08:08') |
+------+--------------------------------------------------------+
| 193 | 3 |
+------+--------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

將日期和時間格式化的函數

date_format(date,format)根據format指定的格式顯示date值。

主要format格式如下圖：

範例：使用date_format()函數格式化輸出日期和時間值；

mysql> select date_format('2022-07-13 09:08:07', '%W %M %Y') as coll, date_format('2022-07-13 09:08:07', '%D %y %a %d %m %b %j') as coll_1;
+---------------------+---------------------------+
| coll | coll_1 |
+---------------------+---------------------------+
| Wednesday July 2022 | 13th 22 Wed 13 07 Jul 194 |
+---------------------+---------------------------+
1 row in set (0.00 sec)

mysql>

time_format(time, format)根據表示式format的要求顯示時間time 。

表示式format指定了顯示的格式。因為time_format(time, format)只處理時間，所以format只使用時間格式。

範例：使用time_format()函數格式化輸入時間值。

mysql> select time_format('13:14:15', '%H %k %h %I %l');
+-------------------------------------------+
| time_format('13:14:15', '%H %k %h %I %l') |
+-------------------------------------------+
| 13 13 01 01 1 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>

get_format(val_typr, format_type)返回日期時間字串的顯示格式：

val_type表示日期資料型別，包括date、datetime和time；
format_type表示格式化顯示型別，包括eur、interval、iso、jis、usa。

get_format根據兩個值型別組合返回的字串顯示格式如下：

範例：使用get_format()函數顯示不同格式化型別下的格式字串。

mysql> select get_format(date, 'eur'), get_format(datetime, 'jis');
+-------------------------+-----------------------------+
| get_format(date, 'eur') | get_format(datetime, 'jis') |
+-------------------------+-----------------------------+
| %d.%m.%Y | %Y-%m-%d %H:%i:%s |
+-------------------------+-----------------------------+
1 row in set (0.00 sec)

mysql>
mysql> select date_format('2022-07-13 09:08:07', get_format(datetime, 'jis')) as coll, date_format('2022-07-13 09:08:07', get_format(date, 'usa')) as coll_1;
+---------------------+------------+
| coll | coll_1 |
+---------------------+------------+
| 2022-07-13 09:08:07 | 07.13.2022 |
+---------------------+------------+
1 row in set (0.00 sec)

mysql>

到此這篇關於mysql函數日期和時間函數彙總的文章就介紹到這了,更多相關mysql日期函數內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！