日付操作の極意！MySQLでTIMESTAMP型の日付差を分単位で計算
MySQL で 2 つの日付間の差を分単位で計算する方法
MySQL では、様々な日付時刻操作関数を提供しています。その中でも、2 つのの日付間の差を分単位で計算する方法はいくつかあります。ここでは、代表的な 2 つの方法をご紹介します。
方法 1: TIMEDIFF() 関数を使用する
TIMEDIFF() 関数は、2 つの日時値の差を時間単位で返します。引数として、開始日時と終了日時を指定する必要があります。分単位で差を知りたい場合は、以下の式を使用します。
SELECT TIMEDIFF(MINUTE, start_datetime, end_datetime) AS minutes_diff;
例
以下のクエリは、'2024-07-09 10:00:00' と '2024-07-09 12:30:00' の間の差を分単位で計算します。
SELECT TIMEDIFF(MINUTE, '2024-07-09 10:00:00', '2024-07-09 12:30:00') AS minutes_diff;
このクエリは、以下の結果を返します。
150
方法 2: DATEDIFF() 関数と HOUR() 関数を使用する
DATEDIFF() 関数は、2 つの日付間の差を日数で返します。HOUR() 関数は、日時値の時刻部分を抽出します。
以下の式は、2 つのの日付間の差を分単位で計算する方法です。
SELECT (DATEDIFF(DAY, start_date, end_date) * 24 + HOUR(end_datetime) - HOUR(start_datetime)) * 60 + MINUTE(end_datetime) - MINUTE(start_datetime) AS minutes_diff;
SELECT (DATEDIFF(DAY, '2024-07-09', '2024-07-10') * 24 + HOUR('2024-07-10 00:00:00') - HOUR('2024-07-09 00:00:00')) * 60 + MINUTE('2024-07-10 00:00:00') - MINUTE('2024-07-09 00:00:00') AS minutes_diff;
1440
- 上記の例では、start_datetime と end_datetime のパラメータに文字列リテラルを使用していますが、実際にはテーブルのカラムから値を参照することもできます。
- TIMEDIFF() 関数は、start_datetime が end_datetime より後の場合、負の値を返します。
- DATEDIFF() 関数と HOUR() 関数を使用する方法は、より汎用性がありますが、TIMEDIFF() 関数よりも処理速度が遅くなる場合があります。
-- サンプルデータ
CREATE TABLE `dates` (
`id` INT PRIMARY KEY AUTO_INCREMENT,
`start_date` DATE NOT NULL,
`end_date` DATE NOT NULL
);
INSERT INTO `dates` (`start_date`, `end_date`) VALUES
('2024-07-09', '2024-07-10'),
('2023-01-01', '2023-12-31');
-- 2 つの日付間の差を分単位で計算
SELECT
`id`,
DATEDIFF(DAY, `start_date`, `end_date`) * 24 * 60 +
(HOUR(`end_date`) - HOUR(`start_date`)) * 60 +
MINUTE(`end_date`) - MINUTE(`start_date`) AS minutes_diff
FROM `dates`;
id | minutes_diff
---|------------
1 | 1440
2 | 52560
説明
- 最初に、
datesという名前のテーブルを作成します。このテーブルには、id、start_date、end_dateという 3 つのカラムがあります。 - 次に、2 つのサンプルデータレコードをテーブルに挿入します。
- 最後に、
DATEDIFF()関数、HOUR()関数、および算術演算子を使用して、2 つの日付間の差を分単位で計算するクエリを実行します。
このコードは、start_date が end_date より前の場合でも動作するように修正されています。
-- サンプルデータ (上記と同じ)
-- 2 つの日付間の差を分単位で計算
SELECT
`id`,
TIMEDIFF(MINUTE, `start_date`, `end_date`) AS minutes_diff
FROM `dates`;
id | minutes_diff
---|------------
1 | 1440
2 | 52560
ADDDATE() 関数は、指定された時間間隔を日付値に加算します。SUBDATE() 関数は、指定された時間間隔を日付値から減算します。以下の式を使用して、2つの日付間の差を分単位で計算することができます。
SELECT (ADDDATE(end_datetime, INTERVAL -MINUTE(end_datetime) + MINUTE(start_datetime) MINUTE) - start_datetime) * 24 * 60 AS minutes_diff;
SELECT (ADDDATE('2024-07-09 12:30:00', INTERVAL -MINUTE('2024-07-09 12:30:00') + MINUTE('2024-07-09 10:00:00') MINUTE) - '2024-07-09 10:00:00') * 24 * 60 AS minutes_diff;
150
DATE_FORMAT() 関数を使用する
DATE_FORMAT() 関数は、日付値をフォーマットされた文字列に変換します。以下の式を使用して、2つの日付間の差を分単位で計算することができます。
SELECT ABS(DATE_FORMAT(end_datetime, '%i') - DATE_FORMAT(start_datetime, '%i')) * 60 +
(DATE_FORMAT(end_datetime, '%M') - DATE_FORMAT(start_datetime, '%M')) AS minutes_diff;
SELECT ABS(DATE_FORMAT('2024-07-09 12:30:00', '%i') - DATE_FORMAT('2024-07-09 10:00:00', '%i')) * 60 +
(DATE_FORMAT('2024-07-09 12:30:00', '%M') - DATE_FORMAT('2024-07-09 10:00:00', '%M')) AS minutes_diff;
150
ストアドプロシージャを使用する
ストアドプロシージャは、事前に定義された一連の SQL ステートメントです。以下のストアドプロシージャは、2つの日付間の差を分単位で計算します。
CREATE PROCEDURE calculate_minutes_diff(
IN start_date DATE,
IN end_date DATE,
OUT minutes_diff INT
)
BEGIN
SET minutes_diff = DATEDIFF(DAY, start_date, end_date) * 24 * 60 +
(HOUR(end_date) - HOUR(start_date)) * 60 +
(MINUTE(end_date) - MINUTE(start_date));
END PROCEDURE;
このストアドプロシージャを呼び出すには、以下のクエリを使用します。
CALL calculate_minutes_diff('2024-07-09', '2024-07-10', @minutes_diff);
SELECT @minutes_diff AS minutes_diff;
1440
どの方法を選択すべきか
使用する方法は、状況によって異なります。
- TIMEDIFF() 関数は、最もシンプルでわかりやすい方法です。
mysql sql timestamp
