Go to Qiita Advent Calendar 2024 Top
LoginSignup
11
11

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@niwasawa

MySQL の DATETIME 型と TIMESTAMP 型を範囲検索するサンプル SQL 文

Last updated at Posted at 2020-07-05

概要

  • MySQL の DATETIME 型と TIMESTAMP 型を範囲検索するサンプル SQL 文を示す
  • 今回の環境: macOS Catalina + MySQL 8.0.19 (Homebrew でインストールしたもの)

MySQL の DATETIME 型と TIMESTAMP 型

DATETIME 型

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.1 DATE、DATETIME、および TIMESTAMP 型

DATETIME 型は、日付と時間の両方の部分を含む値に使用されます。MySQL では、DATETIME 値の取得と表示は 'YYYY-MM-DD HH:MM:SS' 形式で行われます。サポートしている範囲は '1000-01-01 00:00:00' から '9999-12-31 23:59:59' です。

TIMESTAMP 型

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.1 DATE、DATETIME、および TIMESTAMP 型

TIMESTAMP データ型は、日付と時間の両方の部分を含む値に使用されます。TIMESTAMP には、'1970-01-01 00:00:01' UTC から '2038-01-19 03:14:07' UTC の範囲があります。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.1 DATE、DATETIME、および TIMESTAMP 型

MySQL は、TIMESTAMP 値を、ストレージでは現在のタイムゾーンを UTC に変換し、取得では UTC から現在のタイムゾーンに戻します。(DATETIME などのほかの型ではこれは行われません。)デフォルトでは、接続ごとの現在のタイムゾーンはサーバーの時間です。

DATETIME 型と TIMESTAMP 型はマイクロ秒までの精度で小数秒部分を持つことが可能

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.1 DATE、DATETIME、および TIMESTAMP 型

DATETIME または TIMESTAMP 値には、マイクロ秒 (6 桁) までの精度で後続の小数秒部分を含めることができます。特に、MySQL 5.6.4 以降では、DATETIME または TIMESTAMP カラムに挿入された値の小数部はすべて破棄されずに格納されます。小数部が含まれる場合、これらの値の形式は 'YYYY-MM-DD HH:MM:SS[.fraction]' であり、DATETIME 値の範囲は '1000-01-01 00:00:00.000000' から '9999-12-31 23:59:59.999999' であり、TIMESTAMP 値の範囲は '1970-01-01 00:00:01.000000' から '2038-01-19 03:14:07.999999' です。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.3.6 時間値での小数秒

小数秒部を含むカラムを定義するには、type_name(fsp) の構文を使用します。ここで、type_name は TIME、DATETIME、または TIMESTAMP であり、fsp は小数秒の精度です。

例: CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

fsp 値を指定する場合、0 から 6 の範囲にする必要があります。0 の値は、小数部がないことを表します。省略した場合、デフォルトの精度は 0 です。(これは、以前の MySQL バージョンと互換性を保つため、標準 SQL のデフォルトである 6 とは異なっています。)

BETWEEN での範囲検索は指定した値を含む結果が返る

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.3.2 比較関数と演算子

expr BETWEEN min AND max

expr が min より多いか等しく、expr が max より少ないか等しい場合、BETWEEN は 1 を返し、それ以外では 0 を返します。すべての引数の型が同じであれば、これは式 (min <= expr AND expr <= max) と同等です。

マイクロ秒精度の DATETIME 型で秒までしか指定しない場合は小数秒部分が0になる

cast 関数で確認することができる。
以下の例では 2020-04-01 23:59:59 が 2020-04-01 23:59:59.000000 に変換されている。

mysql> select cast('2020-04-01 23:59:59' as datetime(6));
+--------------------------------------------+
| cast('2020-04-01 23:59:59' as datetime(6)) |
+--------------------------------------------+
| 2020-04-01 23:59:59.000000                 |
+--------------------------------------------+
1 row in set (0.00 sec)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.10 キャスト関数と演算子

CAST(expr AS type)

CONVERT() と同様に、CAST() 関数には任意の型の式が指定され、指定された型の結果値が生成されます。

範囲検索するサンプル

DATETIME 型を範囲検索するサンプル

検索対象となるテーブルを作成し検索用データを追加する。

mysql> create table t1 (id int not null auto_increment, dt datetime, primary key (id));
Query OK, 0 rows affected (0.06 sec)

mysql> show columns from t1;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int      | NO   | PRI | NULL    | auto_increment |
| dt    | datetime | YES  |     | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into t1 (dt) values
    ->   (NULL),
    ->   ('0000-00-00 00:00:00'),
    ->   ('2020-04-01 00:00:00'),
    ->   ('2020-04-01 23:59:59'),
    ->   ('2020-04-02 00:00:00');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+---------------------+
| id | dt                  |
+----+---------------------+
|  1 | NULL                |
|  2 | 0000-00-00 00:00:00 |
|  3 | 2020-04-01 00:00:00 |
|  4 | 2020-04-01 23:59:59 |
|  5 | 2020-04-02 00:00:00 |
+----+---------------------+
5 rows in set (0.00 sec)

2020-04-01 のデータだけ検索したい。

between 演算子で検索する。

mysql> select * from t1 where dt between '2020-04-01 00:00:00' and '2020-04-01 23:59:59';
+----+---------------------+
| id | dt                  |
+----+---------------------+
|  3 | 2020-04-01 00:00:00 |
|  4 | 2020-04-01 23:59:59 |
+----+---------------------+
2 rows in set (0.02 sec)

小なりイコール演算子 (Less than or equal operator) で検索する。

mysql> select * from t1 where '2020-04-01 00:00:00' <= dt and dt <= '2020-04-01 23:59:59';
+----+---------------------+
| id | dt                  |
+----+---------------------+
|  3 | 2020-04-01 00:00:00 |
|  4 | 2020-04-01 23:59:59 |
+----+---------------------+
2 rows in set (0.00 sec)

小なりイコール演算子 (Less than or equal operator)と、小なり演算子 (Less than operator) で検索する。

mysql> select * from t1 where '2020-04-01 00:00:00' <= dt and dt < '2020-04-02 00:00:00';
+----+---------------------+
| id | dt                  |
+----+---------------------+
|  3 | 2020-04-01 00:00:00 |
|  4 | 2020-04-01 23:59:59 |
+----+---------------------+
2 rows in set (0.02 sec)

DATETIME 型 (小数秒部分あり) を範囲検索するサンプル

検索対象となるテーブルを作成し検索用データを追加する。
マイクロ秒までの精度に対応するため、DATETIME 型の小数秒の精度に 6 を指定する。

mysql> create table t2 (id int not null auto_increment, dt datetime(6), primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql> show columns from t2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| dt    | datetime(6) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into t2 (dt) values
    ->   (NULL),
    ->   ('0000-00-00 00:00:00.000000'),
    ->   ('2020-04-01 00:00:00.000000'),
    ->   ('2020-04-01 23:59:59.000000'),
    ->   ('2020-04-01 23:59:59.999999'),
    ->   ('2020-04-02 00:00:00.000000');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+----+----------------------------+
| id | dt                         |
+----+----------------------------+
|  1 | NULL                       |
|  2 | 0000-00-00 00:00:00.000000 |
|  3 | 2020-04-01 00:00:00.000000 |
|  4 | 2020-04-01 23:59:59.000000 |
|  5 | 2020-04-01 23:59:59.999999 |
|  6 | 2020-04-02 00:00:00.000000 |
+----+----------------------------+
6 rows in set (0.00 sec)

2020-04-01 のデータだけ検索したい。

between 演算子で秒まで指定して検索すると、小数秒部分を取りこぼしてしまう。

mysql> select * from t2 where dt between '2020-04-01 00:00:00' and '2020-04-01 23:59:59';
+----+----------------------------+
| id | dt                         |
+----+----------------------------+
|  3 | 2020-04-01 00:00:00.000000 |
|  4 | 2020-04-01 23:59:59.000000 |
+----+----------------------------+
2 rows in set (0.00 sec)

between 演算子で小数秒まで指定して検索すれば小数秒部分を取りこぼさない。
ただし、データの精度を把握していないと指定する値を決めるのが難しい。

mysql> select * from t2 where dt between '2020-04-01 00:00:00' and '2020-04-01 23:59:59.999999';
+----+----------------------------+
| id | dt                         |
+----+----------------------------+
|  3 | 2020-04-01 00:00:00.000000 |
|  4 | 2020-04-01 23:59:59.000000 |
|  5 | 2020-04-01 23:59:59.999999 |
+----+----------------------------+
3 rows in set (0.00 sec)

小なりイコール演算子 (Less than or equal operator)と、小なり演算子 (Less than operator) で検索する。

mysql> select * from t2 where '2020-04-01 00:00:00' <= dt and dt < '2020-04-02 00:00:00';
+----+----------------------------+
| id | dt                         |
+----+----------------------------+
|  3 | 2020-04-01 00:00:00.000000 |
|  4 | 2020-04-01 23:59:59.000000 |
|  5 | 2020-04-01 23:59:59.999999 |
+----+----------------------------+
3 rows in set (0.00 sec)

DATETIME 型のカラムの値を DATE 型にキャストして日付のみで一致するものを検索する。

mysql> select * from t2 where cast(dt as date) = '2020-04-01';
+----+----------------------------+
| id | dt                         |
+----+----------------------------+
|  3 | 2020-04-01 00:00:00.000000 |
|  4 | 2020-04-01 23:59:59.000000 |
|  5 | 2020-04-01 23:59:59.999999 |
+----+----------------------------+
3 rows in set (0.00 sec)

between 演算子で大きめの範囲を指定して検索し、除外したい値を不等価演算子 (Not equal operator) で除外する。

mysql> select * from t2 where dt between '2020-04-01 00:00:00' and '2020-04-02 00:00:00' and dt != '2020-04-02 00:00:00';
+----+----------------------------+
| id | dt                         |
+----+----------------------------+
|  3 | 2020-04-01 00:00:00.000000 |
|  4 | 2020-04-01 23:59:59.000000 |
|  5 | 2020-04-01 23:59:59.999999 |
+----+----------------------------+
3 rows in set (0.00 sec)

TIMESTAMP 型を範囲検索するサンプル

検索対象となるテーブルを作成し検索用データを追加する。

mysql> create table t3 (id int not null auto_increment, ts timestamp, primary key (id));
Query OK, 0 rows affected (0.02 sec)

mysql> show columns from t3;
+-------+-----------+------+-----+---------+----------------+
| Field | Type      | Null | Key | Default | Extra          |
+-------+-----------+------+-----+---------+----------------+
| id    | int       | NO   | PRI | NULL    | auto_increment |
| ts    | timestamp | YES  |     | NULL    |                |
+-------+-----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into t3 (ts) values
    ->   (NULL),
    ->   ('0000-00-00 00:00:00'),
    ->   ('2020-04-01 00:00:00'),
    ->   ('2020-04-01 23:59:59'),
    ->   ('2020-04-02 00:00:00');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from t3;
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  1 | NULL                |
|  2 | 0000-00-00 00:00:00 |
|  3 | 2020-04-01 00:00:00 |
|  4 | 2020-04-01 23:59:59 |
|  5 | 2020-04-02 00:00:00 |
+----+---------------------+
5 rows in set (0.00 sec)

2020-04-01 のデータだけ検索したい。

between 演算子で検索する。

mysql> select * from t3 where ts between '2020-04-01 00:00:00' and '2020-04-01 23:59:59';
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  3 | 2020-04-01 00:00:00 |
|  4 | 2020-04-01 23:59:59 |
+----+---------------------+
2 rows in set (0.00 sec)

小なりイコール演算子 (Less than or equal operator) で検索する。

mysql> select * from t3 where '2020-04-01 00:00:00' <= ts and ts <= '2020-04-01 23:59:59';
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  3 | 2020-04-01 00:00:00 |
|  4 | 2020-04-01 23:59:59 |
+----+---------------------+
2 rows in set (0.00 sec)

小なりイコール演算子 (Less than or equal operator)と、小なり演算子 (Less than operator) で検索する。

mysql> select * from t3 where '2020-04-01 00:00:00' <= ts and ts < '2020-04-02 00:00:00';
+----+---------------------+
| id | ts                  |
+----+---------------------+
|  3 | 2020-04-01 00:00:00 |
|  4 | 2020-04-01 23:59:59 |
+----+---------------------+
2 rows in set (0.00 sec)

TIMESTAMP 型 (小数秒部分あり) を範囲検索するサンプル

検索対象となるテーブルを作成し検索用データを追加する。
マイクロ秒までの精度に対応するため、TIMESTAMP 型の小数秒の精度に 6 を指定する。

mysql> create table t4 (id int not null auto_increment, ts timestamp(6), primary key (id));
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from t4;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| ts    | timestamp(6) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into t4 (ts) values
    ->   (NULL),
    ->   ('0000-00-00 00:00:00.000000'),
    ->   ('2020-04-01 00:00:00.000000'),
    ->   ('2020-04-01 23:59:59.000000'),
    ->   ('2020-04-01 23:59:59.999999'),
    ->   ('2020-04-02 00:00:00.000000');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t4;
+----+----------------------------+
| id | ts                         |
+----+----------------------------+
|  1 | NULL                       |
|  2 | 0000-00-00 00:00:00.000000 |
|  3 | 2020-04-01 00:00:00.000000 |
|  4 | 2020-04-01 23:59:59.000000 |
|  5 | 2020-04-01 23:59:59.999999 |
|  6 | 2020-04-02 00:00:00.000000 |
+----+----------------------------+
6 rows in set (0.00 sec)

2020-04-01 のデータだけ検索したい。

between 演算子で秒まで指定して検索すると、小数秒部分を取りこぼしてしまう。

mysql> select * from t4 where ts between '2020-04-01 00:00:00' and '2020-04-01 23:59:59';
+----+----------------------------+
| id | ts                         |
+----+----------------------------+
|  3 | 2020-04-01 00:00:00.000000 |
|  4 | 2020-04-01 23:59:59.000000 |
+----+----------------------------+
2 rows in set (0.00 sec)

between 演算子で小数秒まで指定して検索すれば小数秒部分を取りこぼさない。
ただし、データの精度を把握していないと指定する値を決めるのが難しい。

mysql> select * from t4 where ts between '2020-04-01 00:00:00' and '2020-04-01 23:59:59.999999';
+----+----------------------------+
| id | ts                         |
+----+----------------------------+
|  3 | 2020-04-01 00:00:00.000000 |
|  4 | 2020-04-01 23:59:59.000000 |
|  5 | 2020-04-01 23:59:59.999999 |
+----+----------------------------+
3 rows in set (0.00 sec)

小なりイコール演算子 (Less than or equal operator)と、小なり演算子 (Less than operator) で検索する。

mysql> select * from t4 where '2020-04-01 00:00:00' <= ts and ts < '2020-04-02 00:00:00';
+----+----------------------------+
| id | ts                         |
+----+----------------------------+
|  3 | 2020-04-01 00:00:00.000000 |
|  4 | 2020-04-01 23:59:59.000000 |
|  5 | 2020-04-01 23:59:59.999999 |
+----+----------------------------+
3 rows in set (0.00 sec)

TIMESTAMP 型のカラムの値を DATE 型にキャストして日付のみで一致するものを検索する。

mysql> select * from t4 where cast(ts as date) = '2020-04-01';
+----+----------------------------+
| id | ts                         |
+----+----------------------------+
|  3 | 2020-04-01 00:00:00.000000 |
|  4 | 2020-04-01 23:59:59.000000 |
|  5 | 2020-04-01 23:59:59.999999 |
+----+----------------------------+
3 rows in set (0.00 sec)

between 演算子で大きめの範囲を指定して検索し、除外したい値を不等価演算子 (Not equal operator) で除外する。

mysql> select * from t4 where ts between '2020-04-01 00:00:00' and '2020-04-02 00:00:00' and ts != '2020-04-02 00:00:00';
+----+----------------------------+
| id | ts                         |
+----+----------------------------+
|  3 | 2020-04-01 00:00:00.000000 |
|  4 | 2020-04-01 23:59:59.000000 |
|  5 | 2020-04-01 23:59:59.999999 |
+----+----------------------------+
3 rows in set (0.00 sec)

参考資料

11
11
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
11
11

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?