timestamp=0よりも前の日時をinsertしようとして怒られたときの備忘録
できごと
いつものように怪しいWebアプリ作りに勤しんでいたら、とある怪しいWebアプリがエラーを吐いていた
Next Illuminate\Database\QueryException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '1970-01-01 07:30:17' for column 'xxx_at' at row 1 (SQL: xxx) in /web/xxx/vendor/laravel/framework/src/Illuminate/Database/Connection.php:770
データベースへレコードをinsertしようとしたときにエラーが発生し、LaravelのDBライブラリから例外が投げられてバッチがお亡くなりになっている模様
こうしてブログネタが見つかった、めでたしめでたし (w)
デバッグする
最初は Invalid datetime format と言われているので、バグか何かで日付として不正な値をinsertしようとしてエラーになったのかな？と思ってデータを確認した
1970-01-01 07:30:17
が、日付をよく見ても特に気になるところは無し
問題のない Y-m-d H:i:s 形式 (通称: 山田形式 )。ちゃんとパースできるはず
次に気になるのが 1970-01-01 と言う部分
ピンと来ました？ Unixtimeが始まった日です
日時まで見て見ると
1970-01-01 07:30:17
なるほど07:30、 すき家で朝食メニューが頼める時間
原因は完全にコレで、
php > print date_default_timezone_get();
Asia/Tokyo
php > print date('Y-m-d H:i:s', 0);
1970-01-01 09:00:00
UNIX Timestamp 0 = JST 1970-01-01 09:00:00
つまり、 JST 1970-01-01 09:00:00よりも前はUNIX Timestampでは表現できない
とても当たり前だが、普段はJSTはあまり考えないので失念していた
対応を考える
テーブルには 1970-01-01 07:30:17 と言う日付をinsertしたいのに、カラム型の制約で Invalid datetime format と言われてしまう
パッと思いつく対応としては、
- 1/1 9時よりも前の日時を、一律9時きっかりに丸める
- 1/1 9時よりも前の日時を扱えるカラム型に変更する
といったもので、それぞれ
1: ビジネス的にそれが許されるなら、と言う事前条件がある 2: (ディスク容量的な意味で)データ量が増える
という難点がある
数千万件、数億件オーダのデータを扱うなら4バイト -> 8バイトへの変更は気になるだろうが、今回の場合は高々数百万件程度なので気にせずdatetime型へ変えてしまう
補足
今回リファレンスをちゃんと読むまで知らなかったのだが、timestamp型、datetime型カラムのデータ保持に必要なディスク容量が MySQL 5.6.4を境に 変わっていたΣ
詳しくはsee MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.7 データ型のストレージ要件
対応する
問題のカラムをtimestamp型からdatetime型へ変更する
- DDLだと
alter table some_table modify xxx_at datetime not null;
- Laravel Migration Fileだと
Schema::table('some_table', function (Blueprint $table) {
$table->datetime('xxx_at')->change();
});
今回のアプリを動かしているデータベースサーバはMySQL5.6以上なので、オンラインで上記DDLを実行しても他からの読み書きはブロックされない
see MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.11.1 オンライン DDL の概要
5.6未満のMySQLを運用中で人生が辛いですか？ We’re hiring!
まとめ
- JST 1970-01-01 09:00:00よりも前の日時はtimestamp型では扱えない
- 1970年1月1日前後の日付でエラーが出たら、timestamp型問題を疑ってみる
- どうしても扱いたかったらtimestamp型ではなくdatetime型を使う
- カラムの型変更はMySQL5.6以上ならノンブロッキングに実行可能
余談
本文中では触れていませんがデバッグの過程で、テーブルの型定義とアプリから入れようとしている日付のフォーマットが違っていて怒られているのかと思って調べていたら、自称エンジニアな方が
そういう時は
set SQL_MODE = '';すればいいんだよ！
って言っているのをいくつか見かけて、とても強く生きたい気持ちになった
一方で、SQLモードについて詳しく解説してくださっている記事も見つけて、MySQLについて学びがあった
MySQLの自動変換を丁重にお断りするためのたった１種類の呪文 - sakaikの日々雑感～(T)編
感謝++
