MySQLのDateTime型とTimeStamp型の違い:
俺が主に気にするのは、タイムゾーンについて。(同じテーブルで、DateTime型とTimeStamp型を混ぜてしまったので、気にしている。)
- datetime型はタイムゾーンの概念がないデータでMySQLに格納される。
以下のように、CONVERT_TZ()により、ある種の手動で時差を算出できる(データにタイムゾーンがないので、抽出しようとしているデータがどのタイムゾーンか、手動で指定しなくてはならない)。
DATE_FORMAT(CONVERT_TZ(date_time, 'Asia/Tokyo', 'America/New_York'), '%Y-%m-%d %H:%i:%s (%a)')
- timestamp型はタイムゾーンの概念があるようだ。
ただ、DATE_FORMAT()で可能なフォーマット指定ではタイムゾーンの出力はない。 MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数
どのように出力するのか すぐには見当たらない。(方法はない?)
なお、datetime型と同じ様にCONERT_TZ()で以下のように扱うことも出来る(但しこれは timestamp型のデータが持つタイムゾーンを無視しているので、よろしくない)。
DATE_FORMAT(CONVERT_TZ(address_verified_time, 'Asia/Tokyo', 'America/New_York'), '%Y-%m-%d %H:%i:%s (%a)')
ミリ秒の取扱いが、MySQL 5.5 ⇒ 5.6 で変わった(&フォーマット例)
MySQLでは、通常、マイクロ秒を格納できない(関数とかの演算上は扱えるがデータとして格納時に切り捨てられるとかそんな動き)。
5.5では、マイクロ秒は切り捨て、5.6以降は、四捨五入、という違い。
(なお、仕事(コーポレートWebサイト)で使っているのは、5.5。)
- これについて簡潔説明版: MySQL DATETIME型のミリ秒の扱いについて | データベース | DoRuby
- 詳細説明版: MySQL 5.6からDATETIMEは小数点以下が四捨五入(round)されるので気を付けよう - Qiita
また、5.6か5.7以上で、設定によりマイクロ秒で格納できるような記述を見た気がするが、勘違いかもしれない。
以下のように、ミリ秒が"%f"でフォーマットできるが、常に"000000"。
SELECT unsubscribed_time, DATE_FORMAT(unsubscribed_time, '%Y-%m-%d %H:%i:%s.%f (%a)')
⇒ 2019-02-26 13:04:36 2019-02-26 13:04:36.000000 (Tue)
ちなみに、PHPのミリ秒やタイムゾーン(のフォーマット)
以下の俺のコード(Utilクラス)参照。
// get the time to have started. (You can't get microtime with "new \DateTime()". See "http://jp.php.net/manual/ja/function.date.php#93891".)
public static function getDateTimeWithMicroSec($microtime=FALSE){
if($microtime===FALSE) $microtime = \microtime(TRUE);
list($utstamp, $micros) = \explode(".", \sprintf("%.06f", $microtime));
return new \DateTime( \date('Y-m-d H:i:s.',$utstamp) . $micros );
}
public static function getTimeStrWithMicroSec(
$format='Y-m-d H:i:s.u', // an example of TimeZone format: 'Y-m-d H:i:s.u O(T - e)' -> "2019-02-25 09:12:30.000000 +0900(JST - Asia/Tokyo)"
$microtime=FALSE
){
if($microtime===FALSE) $microtime = \microtime(TRUE);
$dateTime = self::getDateTimeWithMicroSec($microtime);
return $dateTime->format($format);
}
- ちょっと別件だが参考まで: mysqlのdatetime型カラムに対し、phpのdate()で使うには - つるながの綴方