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

こんにちは。カトーです。スクリプト組むのに欠かせない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時間されています。海外のサーバーなど利用する時にタイムゾーンをデフォルトで使っていると合わなくて焦る事がありますので、気をつけましょう。

 

加藤

この記事を書いた人:加藤

一応会社の代表。薄毛に悩む41歳。
元々デザイナーだがスクリプトも組むしコンパイル系も書く。
社内で唯一のBASIC言語の経験者

この記事に関してのお問い合わせ
御連絡・ご返信は原則2営業日以内を予定しております。
お急ぎの場合は、お手数ですが下記電話でもご対応をしております。

システムガーディアン株式会社
受付時間:平日9:00~18:00
受付担当:坪郷(つぼごう)・加藤
電話:03-6758-9166