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

序章

リレーショナルデータベースと構造化照会言語（SQL）を操作する場合、特定の日付または時刻を表す値を操作する必要がある場合があります。 たとえば、特定のアクティビティに費やされた合計時間を計算する必要がある場合や、数学演算子と集計関数を使用して日付または時刻の値を操作して合計または平均を計算する必要がある場合があります。

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

前提条件

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

• sudo管理者権限とファイアウォールが有効になっているroot以外のユーザーがいるUbuntu20.04を実行しているサーバー。 Ubuntu20.04を使用したサーバーの初期設定に従って開始します。
• MySQLがサーバーにインストールされ、保護されています。 これを設定するには、 Ubuntu20.04ガイドにMySQLをインストールする方法に従ってください。 このガイドは、このガイドのステップ3 で概説されているように、root以外のMySQLユーザーも設定していることを前提としています。

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

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

このページに埋め込まれているインタラクティブ端末を使用して、このチュートリアルのサンプルクエリを試すこともできます。 次のLaunch an Interactive Terminal!ボタンをクリックして開始します。

インタラクティブターミナルを起動します！

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

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

ssh sammy@your_server_ip

次に、MySQLプロンプトを開き、sammyをMySQLユーザーアカウント情報に置き換えます。 このページに埋め込まれたインタラクティブ端末を使用している場合、プロンプトが表示されたときに使用するパスワードはsecretという単語であることに注意してください。

mysql -u sammy -p

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

CREATE DATABASE datetimeDB;

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

OutputQuery OK, 1 row affected (0.01 sec)

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

USE datetimeDB;

OutputDatabase changed

データベースを選択したら、その中にテーブルを作成します。 このチュートリアルの例では、1年間に実行したさまざまなレースの2人のランナーの結果を保持するテーブルを作成します。 このテーブルには、次の7つの列が含まれます。

• race_id：intデータ型の値を表示し、テーブルの主キーとして機能します。つまり、この列の各値は、それぞれの行の一意の識別子として機能します。
• runner_name：2人のレーサー、ボルトとフェリックスの名前に最大30文字のvarcharデータ型を使用します。
• race_name：varcharデータ型で最大20文字の種族の種類を保持します。
• start_day：DATEデータ型を使用して、特定のレースの日付を年、月、日で追跡します。 このデータ型は、次のパラメーターに準拠しています。年は4桁、月と日は最大2桁（YYYY-MM-DD）。
• start_time：TIMEデータ型のレース開始時刻を時間、分、秒（HH:MM:SS）で表します。 このデータ型は、午後3時に相当する15:00などの24時間形式に従います。
• total_miles：decimalデータ型を使用して、各レースの合計マイレージを表示します。これは、レースごとの合計マイルの多くが整数ではないためです。 この場合、decimalは1のスケールで3の精度を指定します。つまり、この列の値は3桁で、そのうちの1桁は小数点の右側にあります。
• end_time：TIMESTAMPデータ型を使用して、レース終了時のランナーの時間を追跡します。 このデータ型は、日付と時刻の両方を1つの文字列に結合し、その形式は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');

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

データを挿入すると、SQLで日付と時刻を使用していくつかの算術関数と関数の練習を開始する準備が整います。

日付と時刻での算術の使用

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

別の例として、2つの異なる時間の間の合計時間を計算するとします。 これを行うには、2回を互いに減算します。 次のクエリでは、11:00が最初の値であり、3:00が2番目の値です。 ここでは、時間の差を返すために、両方が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の差が80000、つまり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)

この出力が示すように、11日前のcurrent_date（この記事の執筆時点）は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)

日付または時刻から間隔を差し引いて、指定された日付値のbeforeから値を見つけることもできることに注意してください。

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番目の例を思い出してください。次のクエリを実行して、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つの列（[X86X]は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つにネストする必要があることに注意してください。 最後に、 GROUP BY 句を追加して、runner_name列に基づいてこれらの値を整理し、出力に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)

この出力は、各ランナーの最短実行時間を示しています。この場合、ボルトの場合は最低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)

この出力は、Boltの最長実行時間が合計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秒になります。 ボルトは6時間9分20秒です。 これは、同じクエリとデータセットを使用している場合でも、さまざまなDBMS実装がデータ値を異なる方法で解釈する方法の例です。

結論

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