開発者ドキュメント

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

序章

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

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

前提条件

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

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

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

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

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

  1. ssh sammy@your_server_ip

次に、MySQLプロンプトを開き、置き換えます sammy with your MySQL user account information:

  1. mysql -u sammy -p

名前の付いたデータベースを作成します datetimeDB:

  1. CREATE DATABASE datetimeDB;

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

Output
Query OK, 1 row affected (0.01 sec)

を選択するには datetimeDB データベースは以下を実行します USE 声明:

  1. USE datetimeDB;
Output
Database changed

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

を実行してテーブルを作成します CREATE TABLE 指図:

  1. CREATE TABLE race_results (
  2. race_id int,
  3. runner_name varchar(30),
  4. race_name varchar(20),
  5. start_day DATE,
  6. start_time TIME,
  7. total_miles decimal(3, 1),
  8. end_time TIMESTAMP,
  9. PRIMARY KEY (race_id)
  10. );

次に、いくつかのサンプルデータを空のテーブルに挿入します。

  1. INSERT INTO race_results
  2. (race_id, runner_name, race_name, start_day, start_time, total_miles, end_time)
  3. VALUES
  4. (1, 'bolt', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:06:30'),
  5. (2, 'bolt', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:22:31'),
  6. (3, 'bolt', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 10:38:05'),
  7. (4, 'bolt', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 07:39:04'),
  8. (5, 'bolt', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 11:23:10'),
  9. (6, 'felix', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:07:15'),
  10. (7, 'felix', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:30:50'),
  11. (8, 'felix', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 11:10:17'),
  12. (9, 'felix', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 08:11:57'),
  13. (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では、数式を使用して日付と時刻の値を操作できます。 必要なのは、数学演算子と計算する値だけです。

例として、特定の日数が次々と続く日付を検索したいとします。 次のクエリは1つの日付値を取ります(2022-10-05)そして追加します 17 クエリで指定された日付から17日後の日付の値を返します。 この例では、 2022-10-05 として DATE DBMSが文字列またはその他のデータ型として解釈しないようにするための値:

  1. SELECT DATE '2022-10-05' + 17 AS new_date;
Output
+----------+ | new_date | +----------+ | 20221022 | +----------+ 1 row in set (0.01 sec)

この出力が示すように、17日後 2022-10-052022-10-22、または2022年10月22日。

別の例として、2つの異なる時間の間の合計時間を計算するとします。 これを行うには、2回を互いに減算します。 次のクエリの場合、 11:00 は初めての値であり、 3:00 2回目の値です。 ここでは、両方が TIME 時間の差を返すための値:

  1. 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:

  1. SELECT runner_name, race_name, end_time - start_time
  2. AS total_time
  3. 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より大きい:

  1. SELECT runner_name, race_name, end_time - start_time AS half_full_results
  2. FROM race_results
  3. 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_datecurrent_time 値。

たとえば、今日の日付を見つけるには、構文が短く、 SELECT ステートメントと current_date 次のように機能します。

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

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

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

出力で日付と時刻の両方を照会する場合は、 current_timestamp 関数:

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

前のセクションと同様の算術関数内で、これらのような日付と時刻の関数を使用できます。 たとえば、今日の日付から11日前の日付を知りたいとします。 この場合、以前にクエリを実行するために使用したのと同じ構文構造を使用できます。 current_date 関数してから減算する 11 それから11日前の日付を検索します。

  1. 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 関数:

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

この出力は、減算すると 11 から current_time 値、それは11秒を差し引きます。 以前に実行した操作 current_date インタプリタされた関数 11 秒ではなく日として。 日付と時刻の関数を操作するときに数値がどのように解釈されるかというこの不一致は、混乱を招く可能性があります。 多くのデータベース管理システムでは、このような算術演算を使用して日付と時刻の値を操作する必要はなく、 INTERVAL 式。

INTERVAL 式を使用すると、特定の日付または時刻の式から、設定された間隔の前後の日付または時刻を見つけることができます。 次の形式をとる必要があります。

間隔式の例
INTERVAL value unit

たとえば、今から5日後の日付を見つけるには、次のクエリを実行できます。

  1. 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)

これは、同じではありませんが類似した出力を生成する次のクエリよりもはるかに曖昧ではありません。

  1. SELECT current_date + 5 AS "5_days_from_today";
Output
+-------------------+ | 5_days_from_today | +-------------------+ | 20220306 | +-------------------+ 1 row in set (0.00 sec)

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

  1. 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:

  1. SELECT current_time + INTERVAL '6' HOUR AS "6_hours_from_now",
  2. current_time - INTERVAL '5' MINUTE AS "5_minutes_ago",
  3. 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 テーブルに設定されたデータ型:

  1. SELECT runner_name, race_name, end_time - start_time
  2. AS total_time
  3. 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 データ・タイプ:

  1. SELECT runner_name, race_name, CAST(end_time - start_time AS time)
  2. AS total_time
  3. 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人のランナーのレース結果を表示するように列を作成します。

  1. SELECT runner_name, MIN(CAST(end_time - start_time AS time)) AS min_time
  2. 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秒です。

次に、各ランナーの最長実行時間を見つけます。 前のクエリと同じ構文を使用できますが、今回は置き換えます MINMAX:

  1. SELECT runner_name, MAX(CAST(end_time - start_time AS time)) AS max_time
  2. 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 両方のランナーの結果の値を整理するには:

  1. SELECT runner_name, SUM(CAST(end_time - start_time AS time))
  2. 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でこれを試した場合、同じクエリはわずかに異なって見えます。

  1. SELECT runner_name, SUM(CAST(end_time - start_time AS time))
  2. 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の日付と時刻の加算と減算の算術のみを使用しましたが、任意の数式で日付と時刻の値を使用できます。 数式と集計関数のガイドから詳細を学び、日付と時刻のクエリで試してみてください。

モバイルバージョンを終了