SQLで日付と時刻を操作する方法

導入

リレーショナルデータベースや構造化照会言語（SQL）を扱う際に、特定の日付や時刻を表す値を操作する必要があることがあります。例えば、特定の活動に費やした合計時間を計算したり、数学演算子や関数を用いて日付や時刻の値を集計し、合計値や平均値を計算したりする必要があるかもしれません。.

このチュートリアルでは、SQLで日付と時刻を使用する方法を学びます。まずはSELECT文だけで日付と時刻を扱う計算や様々な関数の使い方を学びます。次に、サンプルデータに対してクエリを実行し、CAST関数を実装して出力を分かりやすくする方法を学びます。.

前提条件

このチュートリアルを完了するには、次のものが必要です。

Ubuntu 20.04 を実行しているサーバー。sudo 管理者権限を持つ非 root ユーザーと、有効なファイアウォールを備えています。.
MySQL はサーバー上にインストールされ、保護されています。.

注：多くのリレーショナルデータベース管理システムは独自のSQL実装を使用していることにご注意ください。このチュートリアルで紹介するコマンドはほとんどのRDBMSで動作しますが、MySQL以外のシステムで実行した場合、正確な構文や出力が異なる場合があります。.

このチュートリアルで日付と時刻の使い方を練習するには、サンプルデータがロードされたデータベースとテーブルが必要です。データベースとテーブルがまだ用意されていない場合は、「MySQLへの接続とサンプルデータベースの設定」セクションを参照して、データベースとテーブルの作成方法を確認してください。このチュートリアルでは、このサンプルデータベースとテーブルを全体を通して参照します。.

MySQLへの接続とサンプルデータベースの設定

SQL データベースがリモートサーバー上で実行されている場合は、ローカルマシンからサーバーに SSH で接続します。

ssh sammy@your_server_ip

次に、MySQL プロンプトを開き、Sami を MySQL アカウント情報に置き換えます。

mysql -u sammy -p

datetimeDB というデータベースを作成します。

CREATE DATABASE datetimeDB;

データベースが正常に作成されると、次の出力が表示されます。

Output
Query OK, 1 row affected (0.01 sec)

datetimeDB データベースを選択するには、次の USE ステートメントを実行します。

USE datetimeDB;

Output
Database changed

データベースを選択したら、その中にテーブルを作成します。このチュートリアルでは、2人のランナーが1年間に走った異なるレースの結果を格納するテーブルを作成します。このテーブルには、以下の7つの列があります。

race_id: int データ型の値を表し、テーブルの主キーとして機能します。つまり、この列の各値は、対応する行の一意の識別子として機能します。.
runner_name: 2 人のランナー、Bolt と Felix の名前に最大 30 文字の varchar データ型を使用します。.
race_name: 最大 20 文字の varchar データ型のレースタイプを保持します。.
start_day: DATEデータ型を使用して、年、月、日に基づいて特定の試合の日付を追跡します。このデータ型は、年を4桁、月と日を最大2桁（YYYY-MM-DD）で表します。.
start_time: 試合開始時刻をTIMEデータ型で表し、時、分、秒（HH:MM:SS）で表します。このデータ型は24時間制に準拠しており、例えば午後3時を表す場合は15:00となります。.
total_miles: 各レースの総走行距離は整数ではないことが多いため、小数点データ型を使用して表示します。この場合、小数点は精度3、スケール1を指定します。つまり、この列の各値は3桁で、そのうち1桁は小数点の右側になります。.
end_time: TIMESTAMPデータ型を使用して、レース終了時のランナーのタイムを追跡します。このデータ型は日付と時刻を文字列に結合し、形式はDATEとTIMEの組み合わせ（YYYY-MM-DD HH:MM:SS）です。.

CREATE TABLE コマンドを実行してテーブルを作成します。

CREATE TABLE race_results (
race_id int, 
runner_name varchar(30),
race_name varchar(20), 
start_day DATE,
start_time TIME, 
total_miles decimal(3, 1),
end_time TIMESTAMP,
PRIMARY KEY (race_id)
);

次に、空のテーブルにサンプルデータを入力します。

INSERT INTO race_results
(race_id, runner_name, race_name, start_day, start_time, total_miles, end_time)
VALUES
(1, 'bolt', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:06:30'),
(2, 'bolt', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:22:31'),
(3, 'bolt', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 10:38:05'),
(4, 'bolt', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 07:39:04'),
(5, 'bolt', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 11:23:10'),
(6, 'felix', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:07:15'),
(7, 'felix', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:30:50'),
(8, 'felix', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 11:10:17'),
(9, 'felix', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 08:11:57'),
(10, 'felix', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 12:02:10');

Output
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0

データを入力すると、SQL で日付と時刻を使った算術演算と関数の練習を始める準備が整います。.

日付と時刻でアカウントを使用する

SQLでは、数式を使って日付と時刻の値を操作できます。必要なのは、数式演算子と計算したい値だけです。.

例えば、ある日付から指定した日数後の日付を検索したいとします。次のクエリは、日付値（2022-10-05）に17を加算し、クエリで指定した日付から17日後の日付値を返します。この例では、DBMSが文字列やその他のデータ型として解釈しないように、DATE値として2022-10-05を指定していることに注意してください。

SELECT DATE '2022-10-05' + 17 AS new_date;

Output
+----------+
| new_date |
+----------+
| 20221022 |
+----------+
1 row in set (0.01 sec)

この出力が示すように、2022-10-05 の 17 日後が 2022-10-22、つまり 2022 年 10 月 22 日になります。.

別の例として、2つの異なる時刻間の合計時間を計算したいとします。これは、2つの時刻を減算することで行えます。次のクエリでは、1つ目の時刻値は11:00、2つ目の時刻値は3:00です。差を時間単位で返すには、両方の値がTIME値であることを指定する必要があります。

SELECT TIME '11:00' - TIME '3:00' AS time_diff;

Output
+-----------+
| time_diff |
+-----------+
| 80000 |
+-----------+
1 row in set (0.00 sec)

この出力は、11:00 と 3:00 の差が 80,000、つまり 8 時間であることを示しています。.

サンプルデータの日付と時刻情報を使って計算を練習してみましょう。最初のクエリでは、start_timeからend_timeを引いて、各レースのランナーが完走するまでにかかった合計時間を計算します。

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name | total_time |
+-------------+---------------+----------------+
| bolt | 1600_meters | 20220918000630 |
| bolt | 5K | 20221019002231 |
| bolt | 10K | 20221120003805 |
| bolt | half_marathon | 20221221013904 |
| bolt | full_marathon | 20230122032310 |
| felix | 1600_meters | 20220918000715 |
| felix | 5K | 20221019003050 |
| felix | 10K | 20221120011017 |
| felix | half_marathon | 20221221021157 |
| felix | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

total_time列の出力は非常に長く、読みにくいことにお気づきでしょう。次に、CAST関数を使ってこれらのデータ値を変換し、読みやすくする方法を説明します。.

さて、ハーフマラソンやフルマラソンといった長距離レースにおける各ランナーのパフォーマンスのみに関心がある場合は、データにクエリを実行してその情報を取得できます。このクエリでは、start_timeからend_timeを減算し、WHERE句を使用して結果を絞り込み、total_milesが12より大きいデータを取得します。

SELECT runner_name, race_name, end_time - start_time AS half_full_results
FROM race_results 
WHERE total_miles > 12;

Output
+-------------+---------------+-------------------+
| runner_name | race_name | half_full_results |
+-------------+---------------+-------------------+
| bolt | half_marathon | 20221221013904 |
| bolt | full_marathon | 20230122032310 |
| felix | half_marathon | 20221221021157 |
| felix | full_marathon | 20230122040210 |
+-------------+---------------+-------------------+
4 rows in set (0.00 sec)

このセクションでは、SELECT文を用いて、サンプルデータを用いた実用的な日付と時刻の計算をいくつか実行しました。次に、様々な日付と時刻の関数を用いたクエリを練習します。.

日付と時刻の関数と範囲式の使用

SQLには、日付と時刻の値を検索したり操作したりするために使用できる関数がいくつかあります。SQL関数は通常、データの処理や操作に使用され、使用できる関数はSQLの実装によって異なります。ただし、ほとんどのSQL実装では、current_dateとcurrent_timeの値をクエリすることで、現在の日付と時刻を取得できます。.

たとえば、今日の日付を見つける場合、構文は短く、次のように SELECT ステートメントと current_date 関数だけで構成されます。

SELECT current_date;

Output
+--------------+
| current_date |
+--------------+
| 2022-02-15 |
+--------------+
1 row in set (0.00 sec)

同じ構文を使用して、current_time 関数で現在の時刻を見つけることができます。

SELECT current_time;

Output
+--------------+
| current_time |
+--------------+
| 17:10:20 |
+--------------+
1 row in set (0.00 sec)

出力内の日付と時刻を照会したい場合は、current_timestamp 関数を使用します。

SELECT current_timestamp;

Output
+---------------------+
| current_timestamp |
+---------------------+
| 2022-02-15 19:09:58 |
+---------------------+
1 row in set (0.00 sec)

前のセクションと同様に、これらの日付と時刻の関数は算術関数でも使用できます。例えば、今日から11日前の日付を知りたいとします。この場合、current_date関数のクエリと同じ構文を使用し、そこから11を引くことで11日前の日付を見つけることができます。

SELECT current_date - 11;

Output
+-------------------+
| current_date - 11 |
+-------------------+
| 20220206 |
+-------------------+
1 row in set (0.01 sec)

この出力が示すように、current_date の 11 日前 (この記事の執筆時点) は 2022-02-06、つまり 2022 年 2 月 6 日でした。今度は同じ操作を試してみますが、current_date を current_time 関数に置き換えます。

SELECT current_time - 11;

Output
+-------------------+
| current_time - 11 |
+-------------------+
| 233639 |
+-------------------+
1 row in set (0.00 sec)

この出力は、current_time値から11を引くと11秒が減算されることを示しています。先ほどcurrent_date関数を使って行った操作では、11は秒ではなく日数として解釈されていました。数値の解釈方法におけるこの不一致は、日付と時刻の関数を扱う際に混乱を招く可能性があります。多くのデータベース管理システムでは、このような計算を用いて日付と時刻の値を操作するのではなく、INTERVALステートメントを用いてより明示的に操作できます。.

INTERVAL式を使用すると、日付または時刻式から指定した間隔の前または後の日付または時刻を指定できます。INTERVAL式は以下の形式で記述する必要があります。

INTERVAL value unit

たとえば、今から 5 日後の日付を検索するには、次のクエリを実行します。

SELECT current_date + INTERVAL '5' DAY AS "5_days_from_today";

この例では、current_dateの値を取得し、それに間隔式INTERVAL '5' DAYを加算します。これにより、5日後の日付が返されます。

Output
+-------------------+
| 5_days_from_today |
+-------------------+
| 2022-03-06 |
+-------------------+
1 row in set (0.00 sec)

これは、同一ではないものの同様の出力を生成する次のクエリよりもはるかに明確です。

SELECT current_date + 5 AS "5_days_from_today";

Output
+-------------------+
| 5_days_from_today |
+-------------------+
| 20220306 |
+-------------------+
1 row in set (0.00 sec)

指定した日付の値より前の値を見つけるために、日付または時刻から時間間隔を減算できることに注意してください。

SELECT current_date - INTERVAL '7' MONTH AS "7_months_ago";

Output
+--------------+
| 7_months_ago |
+--------------+
| 2021-08-01 |
+--------------+
1 row in set (0.00 sec)

INTERVAL ステートメントで使用できる単位は選択した DBMS によって異なりますが、ほとんどの DBMS には HOUR、MINUTE、SECOND などのオプションがあります。

SELECT current_time + INTERVAL '6' HOUR AS "6_hours_from_now",
current_time - INTERVAL '5' MINUTE AS "5_minutes_ago",
current_time + INTERVAL '20' SECOND AS "20_seconds_from_now";

Output
+------------------+---------------+---------------------+
| 6_hours_from_now | 5_minutes_ago | 20_seconds_from_now |
+------------------+---------------+---------------------+
| 07:51:43 | 01:46:43 | 01:52:03.000000 |
+------------------+---------------+---------------------+
1 row in set (0.00 sec)

間隔式といくつかの日付と時刻の関数について学習したので、最初の手順で入力したサンプルデータの操作を練習し続けます。.

日付と時刻でCAST関数と集計関数を使用する

「日付と時刻の算術演算の使用」セクションの3番目の例を思い出してください。次のクエリを実行して、start_timeからend_timeを減算し、各ランナーが各レースで走った合計時間を計算しました。しかし、出力はテーブルに設定されたTIMESTAMPデータ型に従った非常に長い列になりました。

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name | total_time |
+-------------+---------------+----------------+
| bolt | 1600_meters | 20220918000630 |
| bolt | 5K | 20221019002231 |
| bolt | 10K | 20221120003805 |
| bolt | half_marathon | 20221221013904 |
| bolt | full_marathon | 20230122032310 |
| felix | 1600_meters | 20220918000715 |
| felix | 5K | 20221019003050 |
| felix | 10K | 20221120011017 |
| felix | half_marathon | 20221221021157 |
| felix | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

異なるデータ型を持つ2つの列（end_timeはTIMESTAMP値を保持し、start_timeはTIME値を保持）に対して操作を実行しているため、データベースは操作結果を出力する際にどのデータ型を使用すべきか判断できません。代わりに、操作を実行するために両方の値を整数に変換し、total_time列に長い数値が出力されます。.

このデータをより明確に読み取り、解釈するために、CAST関数を使用してこれらの長整数値をTIMEデータ型に変換できます。これを行うには、CAST関数で開始し、その直後に開き括弧、変換したい値、そしてASキーワードと変換後のデータ型を続けます。.

次のクエリは前の例と同じですが、CAST 関数を使用して total_time 列を時間データ型に変換します。

SELECT runner_name, race_name, CAST(end_time - start_time AS time)
AS total_time 
FROM race_results;

Output
+-------------+---------------+------------+
| runner_name | race_name | total_time |
+-------------+---------------+------------+
| bolt | 1600_meters | 00:06:30 |
| bolt | 5K | 00:22:31 |
| bolt | 10K | 00:38:05 |
| bolt | half_marathon | 01:39:04 |
| bolt | full_marathon | 03:23:10 |
| felix | 1600_meters | 00:07:15 |
| felix | 5K | 00:30:50 |
| felix | 10K | 01:10:17 |
| felix | half_marathon | 02:11:57 |
| felix | full_marathon | 04:02:10 |
+-------------+---------------+------------+
10 rows in set (0.00 sec)

CAST はこの出力のデータ値を TIME に変換し、非常に読みやすく理解しやすくしました。.

それでは、CAST関数と組み合わせて集計関数を使用し、各ランナーの最短、最長、合計タイムを調べてみましょう。まず、MIN関数で最小（または最短）タイムを検索します。ここでも、わかりやすくするために、TIMESTAMPデータ値をTIMEデータ値に変換するにはCASTを使用します。この例のように2つの関数を使用する場合は、括弧を2組使用し、合計時間（end_time - start_time）の計算をそのうちの1つに配置する必要があることに注意してください。最後に、GROUP BY句を追加して、これらの値をrunner_name列で整理し、ランナーのレース結果出力を表示します。

SELECT runner_name, MIN(CAST(end_time - start_time AS time)) AS min_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | min_time |
+-------------+----------+
| bolt | 00:06:30 |
| felix | 00:07:15 |
+-------------+----------+
2 rows in set (0.00 sec)

この出力はランナーの最短時間を示しており、この場合、ボルトの場合は少なくとも 6 分 30 秒、フェリックスの場合は 7 分 15 秒です。.

次に、各ランナーの最長実行時間を調べます。前のステートメントと同じ構文を使用できますが、今回はMINをMAXに置き換えます。

SELECT runner_name, MAX(CAST(end_time - start_time AS time)) AS max_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | max_time |
+-------------+----------+
| bolt | 03:23:10 |
| felix | 04:02:10 |
+-------------+----------+
2 rows in set (0.00 sec)

この出力から、ボルトの最長走行タイムは合計3時間23分10秒だったことがわかります。一方、フェリックスは合計4時間2分10秒でした。.

それでは、各ランナーのランニング時間合計に関する高レベル情報をクエリしてみましょう。このクエリでは、SUM関数を使ってend_time - start_timeに基づいて合計時間を求め、CASTを使ってこれらのデータ値をTIMEに変換します。各ランナーの結果値を整理するために、GROUP BY句を追加することを忘れないでください。

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
+-------------+-------------+
| runner_name | total_hours |
+-------------+-------------+
| bolt | 52880 |
| felix | 76149 |
+-------------+-------------+
2 rows in set (0.00 sec)

興味深いことに、この出力はMySQLが合計時間を整数として解釈していることを示しています。これらの結果を時間として読み取ると、ボルトの合計時間は5時間28分80秒に分割されます。また、フェリックスの時間は7時間61分49秒に分割されます。ご覧のとおり、これは論理的な時間の内訳ではなく、時間ではなく整数として計算されていることを示しています。例えば、PostgreSQLなどの別のDBMSで同じクエリを実行すると、結果は少し異なります。

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
runner_name | total_hours
-------------+-------------
felix | 10:01:44
bolt | 06:09:20
(2 rows)

この場合、PostgreSQLのクエリは値を時間として解釈し、そのように計算するため、Felixの結果は合計10時間1分44秒に分割されます。一方、Boltの場合は6時間9分20秒です。これは、同じクエリとデータセットを使用していても、異なるDBMS実装ではデータ値の解釈が異なる場合があることを示す例です。.

結果

SQLで日付と時刻の使い方を理解しておくと、分、秒、時間、日、月、年など、特定の日付や時刻の組み合わせを検索するときに役立ちます。さらに、日付と時刻用の関数も数多く用意されており、現在の日付や時刻など、特定の値を簡単に見つけることができます。このチュートリアルでは、SQLにおける日付と時刻の加算と減算の計算についてのみ説明しますが、日付と時刻の値は任意の数式で使用できます。数式と加算関数に関するガイドで詳細を学び、日付と時刻の検索で試してみてください。.