MySQLだけで次のことを実現したい
- あるカラムAの時間(DateTime)、
- あるカラムBの時間(DateTime)、
- この２つの差分を取りたい
- ただしMySQLクエリだけを使うこと
やり方を知らなかったらカラムA／Bの時間をクエリごとに取得し、それをPHP・Pythonなどスクリプトで計算！みたいな遠回りをしがちです。
ところがMySQLには便利なのが用意されてます。
その名もTIMEDIFF()という関数
この関数が本当に便利であるので、
知らない人のためにTIMEDIFF()をまとめます。
このページの目次
MySQLで時間差分取得できるTIMEDIFF関数の概要
MySQLで時間差分を取得できる関数です。
▼ 公式リファレンスでの解説
TIMEDIFF(expr1,expr2)
TIMEDIFF() は、時間値として表現された expr1 ? expr2 を返します。expr1 および expr2 は時間または日付時間式ですが、両方とも同じ型にする必要があります。TIMEDIFF() で返される結果は、TIME 値で許可される範囲に制限されています。また、TIMESTAMPDIFF() および UNIX_TIMESTAMP() 関数のいずれかを使用することもできます。両方とも整数を返します。
引用元 ： https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_timediff
▼ シンプルな使用例も載っていた
|
1 2 3 4 5 6 |
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', -> '2000:01:01 00:00:00.000001'); -> '-00:00:00.000001' mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001', -> '2008-12-30 01:01:01.000002'); -> '46:58:57.999999' |
説明するまでもないほどシンプルな関数
ただし expr1 、 expr2 は同じ型にすべきとあるので、両方ともDateTime型あるいはDate型である必要があります。（ちなみに TIMESTAMP型は2038年問題のリスク があるから使うのは避けるべき）
また expr1 > expr2 のときは '46:58:57.999999' のような値が返り、expr1 < expr2 のときは '-00:00:00.000001' のようなマイナス時間表現が返るようです
これは便利だなと改めて思いました。
知らなかったらPHP側でアレコレしちゃうかも。
MySQLだけで完結できるのが最強の利点です。
この関数はNOW()と一緒に使えそうだなと思う
あとMySQLにはNOW関数もあります。
▼ リファレンスでのNOW関数の解説
NOW([fsp])
関数が文字列と数値のどちらのコンテキストで使用されているのかに応じて、現在の日付と時間を 'YYYY-MM-DD HH:MM:SS' または YYYYMMDDHHMMSS 書式の値として返します。値は、現在のタイムゾーンで表現されています。
MySQL 5.6.4 の時点では、0 から 6 までの小数秒の精度を指定するために fsp 引数が指定されている場合は、その桁数の小数秒部分が戻り値に含まれます。5.6.4 よりも前では、すべての引数が無視されます。
引用元 ： https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_now
これを使用すると '2007-12-15 23:50:26' のような形式の時間文字列が返ってきます。（ただしタイムゾーンはMySQL側のものが使用される）
これとTIMEDIFF()を組み合わせると便利そうです。
▼ 組み合わせて使う使用例
|
1 |
SELECT TIMEDIFF(NOW(), '2022-10-11 09:27:00') |
現在時刻 NOW() から時間カラム（例えば登録時刻の created_at カラムなど）を引いた時間差分を取ったり……こういうケースは多いと思います。
PHPコードからMySQL操作時のTIMEDIFF()の応用例
少し実用的なコード例とか考えてみます。
例えばPHPからMySQLを操作してる場合です。
▼ こんなIDと登録時刻だけのテーブルを作る
|
1 2 3 4 5 |
CREATE TABLE `hogehoge`. ( `id` BIGINT NOT NULL , `created_at` DATETIME NOT NULL , PRIMARY KEY (`id`) ) |
▼ 適当にレコードを挿入した
|
1 2 3 4 5 6 7 |
------------------------------ id | created_at ------------------------------ 1 | 2022-01-15 12:34:56 2 | 2022-05-27 19:23:01 3 | 2022-10-11 02:11:37 ------------------------------ |
この時、登録日時（created_atカラム）に対して現在時刻からどれだけ経過してるのか、すなわち時間差分を取りたいとします。
それなら次のPHPコードを書けば簡単です。
▼ 時間差分取得のPHPコード例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
/// id=1の時間差分をtimediffとして取得 $stmt = $connection->prepare( "SELECT TIMEDIFF(NOW(), (". "SELECT created_at FROM hogehoge WHERE id=?". ")) AS timediff" ); $id = 1 $stmt->bind_param('s', $id); $stmt->execute(); $result = $stmt->get_result()->fetch_assoc(); /// hhmmss表現を秒数に直す $difftime = $result['timediff']; $time = explode(':', $difftime); $time_in_secs = $time[0]*3600+$time[1]*60+$time[2]; echo "time_in_secs : " . $time_in_secs; |
クエリ１つだけで時間差分が取得できました。
あとは取得差分を利用しやすい形に変換するだけです。
時間差分にはTIMEDIFF()を絶体に使うべき
もしこれをTIMEDIFF()を使ってなかったら……
▼ こんな遠回りすぎるコードを書くことになる
- 現在時刻をPHPコード側で取得
- 特定のカラムのcreated_atカラムを取得
- 両者の時間差分をコード側で計算する
ありえないほど遠回りで面倒くさいことになります。
一方のTIMEDIFF()なら1クエリだけで取得できる、
その上PHPコードではその結果を受け取るだけでいい。
知らないと知ってるでは大きな差が出てきます。
