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

投稿日: 2022-04-20 2022-04-20
タグ: Interactive, 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]

getdocs

8670