サービスを運用していると、ときどき日や月をまたいだとある時間に起きた処理を探したいことがあるかもしれません。今回はそんな時に役に立つ機能に関して紹介していきたいと思います。
検証環境
今回はDockerで建てたMySQLを使用します。以下のコマンドでDockerを建てて、ローカルからアクセスします。
% docker run --platform linux/x86_64 -p 127.0.0.1:3307:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -e MYSQL_USER=kk2170 -e MYSQL_PASSWORD=my-secret-pw -d mysql:8.4.3 --secure-file-priv='/tmp'
今回はMySQL Shellをインストールして実行していきますが、いったん従来のmysqlクライアントでアクセスが可能であることを確認しましょう。方法は以下の通りです。
% mysql -uroot -pmy-secret-pw -h127.0.0.1 -P3307
執筆時点では、以下の通りMySQL 8.
mysql> select version(); +-----------+ | version() | +-----------+ | 8.4.3 | +-----------+ 1 row in set (0.01 sec)
テストデータ作成
今回は時刻データが欲しいので、idとtimestamp型のcreated_
mysql> CREATE DATABASE timestamptest;
Query OK, 1 row affected (0.01 sec)
mysql> use timestamptest
Database changed
mysql> CREATE TABLE example_table (
id INT AUTO_INCREMENT PRIMARY KEY,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
追加でcreated_
mysql> CREATE INDEX idx_created_at ON example_table (created_at);
10万件を適当に作成して入れてみましょう。2024年の1月1日から2025年の3月14日までの値を生成して入れることにします。CSVに一旦結果を吐き出して、それをLOAD DATA INFILEで取り込みました。
SET SESSION cte_max_recursion_depth = 100000;
WITH RECURSIVE random_timestamps AS (
-- 開始点（最初の1行）
SELECT
1 AS n,
FROM_UNIXTIME(
FLOOR(UNIX_TIMESTAMP('2024-01-01 00:00:00') +
RAND() * (UNIX_TIMESTAMP('2025-03-14 23:59:59') - UNIX_TIMESTAMP('2024-01-01 00:00:00')))
) AS created_at
UNION ALL
-- 再帰的に 10,000 行を作成
SELECT
n + 1,
FROM_UNIXTIME(
FLOOR(UNIX_TIMESTAMP('2024-01-01 00:00:00') +
RAND() * (UNIX_TIMESTAMP('2025-03-14 23:59:59') - UNIX_TIMESTAMP('2024-01-01 00:00:00')))
)
FROM random_timestamps
WHERE n < 100000
)
SELECT created_at FROM random_timestamps
INTO OUTFILE '/tmp/random_timestamps.csv'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
example_tableに挿入LOAD DATA INFILE '/tmp/random_timestamps.csv' INTO TABLE example_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (created_at);
結果を適当に選択して確認してみましょう。
mysql> SELECT MAX(created_at) FROM example_table; +---------------------+ | MAX(created_at) | +---------------------+ | 2025-03-14 23:57:39 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT MIN(created_at) FROM example_table; +---------------------+ | MIN(created_at) | +---------------------+ | 2024-01-01 00:10:07 | +---------------------+ 1 row in set (0.00 sec)
最大値と最小値を見てみましたが、1月1日から3月14日の値に収まっていることがわかります。
年単位での取得
さて、このデータから年単位での処理を行いたい場合を考えます。2024年と2025年のデータが入っていることがわかるので、2024年のデータを取得してみましょう。
この場合はBETWEENを使用すると、簡単に取得することができます。
mysql> SELECT COUNT(*) FROM example_table WHERE created_at BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59';
この時にEXPLAINを取ってみると、以下のようにインデックスが使用されていることがわかります。
mysql> EXPLAIN SELECT COUNT(*) FROM example_table WHERE created_at BETWEEN '2024-01-01 00:00:00' AND '2024-12-31 23:59:59'; +----+-------------+---------------+------------+-------+----------------+----------------+---------+------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+----------------+----------------+---------+------+-------+----------+--------------------------+ | 1 | SIMPLE | example_table | NULL | range | idx_created_at | idx_created_at | 5 | NULL | 50232 | 100.00 | Using where; Using index | +----+-------------+---------------+------------+-------+----------------+----------------+---------+------+-------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
ここまではよくある話だと思います。
時間単位での取得
続いて、年をまたいでの時間単位での処理がしたい場合を考えます。普段は年、月、日、時間という順序で処理を考えていると思いますが、たとえば00:00:00のデータを探したい場合を考えます。
力技で回すのでであれば、プログラムなどで全件取得してきて00:00:00のデータを探す方法などが考えられますが、SQLだけで簡単に確認したい場合もあると思います。
そんな場合に便利なのがTIME関数です。DATETIMEまたはTIMESTAMPから時間部分だけを取得する関数になります。
mysql> SELECT created_at FROM example_table order by created_at limit 1; +---------------------+ | created_at | +---------------------+ | 2024-01-01 00:10:07 | +---------------------+ 1 row in set (0.00 sec)
上のようなcreated_
mysql> SELECT TIME(created_at) FROM example_table order by created_atlimit 1; +------------------+ | TIME(created_at) | +------------------+ | 00:10:07 | +------------------+ 1 row in set (0.00 sec)
ちなみに、文字列で比較ができるので、以下のようなクエリで探すことができます。
mysql> SELECT * FROM example_table where TIME(created_at) = '00:00:00'; +-------+---------------------+ | id | created_at | +-------+---------------------+ | 87924 | 2024-10-27 00:00:00 | | 87706 | 2025-02-08 00:00:00 | +-------+---------------------+ 2 rows in set (0.02 sec)
さてここで気になるのが、TIMESTAMPを文字列に変換するような処理をした際に、インデックスが適切に使用されているかというところです。EXPLAINで確認してみましょう。
mysql> EXPLAIN SELECT * FROM example_table where TIME(created_at) = '00:00:00'; +----+-------------+---------------+------------+-------+---------------+----------------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+---------------+----------------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | example_table | NULL | index | NULL | idx_created_at | 5 | NULL | 100464 | 100.00 | Using where; Using index | +----+-------------+---------------+------------+-------+---------------+----------------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
typeがindexになっていることがわかります。これはindexをすべて見ないとわからなかったという所で、全件見たことになり、性能が出ない結果となりました。
このような処理をサービスに組み込む場合は、時間だけ切り出したカラムを用意した方が良いことがわかります。ただ、1回だけで分析をしたいような場合にも便利な処理となります。
年 月 日 時 分 秒それぞれ取得したい場合を考える
実は、MySQLにはそれぞれに対応する関数があります。
| 関数 | 説明 |
|---|---|
YEAR() |
DATEやDATETIMEから年 (YYYY) を取得 |
MONTH() |
DATEやDATETIMEから月 (1～12) を取得 |
DAY() |
DATEやDATETIMEから日 (1～31) を取得 |
HOUR() |
DATETIMEやTIMEから時 (0～23) を取得 |
MINUTE() |
DATETIMEやTIMEから 分 (0～59) を取得 |
SECOND() |
DATETIMEやTIMEから 秒 (0～59) を取得 |
これらの関数の使用例を以下に挙げます。
mysql> SELECT created_at FROM example_table order by created_at desc limit 1; +---------------------+ | created_at | +---------------------+ | 2025-03-14 23:57:39 | +---------------------+ 1 row in set (0.00 sec)
上のようなデータがあった場合に、以下のような結果がそれぞれ得られます。
mysql> SELECT YEAR(created_at), MONTH(created_at), DAY(created_at), HOUR(created_at), MINUTE(created_at), SECOND(created_at) FROM example_table order by created_at desc limit 1; +------------------+-------------------+-----------------+------------------+--------------------+--------------------+ | YEAR(created_at) | MONTH(created_at) | DAY(created_at) | HOUR(created_at) | MINUTE(created_at) | SECOND(created_at) | +------------------+-------------------+-----------------+------------------+--------------------+--------------------+ | 2025 | 3 | 14 | 23 | 57 | 39 | +------------------+-------------------+-----------------+------------------+--------------------+--------------------+ 1 row in set (0.00 sec)
ということで、先ほどと同じように00:00:00のデータを探してみましょう。
mysql> SELECT created_at FROM example_table WHERE HOUR(created_at) = '00' AND MINUTE(created_at) = '00' AND SECOND(created_at) = '00'; +---------------------+ | created_at | +---------------------+ | 2024-10-27 00:00:00 | | 2025-02-08 00:00:00 | +---------------------+ 2 rows in set (0.01 sec)
このように、同じように取得できることがわかりました。当然ですが、これらの関数を検索に使用した場合、インデックスを使用することができないため遅くなります。
mysql> explain SELECT created_at FROM example_table WHERE HOUR(created_at) = '00' AND MINUTE(created_at) = '00' AND SECO ND(created_at) = '00'; +----+-------------+---------------+------------+-------+---------------+----------------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+---------------+----------------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | example_table | NULL | index | NULL | idx_created_at | 5 | NULL | 100464 | 100.00 | Using where; Using index | +----+-------------+---------------+------------+-------+---------------+----------------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)
ただ、これらの関数はMySQLの独自機能のため、標準機能を使用しておきたい場合は、後述するEXTRACT関数を使用するとよいでしょう。
EXTRACT関数を使用する
EXTRACT(指定の形式 FROM 時刻)の形式で使用できます。使用できる形式は以下の表にまとめておきます。
| 関数 | 説明 |
|---|---|
EXTRACT(YEAR FROM ...) |
年を取得 |
EXTRACT(MONTH FROM ...) |
月を取得 |
EXTRACT(DAY FROM ...) |
日を取得 |
EXTRACT(HOUR FROM ...) |
時を取得 |
EXTRACT(MINUTE FROM ...) |
分を取得 |
EXTRACT(SECOND FROM ...) |
秒を取得 |
EXTRACT(WEEK FROM ...) |
週番号を取得 |
EXTRACT(QUARTER FROM ...) |
四半期を取得 |
SELECT
EXTRACT(YEAR FROM created_at) AS year_value,
EXTRACT(MONTH FROM created_at) AS month_value,
EXTRACT(DAY FROM created_at) AS day_value,
EXTRACT(HOUR FROM created_at) AS hour_value,
EXTRACT(MINUTE FROM created_at) AS minute_value,
EXTRACT(SECOND FROM created_at) AS second_value,
EXTRACT(WEEK FROM created_at) AS week_value,
EXTRACT(QUARTER FROM created_at) AS quarter_value
FROM example_table limit 1;
+------------+-------------+-----------+------------+--------------+--------------+------------+---------------+
| year_value | month_value | day_value | hour_value | minute_value | second_value | week_value | quarter_value |
+------------+-------------+-----------+------------+--------------+--------------+------------+---------------+
| 2024 | 1 | 1 | 0 | 10 | 7 | 0 | 1 |
+------------+-------------+-----------+------------+--------------+--------------+------------+---------------+
1 row in set (0.00 sec)
当然ですが、これらに関してもインデックスは効率的に使用されません。
mysql> explain SELECT EXTRACT(YEAR FROM created_at) AS year_value, EXTRACT(MONTH FROM created_at) AS month_valu e, EXTRACT(DAY FROM created_at) AS day_value, EXTRACT(HOUR FROM created_at) AS hour_value, EXTRACT(MINUTE FR OM created_at) AS minute_value, EXTRACT(SECOND FROM created_at) AS second_value, EXTRACT(WEEK FROM created_at) A S week +----+-------------+---------------+------------+-------+---------------+----------------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+-------+---------------+----------------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | example_table | NULL | index | NULL | idx_created_at | 5 | NULL | 100464 | 100.00 | Using index | +----+-------------+---------------+------------+-------+---------------+----------------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
個人的な感想になってしまうのですが、咄嗟にEXTRACT関数を思い出すよりは、YEAR()、MONTH()、DAY()、TIME()といった関数の方が直観的だと思うので、サービスに組み込む場合を除いて、コマンドラインクライアントなどで使用するときは、あまりお世話になることは無いと思います。
GENERATED COLUMNを使用して必要なカラムを用意する
もし、万が一これらの計算を常に行わないといけないとしたら、第150回で紹介したGENERATED COLUMNを使用するのが良いかもしれません。
ALTER TABLE example_table
ADD COLUMN hour INT GENERATED ALWAYS AS (HOUR(created_at)) STORED,
ADD INDEX idx_hour (hour);
hourカラムを使用して検索をしてみましょう。
mysql> SELECT * FROM example_table WHERE hour = '00' limit 1; +----+---------------------+------+ | id | created_at | hour | +----+---------------------+------+ | 12 | 2024-11-20 00:30:03 | 0 | +----+---------------------+------+ 1 row in set (0.00 sec)
EXPLAINを取ってみましょう。typeもrefとなっていて、indexが効率的に使われていることがわかります。
mysql> explain SELECT * FROM example_table WHERE hour = '00' limit 1; +----+-------------+---------------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------------+------------+------+---------------+----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | example_table | NULL | ref | idx_hour | idx_hour | 5 | const | 4147 | 100.00 | NULL | +----+-------------+---------------+------------+------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
まとめ
今回は、知っておくとちょっとだけ便利なTIME関数や、各種時刻を取得する処理を紹介しました。
普段は比較処理というよりは、SELECTしてきた結果に使用することがほとんどだと思いますが。こういう使い方があることを知っておくと、ぱっと準備されてないデータに対して検索を行いたいときに、思わぬところで便利だったりします。
ただし、インデックスが効かないパターンが多いため、流す際には件数やCPU、メモリの使用率などに注意をして実施をしましょう。 また、こういったクエリを日常的に使用する際には、検索用のカラムを用意することを検討してください。