当サイトはPR広告を利用しています。

【MySQL】日付型の使い方まとめ【比較・範囲】

2024年11月17日

数値型や文字列型と比較すると使い方が難しい日付型について使い方をまとめます。

日付型とは

MySQLの公式ページでは"日付型"ではなく、"日時データ型"（Date and Time Data Types）と呼んでいるので、ここからは"日時データ型"と呼ばせていただきます。

そもそも日時データ型とは何かを正確に理解している人は少ないと思います。実はMySQLにおいて日時データ型は複数あります。DATE, TIME, DATETIME, TIMESTAMP, YEARの5種類が存在し、それぞれゼロを表す際には以下になります。

データ型「ゼロ」値
DATE'0000-00-00’
TIME’00:00:00′
DATETIME'0000-00-00 00:00:00’
TIMESTAMP'0000-00-00 00:00:00’
YEAR0000
公式ページより引用

ゼロは上記の値で表せますが、TIME, DATETIME, TIMESTAMPの3つの時間を表すデータ型では小数点以下6桁（マイクロ秒）まで記録することが可能です。形式は’00:00:00.000000’です。

DATETIME型とTIMESTAMP型は見た目が同じで差がややこしいので別途説明します。

DATETIME型とTIMESTAMP型の違い

DATETIME型とTIMESTAMP型の一番の大きな違いはタイムゾーンを考慮しているか否かです。DATETIME型ではタイムゾーンを考慮せず、TIMESTAMP型では考慮します。

TIMESTAMP型の場合、データ挿入時にはクライアントで採用されているタイムゾーンを検知して、UTCに変換した上でデータとして保存されます。常にデータ自体はUTCで保存されるということです。そして使うときにはクライアントで採用されているタイムゾーンに合わせて時間が変更されて取得されます。

DATETIME型の場合はタイムゾーンを考慮せず、時間をそのまま格納しそのまま表示します。

差を理解するための例を挙げます。クライアントのタイムゾーン設定がJSTであり標準時刻よりも9時間進んでいるという状態でDATETIME型のカラムとTIMESTAMP型のカラムに同時に’2010/10/10 10:10:10’を格納したら、以下のような状況になります。

型入力格納される値
DATETIME'2010-10-10 10:10:10' JST'2010-10-10 10:10:10'
TIMESTAMP'2010-10-10 10:10:10' JST'2010-10-10 01:10:10'

またその他の違いとして、DATETIME型は8バイト使うのに対し、TIMESTAMP型は半分の4バイトしか使わないことも挙げられます。

ここまでの説明だとTIMESTAMP型を使う方が良さそうですが、TIMESTAMP型は1970年から2038年までの範囲しかサポートしていないことや、タイムゾーンを考慮してくれるが故に意図しない挙動を起こす恐れがあるなど、厄介な点もあります。

違いにをまとめると以下です。

比較項目DATETIME型TIMESTAMP型
タイムゾーン考慮しない考慮する
取れる範囲'1000-01-01 00:00:00' から 
'9999-12-31 23:59:59'		'1970-01-01 00:00:01' UTC から
'2038-01-19 03:14:07' UTC
利用する容量8バイト4バイト

それぞれの型の良い点と悪い点を把握したうえで使い分けましょう。

比較・範囲指定方法

ここからはWHERE句やHAVING句で日時データ型を利用する方法を説明します。

基本

日時データ型を扱うときには’INTERVAL’など特別な予約語を使うことが出来ます。それら特別な用語の使い方は応用編で説明するとして、まずは基本的な比較・範囲指定方法を説明します。

抑えるべきポイントは以下です。

  • 同じフォーマットで比較する
    • 例としてDATE型なら’2024-11-14’、TIMESTAMP型なら’2024-11-14 12:34:56’と比較
  • 数値の比較で利用する演算子を利用可能
    • =（等しい）
    • != または <>（等しくない）
    • <（小さい）
    • >（大きい）
    • <=（小さいか等しい）
    • >=（大きいか等しい）
    • BETWEEN（間）

BETWEENは少し特殊なので記法を説明します。以下のような形で利用し、特定の期間の日時に絞り込みが可能です。

SELECT
    *
FROM
    example_table
WHERE
    date_column BETWEEN '2024-10-01' AND '2024-10-03';

よく覚えておくべき点はBETWEENを使ったときには、以上・以下になるということです。つまり↑のクエリは以下のクエリと同じ意味を持ちます。

SELECT
    *
FROM
    example_table
WHERE
    date_column >= '2024-10-01'
    AND date_column <= '2024-10-03';

基本は以上です。ここまで知っておけば採用面談などで「日時データ型を問題なく扱えます」と言っても嘘にならないレベルになっています。

応用

ここからはMySQLに準備されている予約語や関数を利用して、より高度に日時データ型を扱う方法を紹介していきます。

CURRENT

現在日時を取得するための予約語が多くあります。基本的には"CURRENT_~"の形式で、~には日時データ型を入れれば現在を表す情報を取得できます。クエリ実行例は以下です。

mysql> SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2024-11-15   |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_TIME;
+--------------+
| CURRENT_TIME |
+--------------+
| 01:18:01     |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_TIMESTAMP;
+---------------------+
| CURRENT_TIMESTAMP   |
+---------------------+
| 2024-11-15 01:18:11 |
+---------------------+
1 row in set (0.00 sec)

mysql>

末尾に()を付けても動作しますが、付けない形が一般的なので特別な理由が無ければ付けずに利用しましょう。

mysql> SELECT CURRENT_TIMESTAMP();
+---------------------+
| CURRENT_TIMESTAMP() |
+---------------------+
| 2024-11-15 01:20:56 |
+---------------------+
1 row in set (0.00 sec)

他によく使われる予約語にNOW()があります。こちらは()なしでは動作しません。CURRENT_DATEなどが()なしで動作していたのはシステム定数としても登録されているからです。NOWはシステム定数としては登録されておらず、一種の関数としてのみ存在しているので実行するには()が必要になります。

mysql> SELECT NOW;
ERROR 1054 (42S22): Unknown column 'NOW' in 'field list'

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2024-11-15 01:25:52 |
+---------------------+
1 row in set (0.00 sec)

INTERVAL

MySQLは型に合わせて処理を柔軟に変えてくれます。例えば文字列型のカラムに’2024-11-15’を入れるときには文字列として扱いますが、DATE型のカラムに入れる際には文字列ではなくカラムの型に合わせてDATE型として解釈した上でデータとして保存してくれます。

これと同じことを演算の際にも行います。日時データの演算をする際、日時データ型として演算をするように解釈するための予約語がINTERVALです。使用例は以下です。

# 現在日付の確認
mysql> SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2024-11-15   |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_DATE + INTERVAL 1 DAY;
+-------------------------------+
| CURRENT_DATE + INTERVAL 1 DAY |
+-------------------------------+
| 2024-11-16                    |
+-------------------------------+
1 row in set (0.00 sec)

mysql>

日時データ型が維持されつつ、正しく1日追加された状態になっています。

INTERVALを利用しない場合は以下のように日時データとしての演算は行われず、数値としての計算結果が返ってきています。

mysql> SELECT CURRENT_DATE + 1 DAY;
+----------+
| DAY      |
+----------+
| 20241116 |
+----------+
1 row in set (0.00 sec)

mysql>

この形だとWhere句などで日時データ型の値との比較に利用できなくなってしまいます。

# INTERVALありだとエラーが起きず、ちゃんと日時データとの比較ができる（created_atはTIMESTAMP型）
mysql> SELECT * FROM example_table WHERE created_at < CURRENT_TIMESTAMP + INTERVAL 1 DAY;
+----+---------------------+---------------------+
| id | created_at          | updated_at          |
+----+---------------------+---------------------+
|  1 | 2024-11-17 20:33:51 | 2024-11-17 20:33:51 |
+----+---------------------+---------------------+
1 row in set (0.01 sec)

# INTERVALなしだとエラー
mysql> SELECT * FROM example_table WHERE created_at < CURRENT_TIMESTAMP + 1 DAY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DAY' at line 1
mysql>

演算結果を日時データ型にして想定通りの比較を行えるようにするため、日時データ型の演算をする際にはINTERVALを利用することを忘れないようにしてください。

DATE_ADD, DATE_SUB

日時データの演算で利用します。公式ページに記載されている実行例が分かりやすいので引用します。

mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
        -> '2018-05-02'
mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
        -> '2017-05-01'
mysql> SELECT DATE_ADD('2020-12-31 23:59:59',
    ->                 INTERVAL 1 SECOND);
        -> '2021-01-01 00:00:00'
mysql> SELECT DATE_ADD('2018-12-31 23:59:59',
    ->                 INTERVAL 1 DAY);
        -> '2019-01-01 23:59:59'
mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
    ->                 INTERVAL '1:1' MINUTE_SECOND);
        -> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2025-01-01 00:00:00',
    ->                 INTERVAL '1 1:1:1' DAY_SECOND);
        -> '2024-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
    ->                 INTERVAL '-1 10' DAY_HOUR);
        -> '1899-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
        -> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
    ->            INTERVAL '1.999999' SECOND_MICROSECOND);
        -> '1993-01-01 00:00:01.000001'

ここまでは日時データの演算の際に単純に + を利用していましたが、プロダクトで利用する場合や定期的に実行するクエリの場合にはDATE_ADDやDATE_SUBを使うべきです。

というのもDATE_ADDとDATE_SUBは日時データ型の演算のみを受け付けるという性質上、日時データ型以外の演算を行おうとしたときにエラーを起こしてくれるからです。

mysql> SELECT CURRENT_TIMESTAMP + 1 DAY;
+----------------+
| DAY            |
+----------------+
| 20241117205744 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(CURRENT_TIMESTAMP, 1 DAY);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 DAY)' at line 1

日時データとしての演算しか受け付けないので意図しない挙動を防ぐことが出来ます。例えば日時データとして演算されているはずが、INTERVALの付け忘れにより演算結果がBIGINT型になっており、その結果クエリの出力結果が意図しないものになっている、という事態を事前に防ぐことが出来ます。

つまりバリデーション付きの演算関数です。日時データの演算を行い、結果を日時データとして出力したいときには利用しましょう。

DATE_FORMAT

日時データ型の値を出力したい形にフォーマットするための関数です。DATE_FORMAT(日時データ型の値, '形式’)の形で利用します。形式の個所は%?の形で設定します。よく使われる値が以下です。

指定子説明
%Y年、数字、4 桁
%y年、数字、2 桁
%m月、数字 (00..12)
%d日、数字 (00..31)
%H時間 (00..23)
%h時間 (01..12)
%i分、数字 (00..59)
%s秒数 (00..59)
%T時間、24 時間単位 (hh:mm:ss)
%Y年、数字、4 桁
%y年、数字、2 桁

分を表すのはmやMではなくiなことに注意しましょう。mとMは月を表すことに使われているので、分はminutesの二文字目であるiが利用されているそうです。

公式ページでは曜日など上記の表には載せていないすべての指定子が載っているので、もし上記以外にも指定したいフォーマットがある方は確認してみると良いでしょう。

DATE_FORMATを使ったクエリ例は以下です。

mysql> SELECT DATE_FORMAT(current_timestamp, '%Y-%m-%d');
+--------------------------------------------+
| DATE_FORMAT(current_timestamp, '%Y-%m-%d') |
+--------------------------------------------+
| 2024-11-17                                 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(current_timestamp, '%y-%m-%d');
+--------------------------------------------+
| DATE_FORMAT(current_timestamp, '%y-%m-%d') |
+--------------------------------------------+
| 24-11-17                                   |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(current_timestamp, '%T');
+--------------------------------------+
| DATE_FORMAT(current_timestamp, '%T') |
+--------------------------------------+
| 21:45:22                             |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT(current_timestamp, '%H:%i:%s');
+--------------------------------------------+
| DATE_FORMAT(current_timestamp, '%H:%i:%s') |
+--------------------------------------------+
| 21:46:02                                   |
+--------------------------------------------+
1 row in set (0.00 sec)

DATE_FORMATは特定の月や日付ごとのデータ数を集計するパターンでよく利用されます。以下のようなレコードが格納されたexample_tableを利用して説明します。

mysql> SELECT * FROM example_table;
+----+---------------------+---------------------+
| id | created_at          | updated_at          |
+----+---------------------+---------------------+
|  1 | 2024-11-17 20:33:51 | 2024-11-17 20:33:51 |
|  2 | 2024-11-17 21:17:21 | 2024-11-17 21:17:21 |
|  3 | 2024-11-17 21:17:47 | 2024-11-17 21:17:47 |
+----+---------------------+---------------------+
3 rows in set (0.00 sec)

mysql>

ここでは時間ごとのデータ数を集計します。

mysql> SELECT
    ->   DATE_FORMAT (created_at, '%Y-%m-%d %H'),
    ->   count(*)
    -> FROM
    ->   example_table
    -> GROUP BY
    ->   DATE_FORMAT (created_at, '%Y-%m-%d %H');
+-----------------------------------------+----------+
| DATE_FORMAT (created_at, '%Y-%m-%d %H') | count(*) |
+-----------------------------------------+----------+
| 2024-11-17 20                           |        1 |
| 2024-11-17 21                           |        2 |
+-----------------------------------------+----------+
2 rows in set (0.00 sec)

GROUP BY句でどんな単位で集計したいのかを設定すればOKです。

まとめ

この記事ではMySQLにおけるよく使われる日時データ型の利用方法を紹介させていただきました。日時データ型は奥が深く分かりにくい部分も多々ありますが、SQLの中でも特に重要です。ぜひマスターしてください。

最後まで読んでいただきありがとうございました。

データベースSQL

Posted by ラプラス