【SQL】日付や日時で使うデータ型や関数まとめ(TIMESTAMPDIFF, INTERVAL, DATE_FORMAT)

TIMESTAMPDIFF

TIMESTAMPDIFF関数は、日付同士の差を求める関数。

使い方

TIMESTAMPDIFF(第一引数, 第二引数, 第三引数)
第二引数の日時から
第三引数の日時までの経過時間を
第一引数に与えた単位で返す

例

サンプルデータとして、MySQLにあるEmployees Sample Databaseを使用する。

まず、employeesテーブルの情報を確認のため、10件取得してみる。

SELECT 
    *
FROM
    employees.employees
LIMIT 10;

実行結果

+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+

生年月日の情報から特定の日付までの、日付同士の差を求めて年齢を取得してみる。
今回は、2000年1月1日時点での年齢を取得する。

SELECT 
    *,
    TIMESTAMPDIFF(
        YEAR,
        birth_date,
        '2000-01-01') AS age
FROM
    employees.employees
LIMIT 10;

実行結果

+--------+------------+------------+-----------+--------+------------+-----+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | age |
+--------+------------+------------+-----------+--------+------------+-----+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |  46 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |  35 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |  40 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |  45 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |  44 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |  46 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |  42 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |  41 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |  47 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |  36 |
+--------+------------+------------+-----------+--------+------------+-----+

INTERVAL

INTERVAL型は、日時に対する加算、減算に使用できるデータ型。

使い方

カラム名 + INTERVAL 加算する間隔値 単位
カラム名 - INTERVAL 減算する間隔値 単位

単位と書式

単位	書式
YEAR	年
MONTH	月
WEEK	週
DAY	日
HOUR	時間
MINUTE	分
SECOND	秒
YEAR_MONTH	年-月
DAY_HOUR	日 時間
HOUR_MINUTE	時:分
MINUTE_SECOND	分:秒

例

雇用開始日の情報から5年後のデータを取得する。

SELECT 
    *, (hire_date + INTERVAL 5 YEAR) AS 5_years_later
FROM
    employees.employees
LIMIT 10;

実行結果

+--------+------------+------------+-----------+--------+------------+---------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | 5_years_later |
+--------+------------+------------+-----------+--------+------------+---------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 | 1991-06-26    |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 | 1990-11-21    |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 | 1991-08-28    |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 | 1991-12-01    |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 | 1994-09-12    |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 | 1994-06-02    |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 | 1994-02-10    |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 | 1999-09-15    |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 | 1990-02-18    |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 | 1994-08-24    |
+--------+------------+------------+-----------+--------+------------+---------------+

DATE_FORMAT

DATE_FORMAT関数は、日時のフォーマットを変更する関数。

使い方

DATE_FORMAT(第一引数, 第二引数)
第一引数のデータを
第二引数の表示形式に変更する

フォーマット指定子

指定子	意味
%Y	年(4桁)
%y	年(2桁)
%m	月(2桁)
%c	月(1~2桁)
%d	日(2桁)
%e	日(1~2桁)
%H	時(2桁)
%k	時(1~2桁)
%i	分(2桁)
%s	秒(2桁)
%W	曜日(英語表記)
%w	曜日(数値)

曜日(数値)は、0=日曜日, 1=月曜日, ...のような表記

例

生年月日と雇用開始日の情報を日本語表記にしてみる。

SELECT 
    *,
    DATE_FORMAT(birth_date, '%Y年%c月%e日') AS 生年月日,
    DATE_FORMAT(hire_date, '%Y年%c月%e日') AS 雇用開始日
FROM
    employees.employees
LIMIT 10;

実行結果

+--------+------------+------------+-----------+--------+------------+---------------+----------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | 生年月日  | 雇用開始日 |
+--------+------------+------------+-----------+--------+------------+---------------+----------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 | 1953年9月2日  | 1986年6月26日  |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 | 1964年6月2日  | 1985年11月21日 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 | 1959年12月3日 | 1986年8月28日  |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 | 1954年5月1日  | 1986年12月1日  |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 | 1955年1月21日 | 1989年9月12日  |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 | 1953年4月20日 | 1989年6月2日   |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 | 1957年5月23日 | 1989年2月10日  |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 | 1958年2月19日 | 1994年9月15日  |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 | 1952年4月19日 | 1985年2月18日  |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 | 1963年6月1日  | 1989年8月24日  |
+--------+------------+------------+-----------+--------+------------+---------------+----------------+