データベースからの日付扱い│システムガーディアン株式会社

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

1 データ型の確認
- 1.1 スクリプト側の日付
- 1.2 MySQL側の日付のデータ型
2 データ型見直して思うこと
3 続いて、変換例
4 DATETIMEとTIMESTAMPの違い
- - 4.2.1 UTC変換

データ型の確認

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

スクリプト側の日付

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

MySQL側の日付のデータ型

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

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年１月19日が最高値だったんですね。もっともTIMESTAMPの扱いはUTCなのありますので、クラウドサーバーが多くなってきた最近は慎重に使いたいですね。それとYEAR型、これはなかなか使い道が見えないですね。

続いて、変換例

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

//現在のUNIXTIME
echo time();
$unix_time = time(); // UNIXTIME格納
$sla_time  = '1999/1/1';
//UNIXTIMEから　0000/00/00 00:00:00へ
echo date('Y/m/d H:i:s', $unix_time);
//0000/00/00 00:00:00 からUNIXITMEへ
echo strtotime($sla_time);
//UNIXTIMEから　DATETIME型やTIMESTAMP型へ （0000-00-00 00:00:00）
echo date('Y-m-d H:i:s', $unix_time);

DATETIMEとTIMESTAMPの違い

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

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

UTC変換

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