SQLで日付と時刻を操作する方法
序章
リレーショナルデータベースと構造化照会言語(SQL)を操作する場合、特定の日付または時刻を表す値を操作する必要がある場合があります。 たとえば、特定のアクティビティに費やされた合計時間を計算する必要がある場合や、数学演算子と集計関数を使用して日付または時刻の値を操作して合計または平均を計算する必要がある場合があります。
このチュートリアルでは、SQLで日付と時刻を使用する方法を学習します。 まず、算術演算を実行し、日付と時刻のみを使用してさまざまな関数を使用します。 SELECT
声明。 次に、サンプルデータに対してクエリを実行して練習し、実装方法を学習します。 CAST
出力を読みやすくするための関数。
前提条件
このチュートリアルを完了するには、次のものが必要です。
- Ubuntu 20.04を実行しているサーバーで、root以外のユーザーが
sudo
管理者権限とファイアウォールが有効になっています。 Ubuntu20.04を使用したサーバーの初期設定に従って開始します。 - MySQLがサーバーにインストールされ、保護されています。 これを設定するには、 Ubuntu20.04ガイドにMySQLをインストールする方法に従ってください。 このガイドは、このガイドのステップ3 で概説されているように、root以外のMySQLユーザーも設定していることを前提としています。
注:多くのリレーショナルデータベース管理システムは、独自のSQL実装を使用していることに注意してください。 このチュートリアルで概説されているコマンドはほとんどのRDBMSで機能しますが、MySQL以外のシステムでテストした場合、正確な構文または出力が異なる場合があります。
このチュートリアルで日付と時刻の使用を練習するには、サンプルデータがロードされたデータベースとテーブルが必要です。 挿入する準備ができていない場合は、次の MySQLへの接続とサンプルデータベースの設定セクションを読んで、データベースとテーブルを作成する方法を学ぶことができます。 このチュートリアルでは、このサンプルデータベースとテーブル全体を参照します。
MySQLへの接続とサンプルデータベースの設定
SQLデータベースがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します。
- ssh sammy@your_server_ip
次に、MySQLプロンプトを開き、置き換えます sammy
with your MySQL user account information:
- 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
:を使用しますvarchar
2人のレーサー、ボルトとフェリックスの名前の最大30文字のデータ型。race_name
:レースの種類を保持しますvarchar
最大20文字のデータ型。start_day
:を使用しますDATE
年、月、日ごとに特定のレースの日付を追跡するデータ型。 このデータ型は、次のパラメータに準拠しています。年は4桁、月と日は最大2桁(YYYY-MM-DD
).start_time
:でレース開始時間を表しますTIME
時間、分、秒によるデータ型(HH:MM:SS
). このデータ型は、次のような24時間形式に従います。15:00
午後3時相当。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)
この出力が示すように、17日後 2022-10-05
は 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時間。
次に、サンプルデータの日付と時刻の情報に算術演算を使用する練習をします。 最初のクエリでは、ランナーが各レースを終了するのにかかった合計時間を減算して計算します 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)
この出力が示すように、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)
この出力は、減算すると 11
から current_time
値、それは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番目の例を思い出してください。 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つにネストする必要があります。 最後に、 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の日付と時刻の加算と減算の算術のみを使用しましたが、任意の数式で日付と時刻の値を使用できます。 数式と集計関数のガイドから詳細を学び、日付と時刻のクエリで試してみてください。