SQLで日付と時刻を扱う方法

紹介

リレーショナルデータベースとStructured Query Language（SQL）を使用する際に、特定の日付や時間を表す値を扱う必要がある場合があります。たとえば、特定のアクティビティに費やされた合計時間を計算する必要があるかもしれません。また、日付や時間の値を数学演算子や集計関数を使用して操作し、その合計や平均を計算する必要があるかもしれません。

このチュートリアルでは、SQLで日付と時間を使用する方法を学びます。まず、SELECTステートメントのみを使用して、日付と時間を操作し、さまざまな関数を使用して算術演算を行います。次に、サンプルデータにクエリを実行して練習し、出力を読みやすくするためにCAST関数を実装する方法を学びます。

前提条件

このチュートリアルを完了するには、以下が必要です：

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• サーバーにMySQLがインストールされ、セキュリティが確保されていること。これを設定するには、当社のUbuntu 20.04にMySQLをインストールする方法ガイドに従ってください。このガイドでは、この手順のステップ3で非ルートのMySQLユーザーも設定したことを前提としています。

このチュートリアルで日付と時刻を使用する練習をするには、サンプルデータがロードされたデータベースとテーブルが必要です。挿入する準備ができていない場合は、次のMySQLへの接続とサンプルデータベースのセットアップセクションを読んで、データベースとテーブルの作成方法を学んでください。このチュートリアルでは、このサンプルデータベースとテーブルを参照します。

MySQLへの接続とサンプルデータベースのセットアップ

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

ssh sammy@your_server_ip

次に、MySQLプロンプトを開き、sammyを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

データベースを選択した後、その中にテーブルを作成します。このチュートリアルの例では、1年間にわたって実施されたさまざまなレースで2人のランナーの結果を保持するテーブルを作成します。このテーブルには、次の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時間制の時計形式に従います。たとえば、15:00 は午後3時の相当です。
• total_miles：各レースの総マイル数を表し、多くの場合、レースごとの総マイル数が整数ではないため、decimalデータ型を使用します。この場合、decimalは精度が3でスケールが1を指定しており、この列の値には3桁の数値が含まれ、そのうち1桁が小数点の右にあることを意味します。
• end_time：ランナーのレース終了時刻を追跡するためにTIMESTAMPデータ型を使用します。このデータ型は日付と時刻を1つの文字列に組み合わせ、その形式はDATEおよびTIMEの形式の組み合わせです：YYYY-MM-DD HH:MM:SS。

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)
); 

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

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では、日付と時刻の値を数学的な式を使用して操作できます。計算したい値と数学演算子が必要です。

次の例を考えてみましょう。特定の日付から一定の日数後の日付を見つけたいとします。次のクエリは、1つの日付値（2022-10-05）を取り、それに17を加えて、クエリで指定された日付の17日後の値を返します。この例では、2022-10-05をDATE値として指定していることに注意してください。これにより、DBMSがそれを文字列やその他のデータ型と解釈しないようにしています。

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日です。

別の例として、異なる時間間の合計時間を計算したいとします。これは、一方の時間から他方の時間を引くことで行うことができます。次のクエリでは、11:00が最初の時間値であり、3:00が2番目の時間値です。ここでは、両方が時間値であることを指定して、時間の差を時間で返す必要があります。

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の間の差は80000、または8時間です。

さて、サンプルデータからの日付および時間情報を使用した演算の練習を行いましょう。最初のクエリでは、ランナーが各レースを終えるのにかかった合計時間を計算するために、end_timeからstart_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関数を使用する方法を示します。

今、もし各ランナーのパフォーマンスに興味があるのが長距離レース（ハーフマラソンやフルマラソンなど）だけなら、その情報を取得するためにデータをクエリで検索できます。このクエリでは、end_timeからstart_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 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の選択に依存しますが、ほとんどの場合、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番目の例から思い出してください。ランナーが各レースごとに完了した合計時間を計算するために、次のクエリを実行して end_time を start_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 列を 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集計関数で検索するクエリを作成します。再び、明確にするためにCASTを使用してTIMESTAMPデータ値をTIMEデータ値に変換する必要があります。この例のように2つの関数を使用する場合、2組の括弧が必要であり、合計時間（end_time - start_time）の計算はそのうちの1つの括弧の中にネストする必要があります。最後に、runner_name列に基づいてこれらの値を整理するためにGROUP BY句を追加して、出力が2人のランナーのレース結果を示すようにします：

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)

この出力には、各ランナーの最短の実行時間が表示されます。今回の場合、Boltの最小は6分30秒、Felixの最小は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)

この出力から、Boltの最長の実行時間は合計3時間23分10秒であり、Felixの最長の実行時間は合計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秒となります。時間のこの分解が意味をなさないことから、これが整数として計算されていることが示されます。たとえば、異なるDBMS（PostgreSQLなど）でこれを試した場合、同じクエリが若干異なる形で表示されます。

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のクエリは値を時間として解釈し、それに応じて計算するため、フェリックスの結果は合計で10時間1分44秒、ボルトの結果は合計で6時間9分20秒となります。これは、異なるDBMSの実装が、同じクエリとデータセットを使用していても、データ値を異なる方法で解釈する例です。

結論

SQLで日付と時刻を使用する方法を理解することは、特定の結果をクエリする際に便利です。分、秒、時間、日、月、年、またはそれらの組み合わせなど、さまざまな条件で結果をクエリすることができます。さらに、日付と時刻に関する多くの関数が利用可能で、現在の日付や時刻など特定の値を見つけやすくなっています。このチュートリアルでは、SQLで日付と時刻に加算および減算演算子のみを使用しましたが、日付と時刻の値は任意の数学式と組み合わせて使用できます。日付と時刻のクエリで数学式と集計関数を試して、さらに詳しく学んでください。