| Tweet |  |
今回は小ネタ。
MySQL 5.5にて、DATETIME型（日付/時刻)を指定したカラム(列)の差を計算する方法です。
テスト環境：MySQL for Windows 5.5.29-log Win32 x86(show variablesで確認）
元データ(テーブル dest)
| id | time_s | time_e |
| 1 | 2013-01-01 14:00:00 | 2013-01-30 13:00:00 |
| 2 | 2013-01-01 14:00:00 | 2013-02-28 13:00:00 |
| 3 | 2013-01-01 14:00:00 | 2012-12-01 13:00:00 |
| 4 | 2013-01-01 14:00:00 | 2012-11-01 13:00:00 |
いきなりですがまずは結果を見て下さい。(見やすいように行列反転しています)
| id | 1 | 2 | 3 | 4 |
| time_s | 2013-01-01 14:00:00 | 2013-01-01 14:00:00 | 2013-01-01 14:00:00 | 2013-01-01 14:00:00 |
| time_e | 2013-01-30 13:00:00 | 2013-02-28 13:00:00 | 2012-12-01 13:00:00 | 2012-11-01 13:00:00 |
| minus | 28990000.000000 | 126990000.000000 | -8900010000.000000 | -9000010000.000000 |
| minus2 | 28990000 | 126990000 | -8900010000 | -9000010000 |
| datediff | 29 | 58 | -31 | -61 |
| timediff | 695:00:00 | 838:59:59 | -745:00:00 | -838:59:59 |
| unixtimestamp | 2502000 | 5007600 | -2682000 | -5274000 |
| unixtimestamp_div1d | 28.958333 | 57.958333 | -31.041667 | -61.041667 |
上記表の画像版
各行の解説
- minus
SQLコード：(`dest`.`time_e` - `dest`.`time_s`)
単純にDATETIME型を引き算しただけ。結果として、DATETIME値を記号を除いて連結し数値化したフォーマット（'YYYY-MM-DD HH:MM:SS' → YYYYMMDDHHMMSS）同士の引き算であるため、結果が明らかにおかしな値となっている。 - minus2
SQLコード：(cast(`dest`.`time_e` as signed) - cast(`dest`.`time_s` as signed))
各DATETIME型を符号付きにキャストして減算。キャストにより整数値へと変換されるため、小数点以下が消えている。
それ以外はminusと同じで結果がおかしい。 - datediff
SQLコード：DATEDIFF((`dest`.`time_e` , `dest`.`time_s`))
もしくは (to_days(`dest`.`time_e`) - to_days(`dest`.`time_s`))
この関数は日付部分のみを参照（時刻部分は無視される）し、その差を計算して結果を日付型(DATE型)で返す。
日付部分の差だけを見る場合にはこれでも問題ない。
時間部分の差がマイナスであっても、日付の差には反映されないため、今回の目的には適さない。 - timediff
SQLコード：timediff(`dest`.`time_e`, `dest`.`time_s`)
この関数は、同じ日時型（双方ともDATE型、TIME型、DATETIME型）の差を計算し、結果を時間型(TIME型)で返す。
非常に惜しい結果となっている。
MySQL5.5において、時間型は-24:00:00～24:00:00の範囲を超えた値を保持できるようになっているのだが、その範囲は-838:59:59～838:59:59まで。
つまり、この範囲外の結果となったばあいは、結果がその上限/下限値に自動的に変更されてしまう。
838:59:59は34日と23時間より1秒少ない。なので、この範囲外の計算を行うには適さない。 - unixtimestamp
SQLコード：(unix_timestamp(`dest`.`time_e`) - unix_timestamp(`dest`.`time_s`))
各DATETIME型をTIMESTAMP型に変換したのちにその差を計算し、結果をTIMESTAMP型で返す。
結果自体は正常な値ではあるが、TIMESTAMP型である（1970/01/01 00:00:00 UTCからの経過秒数)ため、可読性に難があるのが欠点。
また、TIMESTAMP型に変換するときに、「接続ごとの」タイムゾーンで計算されるため、注意が必要。
（参考文献：MySQL :: MySQL 5.1 リファレンスマニュアル :: 11.5 日付時刻関数、MySQL :: MySQL 5.1 リファレンスマニュアル :: 4.10.8 MySQL サーバのタイム ゾーン サポート）
特に、夏時間を採用するタイムゾーンでは、この変換によって2つのDATETIME型の値が同一のTIMESTAMPに計算されることがあるため、特に注意が必要である。
また、TIMESTAMP型の上限は2^31-1、つまり2038-01-19 03:14:07 UTC もしくは 2038-01-19 12:14:07 JSTとされているため、これ以上の値となる場合は現在使用できない。
なおTIMESTAMP型の制限により、下限は1970/01/01 00:00:00 UTCである（=TIMESTAMP値が0）。FROM_UNIXTIMEで負の値または上限を超えた値を与えるとNULLが返ってくる。正の値しか受け付けないのだろう。今回の計算ではTIMESTAMP値に変換後の値を用いた単なる整数演算であるため、負の値になることは問題は無い。ただし加算時は注意が必要である。
（参考文献：MySQL :: MySQL 5.1 リファレンスマニュアル :: 10.3.1 DATETIME、DATE、そして TIMESTAMP タイプ） - unixtimestamp_div1d
SQLコード：(((unix_timestamp(`dest`.`time_e`) - unix_timestamp(`dest`.`time_s`)) * 1.00) / 86400)
先ほどのunixtimestampで計算した値を1日の秒数である86400で除算した値である。
Microsoft Excelでよく使われる、日付部分が整数+時間部分が小数となる値となる。
なお、1.00を掛けているのは意味がある。
もし*1.00が無い場合は、結果は小数点以下4桁しか出力されないのである（環境により異なるかもしれない）。
このため、秒数以下の値が正しく計算されないおそれがある。
このとき、1.00を掛けると小数点以下の桁数が6桁となり、秒数までカバーできるようになる。
1.00000...のように桁数を増やすほど結果の小数点以下の値が増えるが、今回は意味が無いので1.00としている。
もし、MySQLで日付・時刻計算に困っている方で、この記事がその手助けになれば幸いである。
Comments:0
Trackbacks:0
- TrackBack URL for this entry
- https://pc-diary.com/movt_direc_post/mt-tb.cgi/1620
- Listed below are links to weblogs that reference
- MySQL 5.5にて日時の差を計算する方法 from PC破壊日記的ブログ
