スクリプトとデータベースの日付扱い

データベースからの日付扱い

  • 2016年4月7日
  • 2017年4月5日
  • PHP, 加藤
  • 521view

こんにちは。カトーです。スクリプト組むのに欠かせないMySQLからデータ引っ張ってきて日付処理。特に差分など行うことはよくありますが、時々忘れるので、検証してみます。特に最近は海外のクラウドサービスなど多くなっていますので、意外な落とし穴もあります。

データ型の確認

スクリプト側では日付形式UNIXタイプスタンプで処理しますが、DBではdata型などありますので、ここはしっかりと両方をリストアップして確認します。

 

スクリプト側の日付

Unixタイム:unixtime、UNIX時間、UNIX時刻とは1970年1月1日(0時0分0秒)からの経過秒数でUNIX等のOS上で日時をあらわすのに用いられる。

 

MySQL側の日付のデータ型

MySQLでは主に5つの日付型があります。

  • DATE
  • TIME
  • DATETIME
  • TIMESTAMP
  • YEAR

DATE型

形式:’YYYY-MM-DD’
値の範囲:’1000-01-01′~ ‘9999-12-31’
時、分、秒などは扱えない。

TIME型

形式:’HH:MM:SS[.fraction]’
値の範囲:’-838:59:59.000000′ ~ ‘838:59:59.999999’
※[.fraction]は小数部
[.fraction]は小数部(マイクロ秒)

 .1とすれば、100ミリ秒 MySQL5.6.4から利用可能
MySQL 5.6.4 以降では、TIME カラムに挿入された値の小数部はすべて破棄されずに格納されます。小数部が含まれている場合、
TIME 値の範囲は '-838:59:59.000000''838:59:59.000000' です。
参照:時間値での小数秒

 

DATETIME

形式:’YYYY-MM-DD HH:MM:SS[.fraction]’
値の範囲:’1000-01-01 00:00:00.000000′ ~ ‘9999-12-31 23:59:59.999999’
※[.fraction]は小数部

 

TIMESTAMP

形式:’YYYY-MM-DD HH:MM:SS[.fraction]’
値の範囲:’1970-01-01 00:00:01.000000′ ~ ‘2038-01-19 03:14:07.999999’
※[.fraction]は小数部 UTCを内部に持ちます。

YEAR

形式:’HH:MM:SS[.fraction]’
値の範囲:’-838:59:59.000000′ ~ ‘838:59:59.999999’

 

 

データ型見直して思うこと

久しぶりに見直して小数点や年や時間に少数点以下の扱いが変更されていたのを見逃していました。それとTIMESTAMPは相変わらず2038年1月19日が最高値だったんですね。もっともTIMESTAMPの扱いはUTCなのありますので、クラウドサーバーが多くなってきた最近は慎重に使いたいですね。それとYEAR型、これはなかなか使い道が見えないですね。

 

続いて、変換例

PHPでの日付型の変換例を書いてみます。

 

DATETIMEとTIMESTAMPの違い

DATIMEとTIMESTAMPは同じでは?と考えますが、大きく違いがあります。

 

  • 値範囲   TIMESTAMPは表現範囲が小さいです。
  • データサイズ TIMESTAMPの方が小さく
    DATETIMEは5byte~8byte
    TIMESTAMPは4byte~7byteです。
     byte~と範囲が小数部のため
  •  UTC変換の有無 これが結構重要です。要は時差です。

UTC変換

UTC
つい忘れがちがですが、TIMESTAMPは内部でUTCを持っています。UTCは協定世界時、つまり時差です。格納する値は同じでも内部に時差も格納されているので、サーバーのタイムゾーンで変換された値が入っています。取り出すときに日本だと+9時間されています。海外のサーバーなど利用する時にタイムゾーンをデフォルトで使っていると合わなくて焦る事がありますので、気をつけましょう。

 

スクリプトとデータベースの日付扱い
最新情報をチェックしよう!
>システム構築・保守に特化した会社です。

システム構築・保守に特化した会社です。

システムの構築・保守運用「システムガーディアン」 社内システム担当が欲しいが、専属で雇うほどの仕事量はない。 必要な時に必要なだけ頼りたいというお悩みを持つ企業様へ専門知識を持って対応を行っております。 サーバから各種システムまで自社・他社で構築されたシステムに対してサポートを行っております。

CTR IMG