Postgres の timestamp 型には 設定でタイムゾーンを設定することが出来る。
このタイムゾーンの扱いは若干面倒だったりするのでまとめ。
Postgres 側の設定
以下のSQL発行で タイムゾーンを確認できる。
Select NOW()
# -> 2017-04-11 09:15:35.611699+00
下記のコマンドでタイムゾーンをセットする
Set timezone TO 'Asia/Tokyo'
Select NOW();
# -> 2017-04-11 18:16:56.37643+09
タイムゾーンは接続ごとに保持されていて、 再接続するとまた +00 に戻る
タイムゾーンは DB単位で個別に設定することも出来る。
ALTER DATABASE homestead SET timezone TO 'Asia/Tokyo';
このやり方だと、デフォルトで 日本時間に変換される。
timestamp 列における with time / without time
Postgres の列には タイムゾーンを持たせることが出来る。
Laravel の migration 的には
-
datetime("created_at")-> timestamp without time zone -
datetimeTZ("created_at")-> timestamp with time zone
となる。
下記のようなデータ挿入において、
DB::table("m_message")->insert([
'message' => '日本は朝です',
'created_at' => '2017-01-01 09:00:00+09',
'updated_at' => '2017-01-01 09:00:00+09',
]);
DB::table("m_message")->insert([
'message' => 'イギリスは昼です',
'created_at' => '2017-01-01 12:00:00+00',
'updated_at' => '2017-01-01 12:00:00+00',
]);
下記のようなマイグレーションをする場合を考えてみる。
$this->timestampTz('created_at')->nullable();
$this->timestamp('updated_at')->nullable();
例えば データベースのtimezone が Asia/Tokyo の場合、
id | message | created_at | updated_at
----+------------------+------------------------+---------------------
1 | 日本は朝です | 2017-01-01 09:00:00+09 | 2017-01-01 09:00:00
2 | イギリスは昼です | 2017-01-01 21:00:00+09 | 2017-01-01 12:00:00
UTC の場合(途中で変更した場合を含む)、
id | message | created_at | updated_at
----+------------------+------------------------+---------------------
1 | 日本は朝です | 2017-01-01 00:00:00+00 | 2017-01-01 09:00:00
2 | イギリスは昼です | 2017-01-01 12:00:00+00 | 2017-01-01 12:00:00
と言った具合になる。
厳密に言うとPostgres の timestamp with TZ 型はその名前に反しタイムゾーンを保持しているわけではないそうでデータは全てUTCで保存される模様。
(詳しくは http://qiita.com/yuba/items/7852a29d63d6279c6f6e )
コンソールで叩いたときの表示は 接続 or データベースの Timezone 設定に従うようで、上記のようにバラバラのタイムゾーンで挿入し続けても、特定のTimeZone にキャストての表示が行われる。
逆に without TZ の列では時差情報は無視され、文字列で指定したとおりの時刻が表示され続ける。
timezone 表記の無い時刻の扱い
検索や insert などで '2017-01-01 09:00:00' 形式で時差フォーマットのない時刻データがやってきた場合、 データベースや接続のTimezone設定が使用される。
Laravel でどのように運用するか
単一のシステムの場合特に問題ないが面倒なのは、日付を UTC 固定で突っ込んでくるタイプの外部アプリケーションで、コレをどう裁くかが非常に重要になってくる。
timestamp without TZ列を運用する場合において UTC 固定で突っ込んで来るタイプの外部アプリケーションが存在するのであれば、データベースの設定を Asia/Tokyo 運用するのは非常に危険な気がする。
timestamp with TZ 列で運用する場合、Timezoneまで含めて +00 付きでセットしてくれるのであれば問題ないが、そうでないケース(Y-m-d H:i:s) も考慮すると悩ましい。Laravelのクエリビルダは Datetime 型を Y-m-d H:i:s にformat してSQLを生成する(時差情報を持たない)。
DBの設定は UTC固定にするとして、次に出てくる問題は、以下の２点
- Postico 等クライアントで UTC 表示になる不便さをどう解消するか
- Laravel 側の Timezone を Asia/Tokyoとする運用は可能か
Postico 等クライアント側の表示設定を考える
こちらは、以下のSQL文を発行することで接続毎に日本時間の表示を行わせることが出来る。
Set timezone TO 'Asia/Tokyo'
ただしこれが有効なのは timestamp with TZ列を運用しているときのみなので、migration 等は適宜 datetimeTZ などの TZ 末尾のメソドを選択しなければならない。
Laravel 側の Timezone を Asia/Tokyoとする運用は可能か
Carbon などのDatetime オブジェクトはY-m-d H:i:s形式で変換されてSQL化されるので、Laravel側の設定で Asia/Tokyo の設定をしていた場合、SQLは日本時間表記で発行される。
これは非常に困るので、以下の形式でクエリ生成のフォーマットを変更する。
Connection::resolverFor("pgsql",function($connection, $database, $prefix, $config){
$pgCon = new PostgresConnection($connection, $database, $prefix, $config);
$grammer = new class extends PostgresGrammar{
public function getDateFormat()
{
return "U";
}
};
$pgCon->setQueryGrammar($grammer);
return $pgCon;
});
残念なことに、Laravel の QueryLog を見ても、$bindingsはCarbonのままなので、デバッグは非常にやりづらい。
以上を踏まえて、とりあえずの結論としては、
- Database の 日付情報は全て UTC 固定で考える。
- Database の 日付情報は全て
timestamp with TZ列を用いる。 - Postico 等クライアント側で日本時刻の表示が必要であれば 接続毎に
SET timezoneする - Laravel側のタイムゾーンは全て Asia/Tokyo で運用する
- UTC Database へのクエリ発行で問題とならないよう、 SQL の日付に関する Grammer は 標準の
Y-m-d H:i:sからUに変更する。
という形で考えたのですが、何かこうした方が楽だよ、的なコメントもらえると助かります。
(特にGrammer のアタリとか コンテナ操作で代替したいのですが、なんとかならないでしょうか)
あとDBロケールの設定とか確認するのも面倒なのでちゃんとテスト書いとくといいなと思いました。