トップへ(mam-mam.net/)

MariaDB(MySQL)で使用できる主な日付時刻系関数

検索：

MariaDB(MySQL)で使用できる主な日付時刻系関数

MariaDB(MySQL)で使用できる主な日付時刻系関数をSQLで解説

NOW()
このSQLが実行される日付時刻
SELECT NOW() AS col;
+---------------------+
| col                 |
+---------------------+
| 2024-12-14 12:05:08 |
+---------------------+

SELECT NOW() + 0 AS col;
+----------------+
| col            |
+----------------+
| 20241214120508 |
+----------------+

SELECT NOW() AS col1, SLEEP(1), SYSDATE() AS col2;
+---------------------+----------+---------------------+
| col1                | SLEEP(1) | col2                |
+---------------------+----------+---------------------+
| 2024-12-15 12:23:32 |        0 | 2024-12-15 12:23:33 |
+---------------------+----------+---------------------+
SYSDATE()
このSYSDATE()関数が実行される日付時刻
SELECT SYSDATE() AS col;
+---------------------+
| col                 |
+---------------------+
| 2024-12-14 12:05:08 |
+---------------------+

SELECT SYSDATE() + 0 AS col;
+----------------+
| col            |
+----------------+
| 20241214120508 |
+----------------+

SELECT NOW() AS col1, SLEEP(1), SYSDATE() AS col2;
+---------------------+----------+---------------------+
| col1                | SLEEP(1) | col2                |
+---------------------+----------+---------------------+
| 2024-12-15 12:23:32 |        0 | 2024-12-15 12:23:33 |
+---------------------+----------+---------------------+
CURDATE()
現在の日付を'YYYY-MM-DD'または YYYYMMDD 形式の値として返す。
SELECT CURDATE() AS col;
+------------+
| col        |
+------------+
| 2024-12-14 |
+------------+

SELECT CURDATE() + 0 AS col;
+----------+
| col      |
+----------+
| 20241214 |
+----------+
CURTIME([fsp])
現在の時間を'hh:mm:ss'または hhmmss 形式の値として返す。
fspは秒の小数点以下何桁(0～6)まで出力するかを指定できる。 
SELECT CURTIME() AS col;
+----------+
| col      |
+----------+
| 09:40:42 |
+----------+

SELECT CURTIME(6) AS col;
+-----------------+
| col             |
+-----------------+
| 09:40:42.540114 |
+-----------------+

SELECT CURTIME() + 0 AS col;
+-------+
| col   |
+-------+
| 94042 |
+-------+

SELECT CURTIME(6) + 0 AS col;
+--------------+
| col          |
+--------------+
| 94042.540114 |
+--------------+
DATE(expr)
日付時刻「expr」の「日付」部分を抽出する
SELECT DATE('2024-12-14 09:40:42.540114') AS col;
+------------+
| col        |
+------------+
| 2024-12-14 |
+------------+

SELECT DATE('2024-12-14') AS col;
+------------+
| col        |
+------------+
| 2024-12-14 |
+------------+
TIME(expr)
日付時刻「expr」の「時刻」部分を抽出する
SELECT TIME('2024-12-14 09:40:42.540114') AS col;
+-----------------+
| col             |
+-----------------+
| 09:40:42.540114 |
+-----------------+

SELECT TIME('2024-12-14 09:04') AS col;
+----------+
| col      |
+----------+
| 09:04:00 |
+----------+
YEAR(expr)
日付時刻「expr」の「年」部分を抽出する
SELECT YEAR('2024-12-14 09:40:42.540114') AS col;
+------+
| col  |
+------+
| 2024 |
+------+

SELECT YEAR('2024-12-15') AS col;
+------+
| col  |
+------+
| 2024 |
+------+
MONTH(expr)
日付時刻「expr」の「月」部分を抽出する
SELECT MONTH('2024-12-14 09:40:42.540114') AS col;
+------+
| col  |
+------+
|   12 |
+------+

SELECT MONTH('2024-01-15') AS col;
+------+
| col  |
+------+
|    1 |
+------+
WEEKDAY(expr)
曜日のインデックス（月:0、火:1、水:2、木:3、金:4、土:5、日:6）を返す
ELT()関数と組み合わせると日本語の曜日も出力できる。 
SELECT WEEKDAY('2024-12-14 09:40:42.540114') AS col;
+------+
| col  |
+------+
|    5 |
+------+

SELECT WEEKDAY('2024-12-15') AS col;
+------+
| col  |
+------+
|    6 |
+------+

SELECT ELT(WEEKDAY('2024-12-15')+1, '月','火','水','木','金','土','日') AS col;
+------+
| col  |
+------+
| 日   |
+------+
DAYOFMONTH(expr) 又は DAY(expr)
日付時刻「expr」の「日」部分を抽出する
SELECT DAYOFMONTH('2024-12-14 09:40:42.540114') AS col;
+------+
| col  |
+------+
|   14 |
+------+

SELECT DAYOFMONTH('2024-01-01') AS col;
+------+
| col  |
+------+
|    1 |
+------+
HOUR(expr)
日付時刻「expr」の「時」部分を抽出する
SELECT HOUR('2024-12-14 09:40:42.540114') AS col;
+------+
| col  |
+------+
|    9 |
+------+

SELECT HOUR('2024-01-01 23:59:59') AS col;
+------+
| col  |
+------+
|   23 |
+------+

SELECT HOUR('23:59:59') AS col;
+------+
| col  |
+------+
|   23 |
+------+
MINUTE(expr)
日付時刻「expr」の「分」部分を抽出する
SELECT MINUTE('2024-12-14 09:04:42.540114') AS col;
+------+
| col  |
+------+
|    4 |
+------+

SELECT MINUTE('2024-01-01 23:59:59') AS col;
+------+
| col  |
+------+
|   59 |
+------+

SELECT MINUTE('23:59:59') AS col;
+------+
| col  |
+------+
|   59 |
+------+
SECOND(expr)
日付時刻「expr」の「秒」部分を抽出する
SELECT SECOND('2024-12-14 09:04:01.540114') AS col;
+------+
| col  |
+------+
|    1 |
+------+

SELECT SECOND('23:59:59') AS col;
+------+
| col  |
+------+
|   59 |
+------+
MICROSECOND(expr)
日付時刻「expr」の「マイクロ秒」部分を抽出する
SELECT MICROSECOND('2024-12-14 09:04:01.540') AS col;
+--------+
| col    |
+--------+
| 540000 |
+--------+

SELECT MICROSECOND('23:59:59') AS col;
+------+
| col  |
+------+
|    0 |
+------+
MAKEDATE(year, dayofyear)
「year」年から「dayofyear」日 経過した日付を返す
「dayofyear」は1以上の値を指定する。 
SELECT MAKEDATE(2024, 1) AS col;
+------------+
| col        |
+------------+
| 2024-01-01 |
+------------+

SELECT MAKEDATE(2024, 367) AS col;
+------------+
| col        |
+------------+
| 2025-01-01 |
+------------+
MAKETIME(hour, minute, second)
「hour」時「 minute」分「second」秒の時刻を返す 
SELECT MAKETIME(2, 3, 4.5) AS col;
+------------+
| col        |
+------------+
| 02:03:04.5 |
+------------+

SELECT MAKETIME(23, 59, 59) AS col;
+----------+
| col      |
+----------+
| 23:59:59 |
+----------+
LAST_DAY(expr)
日付時刻「expr」の月に対する最終日の日付を返す 
SELECT LAST_DAY('2024-02-01') AS col;
+------------+
| col        |
+------------+
| 2024-02-29 |
+------------+

SELECT LAST_DAY('2023-02-01 23:59:59') AS col;
+------------+
| col        |
+------------+
| 2023-02-28 |
+------------+
DATE_FORMAT(expr, format)
日付時刻「expr」を書式「format」に設定して返す
指定子説明
%y年2桁
%Y年4桁
%c月1～2桁
%m月2桁
%M英語の月名 (January..December)
%b英語の略月名(Jan..Dec)
%w曜日のインデックス（日:0、月:1、火:2、水:3、木:4、金:5、土:6）
%W英語の曜日名(Sunday..Saturday)
%a英語の略曜日名(Sun..Sat)
%e日1～2桁
%d日2桁
%T24時間制の時分秒 hh:mm:ss
%r12時間制の時分秒 hh:mm:ss にAM又はPM
%k24時間制の時1～2桁
%H24時間制の時2桁
%l12時間制の時1～2桁
%h12時間制の時2桁
%I12時間制の時2桁
%p12時間制の午前(AM)と午後(PM)
%i分2桁
%s秒2桁
%fマイクロ秒6桁
SELECT DATE_FORMAT('2024-01-01 13:1:1', '%Y/%m/%d(%a) %T') AS col;
+--------------------------+
| col                      |
+--------------------------+
| 2024/01/01(Mon) 13:01:01 |
+--------------------------+

SELECT DATE_FORMAT('2024-01-01 13:1:1', '%y/%c/%e(%W) %p%h:%i:%s') AS col;
+---------------------------+
| col                       |
+---------------------------+
| 24/1/1(Monday) PM01:01:01 |
+---------------------------+
STR_TO_DATE(str,format)
DATE_FORMATの逆で、文字列を日付時刻に変換して返す 
SELECT STR_TO_DATE('2024/01/01 13:01:01', '%Y/%m/%d %T') AS col;
+---------------------+
| col                 |
+---------------------+
| 2024-01-01 13:01:01 |
+---------------------+

SELECT STR_TO_DATE('24/1/1 01:01:01 PM', '%y/%c/%e %h:%i:%s %p') AS col;
+---------------------+
| col                 |
+---------------------+
| 2024-01-01 13:01:01 |
+---------------------+
DATE_ADD(date, INTERVAL expr unit) 又は ADDDATE(date, INTERVAL expr unit)
DATE_SUB(date, INTERVAL expr unit) 又は SUBDATE(date, INTERVAL expr unit)
日付演算（日付の加算、日付の減算）を行って返す 
SELECT DATE_ADD('2023-02-01', INTERVAL 28 DAY) AS col;
+------------+
| col        |
+------------+
| 2023-03-01 |
+------------+

SELECT DATE_ADD('2023-02-01', INTERVAL -28 DAY) AS col;
+------------+
| col        |
+------------+
| 2023-01-04 |
+------------+

SELECT DATE_ADD('2023-01-31', INTERVAL 1 MONTH) AS col;
+------------+
| col        |
+------------+
| 2023-02-28 |
+------------+

SELECT DATE_ADD('2024-02-29', INTERVAL -1 YEAR) AS col;
+------------+
| col        |
+------------+
| 2023-02-28 |
+------------+

SELECT DATE_ADD('2024-02-29 23:59:59', INTERVAL 1 HOUR) AS col;
+---------------------+
| col                 |
+---------------------+
| 2024-03-01 00:59:59 |
+---------------------+

SELECT DATE_ADD('2024-02-29 23:59:59', INTERVAL 1 MINUTE) AS col;
+---------------------+
| col                 |
+---------------------+
| 2024-03-01 00:00:59 |
+---------------------+

SELECT DATE_ADD('2024-02-29 23:59:59', INTERVAL 1 SECOND) AS col;
+---------------------+
| col                 |
+---------------------+
| 2024-03-01 00:00:00 |
+---------------------+
ADDTIME(expr1,expr2)
「expr1」から「expr2」で示す日時を足した日付時間を返す 
SELECT ADDTIME('2024-01-01 00:00:01', '2 00:00:02') AS col;
+---------------------+
| col                 |
+---------------------+
| 2024-01-03 00:00:03 |
+---------------------+

SELECT ADDTIME('01:01:01', '00:00:02') AS col;
+----------+
| col      |
+----------+
| 01:01:03 |
+----------+
SUBTIME(expr1,expr2)
「expr1」から「expr2」で示す日時を引いた日付時間を返す 
SELECT SUBTIME('2024-01-01 00:00:01', '2 00:00:02') AS col;
+---------------------+
| col                 |
+---------------------+
| 2023-12-29 23:59:59 |
+---------------------+

SELECT SUBTIME('01:01:01', '00:00:02') AS col;
+----------+
| col      |
+----------+
| 01:00:59 |
+----------+
TIMEDIFF(expr1,expr2)
「expr1」から「expr2」で示す日付時間、又は時間を引いた時間を返す 
SELECT TIMEDIFF('2024-01-01 00:00:00', '2024-01-01 00:00:01') AS col;
+-----------+
| col       |
+-----------+
| -00:00:01 |
+-----------+

SELECT TIMEDIFF('2024-01-02 00:00:00', '2024-01-01 00:00:01') AS col;
+----------+
| col      |
+----------+
| 23:59:59 |
+----------+

SELECT TIMEDIFF('23:00:00', '00:00:01') AS col;
+----------+
| col      |
+----------+
| 22:59:59 |
+----------+
SEC_TO_TIME(second)
秒「second」を hh:mm:ss 形式に変換する 
SELECT SEC_TO_TIME(23*60*60 + 59*60 + 59) AS col;
+----------+
| col      |
+----------+
| 23:59:59 |
+----------+
TIME_TO_SEC(time)
時間「time」を秒に変換する 
SELECT TIME_TO_SEC('23:59:59') AS col;
+-------+
| col   |
+-------+
| 86399 |
+-------+
TO_SECONDS(expr)
日付時刻「expr」を 0年からの通算秒に変換する 
SELECT TO_SECONDS('0000-01-01') AS col;
+-------+
| col   |
+-------+
| 86400 |
+-------+

SELECT TO_SECONDS('0000-01-01 00:00:01') AS col;
+-------+
| col   |
+-------+
| 86401 |
+-------+
FROM_UNIXTIME(expr)
UNIXタイムから日付時刻に変換する
UNIXタイムとは 世界協定時間(UTC)の1970-01-01 00:00:00 からの経過秒数 
SELECT FROM_UNIXTIME(0) AS col;
+---------------------+
| col                 |
+---------------------+
| 1970-01-01 09:00:00 |
+---------------------+
UNIX_TIMESTAMP(expr)
日付時刻からUNIXタイムに変換する 
SELECT UNIX_TIMESTAMP('2000-01-01 01:01:01') AS col;
+-----------+
| col       |
+-----------+
| 946656061 |
+-----------+
MariaDB(MySQL)のサンプルSQL一覧に戻る