Amazon.co.jp ウィジェット MySQL 5.5にて日時の差を計算する方法 - PC破壊日記的ブログ

Home > PC破壊日記 > | 雑談 > MySQL 5.5にて日時の差を計算する方法

MySQL 5.5にて日時の差を計算する方法

このエントリーをはてなブックマークに追加

今回は小ネタ。
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

上記表の画像版
dest_result.gif

各行の解説

  • 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

Comment Form

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破壊日記的ブログ
  • タグ: DATE, DATETIME, MySQL, TIME, TIMESTAMP, UNIX_TIMESTAMP
  • Home > PC破壊日記 > | 雑談 > MySQL 5.5にて日時の差を計算する方法

    2進数時計
    ※クリックで読みやすくなります。
    ※この時計の時刻は、閲覧しているパソコンのものであり、必ずしも正確な時間とは限りません
    Search
    Feeds
    Google Adsense
    Tag Cloud

    このページの最初に戻る