PostgreSQL の日時型はタイムゾーンをつけたおいたほうがいいかも
◆ タイムゾーンの問題を避けたくてタイムゾーンなしにしたらタイムゾーンの問題が起きた
◆ 文字列中のタイムゾーン情報があっても捨てられるので UTC 化された日時文字列を使ってはいけない
◆ 2022-02-02T02:02:02.222Z みたいなの
◆ JavaScript で Date を JSON 化するとこうなる
◆ タイムゾーンに応じた文字列にするか タイムゾーンをありにしておけば問題ない
◆ 文字列中のタイムゾーン情報があっても捨てられるので UTC 化された日時文字列を使ってはいけない
◆ 2022-02-02T02:02:02.222Z みたいなの
◆ JavaScript で Date を JSON 化するとこうなる
◆ タイムゾーンに応じた文字列にするか タイムゾーンをありにしておけば問題ない
PostgreSQL の日時型は timestamp with time zone と timetamp without time zone の 2 種類があります
タイムゾーンのありなしです
複数の国をまたぐサービスなんて作らないし 日本時間しか考える必要がないからといつもタイムゾーンはなしにしていました
余計な情報はいらないですし タイムゾーン関係で問題が出るのも嫌ですし
しかし 逆にタイムゾーンがないことで問題が出ました
タイムゾーン情報がある文字列をインサートしたときに +0900 でも +0000 (Z) でもすべて無視されます
UTC として文字列化されているデータをインサートすると実際の時間とずれます
特に Node.js では JSON 化するとすべて UTC となって 2022-02-02T05:00:00.000Z みたいな文字列になります
JSON ファイルを単純に読み取って INSERT すると時間が UTC との差分の 9 時間ずれるということが起きます
設定時の timezone と型指定の time zone でスペースの有無が異なるのが微妙なところです
一応 DB のタイムゾーンは日本にします
このタイムゾーン設定は 現在時刻を now() で取得してタイムゾーンなしのタイムスタンプ型にするときには考慮されますが 文字列からタイムゾーンなしのタイムスタンプ型にするときは考慮されません
Node.js からインサートしてみます
pg ライブラリを直接使っても良いですが 面倒なので knex を使ってます
Date 型でインサートするときはタイムゾーンのありなし関係なく インサートした時刻と同じ時刻のデータを取得できます
ただ タイムゾーンなしだと データにタイムゾーンが含まれないので 地域をアメリカ等に変更しても 12 時は 12 時のままです
日本で 12 時として保存したのだから アメリカの UTC -8 にしたら 前日の 19 時になって欲しい場合には使えません
今回はずっと日本前提なのでこのケースは考慮しません
JSON 化したデータを使ってインサートしてみます
タイムゾーンがある方 (tswtz) は UTC で 3 時なので 日本で 12 時です
タイムゾーンがない方 (tswotz) は UTC で 18 時なので 日本で 3 時です
9 時間ずれています
正しい情報を保存するためには JSON 化で出力される UTC 文字列ではなく日本のタイムゾーンでの文字列にするか Date 型に変換する必要があります
https://github.com/brianc/node-postgres/blob/pg%408.7.1/packages/pg/lib/utils.js#L55
こういう処理があって内部的に文字列にしているようです
if 文の条件の defaults.parseInputDatesAsUTC のデフォルトは false のようです
https://github.com/brianc/node-postgres/blob/pg%408.7.1/packages/pg/lib/defaults.js#L51
となると実行されるのは dateToString 関数です
関数の中身は
でした
UTC にせずタイムゾーンを考慮した文字列にしているようです
「2022-02-02T14:57:52.168+09:00」 のように日本時間で +0900 がついています
上の引用した関数を実行する場合は この関数だけで使おうとすると pad 関数がないエラーです
utils.js ファイルから pad 関数もコピーしてくる必要があります
ただ 今では padStart メソッドが使えるのでもっとシンプルに↓を使ってもいいです
しかし よく考えると日本のみでタイムゾーンを使わない前提だからタイムゾーンなしを選んだのに UTC の文字列が出てくるのが悪い気がします
それに JSON 化と言っても JSON では日付型というものはなく ただの文字列です
数値や真偽値のような専用の型ではありません
JSON 化で強制的に UTC にする Node.js が原因と言っても良いと思います
タイムゾーンの +0900 みたいなのがあれば 複数のタイムゾーンが混ざっても問題になりませんし UTC に揃える必要性もないです
Date 型にタイムゾーンがないならともかくタイムゾーン情報があるのですし そのタイムゾーンに応じた文字列にしてほしいです
他言語でも UTC 化が一般的なのかと見てみました
タイムゾーンを考慮して日本時間で +0900 がついています
+0900 がついた文字列ではなく オブジェクトになっていてタイムゾーン情報がプロパティとして追加されています
それでもベースとなる日時の文字列は日本時間です
JSON.stringify は JSON 化処理をカスタマイズできるので 変更してみます
dateToString は上の pg ライブラリのものを使ってます
日本時間前提なら + 以降のタイムゾーン情報はなしにしても良いです
UTC 化する標準の文字列化はいらないので Date.prototype.toJSON を置き換えてしまって replacer を不要にもできます
JSON 化の途中で Date 型かをひとつひとつ見なくて済む分 パフォーマンスはよくなりそうですが ライブラリ内の動作にも影響します
ライブラリが JSON 化する時でも日本時間にできるメリットはありますが そのライブラリが UTC を前提としてなにかする場合には意図しない動きになるかもしれません
タイムゾーンがあると余計に考慮する部分が増えると思って避けてましたが むしろあったほうが良いのかもしれません
余分に情報が増えるので データ量が増えるかもとも思いましたが 大したデータ量ではないはずです
一応ドキュメントを見に行ったら なんとどっちも 8 byte でサイズ的には一緒でした
どのタイムゾーンにしても 12 時は 12 時のままでないといけない みたいなケースを除けばとりあえずタイムゾーンはありにしておくで良い気がします
タイムゾーンのありなしです
複数の国をまたぐサービスなんて作らないし 日本時間しか考える必要がないからといつもタイムゾーンはなしにしていました
余計な情報はいらないですし タイムゾーン関係で問題が出るのも嫌ですし
しかし 逆にタイムゾーンがないことで問題が出ました
タイムゾーン情報がある文字列をインサートしたときに +0900 でも +0000 (Z) でもすべて無視されます
UTC として文字列化されているデータをインサートすると実際の時間とずれます
特に Node.js では JSON 化するとすべて UTC となって 2022-02-02T05:00:00.000Z みたいな文字列になります
JSON ファイルを単純に読み取って INSERT すると時間が UTC との差分の 9 時間ずれるということが起きます
試してみる
DB を用意しますcreate database testdb1;
alter database testdb1 set timezone to 'Asia/Tokyo';
\c testdb1;
create table t (tswtz timestamp with time zone, tswotz timestamp without time zone);
設定時の timezone と型指定の time zone でスペースの有無が異なるのが微妙なところです
一応 DB のタイムゾーンは日本にします
このタイムゾーン設定は 現在時刻を now() で取得してタイムゾーンなしのタイムスタンプ型にするときには考慮されますが 文字列からタイムゾーンなしのタイムスタンプ型にするときは考慮されません
postgres=# select now()::timestamp without time zone;
now
---------------------------
2022-02-02 05:30:15.74667
(1 row)
postgres=# select '2022-01-01T12:00:00+0900'::timestamp without time zone;
timestamp
---------------------
2022-01-01 12:00:00
(1 row)
postgres=# \c testdb1
You are now connected to database "testdb1" as user "postgres".
testdb1=# select now()::timestamp without time zone;
now
----------------------------
2022-02-02 14:30:27.226243
(1 row)
testdb1=# select '2022-01-01T12:00:00+0900'::timestamp without time zone;
timestamp
---------------------
2022-01-01 12:00:00
(1 row)
Node.js からインサートしてみます
pg ライブラリを直接使っても良いですが 面倒なので knex を使ってます
const pg = require("knex")({ client: "pg", connection: { user: "postgres", database: "testdb1" } })
const date = new Date()
// 2022-02-02T03:00:20.124Z
date.getTimezoneOffset()
// -540 (← JST)
await pg("t").insert({ tswtz: date, tswotz: date })
await pg("t")
// [
// { tswtz: 2022-02-02T03:00:20.124Z, tswotz: 2022-02-02T03:00:20.124Z }
// ]
Date 型でインサートするときはタイムゾーンのありなし関係なく インサートした時刻と同じ時刻のデータを取得できます
ただ タイムゾーンなしだと データにタイムゾーンが含まれないので 地域をアメリカ等に変更しても 12 時は 12 時のままです
日本で 12 時として保存したのだから アメリカの UTC -8 にしたら 前日の 19 時になって欲しい場合には使えません
今回はずっと日本前提なのでこのケースは考慮しません
JSON 化したデータを使ってインサートしてみます
const str_date = JSON.parse(JSON.stringify(date))
// '2022-02-02T03:00:20.124Z'
await pg("t").delete()
await pg("t").insert({ tswtz: str_date, tswotz: str_date })
await pg("t")
// [
// { tswtz: 2022-02-02T03:00:20.124Z, tswotz: 2022-02-01T18:00:20.124Z }
// ]
タイムゾーンがある方 (tswtz) は UTC で 3 時なので 日本で 12 時です
タイムゾーンがない方 (tswotz) は UTC で 18 時なので 日本で 3 時です
9 時間ずれています
正しい情報を保存するためには JSON 化で出力される UTC 文字列ではなく日本のタイムゾーンでの文字列にするか Date 型に変換する必要があります
pg ライブラリ
どうして Date 型ならうまくいくのかとライブラリの中を見てみましたhttps://github.com/brianc/node-postgres/blob/pg%408.7.1/packages/pg/lib/utils.js#L55
if (val instanceof Date) {
if (defaults.parseInputDatesAsUTC) {
return dateToStringUTC(val)
} else {
return dateToString(val)
}
}
こういう処理があって内部的に文字列にしているようです
if 文の条件の defaults.parseInputDatesAsUTC のデフォルトは false のようです
https://github.com/brianc/node-postgres/blob/pg%408.7.1/packages/pg/lib/defaults.js#L51
となると実行されるのは dateToString 関数です
関数の中身は
function dateToString(date) {
var offset = -date.getTimezoneOffset()
var year = date.getFullYear()
var isBCYear = year < 1
if (isBCYear) year = Math.abs(year) + 1 // negative years are 1 off their BC representation
var ret =
pad(year, 4) +
'-' +
pad(date.getMonth() + 1, 2) +
'-' +
pad(date.getDate(), 2) +
'T' +
pad(date.getHours(), 2) +
':' +
pad(date.getMinutes(), 2) +
':' +
pad(date.getSeconds(), 2) +
'.' +
pad(date.getMilliseconds(), 3)
if (offset < 0) {
ret += '-'
offset *= -1
} else {
ret += '+'
}
ret += pad(Math.floor(offset / 60), 2) + ':' + pad(offset % 60, 2)
if (isBCYear) ret += ' BC'
return ret
}
でした
UTC にせずタイムゾーンを考慮した文字列にしているようです
「2022-02-02T14:57:52.168+09:00」 のように日本時間で +0900 がついています
上の引用した関数を実行する場合は この関数だけで使おうとすると pad 関数がないエラーです
utils.js ファイルから pad 関数もコピーしてくる必要があります
ただ 今では padStart メソッドが使えるのでもっとシンプルに↓を使ってもいいです
const pad = (s, n) => String(s).padStart(n, "0")
他言語
こういう問題があるので PostgreSQL で日時型を使うときはタイムゾーンをありにしておくほうが余計な問題が起きずにいいかなと思いましたしかし よく考えると日本のみでタイムゾーンを使わない前提だからタイムゾーンなしを選んだのに UTC の文字列が出てくるのが悪い気がします
それに JSON 化と言っても JSON では日付型というものはなく ただの文字列です
数値や真偽値のような専用の型ではありません
JSON 化で強制的に UTC にする Node.js が原因と言っても良いと思います
タイムゾーンの +0900 みたいなのがあれば 複数のタイムゾーンが混ざっても問題になりませんし UTC に揃える必要性もないです
Date 型にタイムゾーンがないならともかくタイムゾーン情報があるのですし そのタイムゾーンに応じた文字列にしてほしいです
他言語でも UTC 化が一般的なのかと見てみました
Python
>>> import datetime
>>> import zoneinfo
>>>
>>> datetime.datetime.now(zoneinfo.ZoneInfo("Asia/Tokyo"))
datetime.datetime(2022, 2, 2, 14, 1, 55, 555023, tzinfo=zoneinfo.ZoneInfo(key='Asia/Tokyo'))
>>>
>>> str(datetime.datetime.now(zoneinfo.ZoneInfo("Asia/Tokyo")))
'2022-02-02 14:02:01.587156+09:00'
>>>
>>> import json
>>> json.dumps({"now": datetime.datetime.now(zoneinfo.ZoneInfo("Asia/Tokyo"))}, default=str)
'{"now": "2022-02-02 14:03:07.039402+09:00"}'
タイムゾーンを考慮して日本時間で +0900 がついています
PHP
$datetime = new DateTime(null, new DateTimeZone('Asia/Tokyo'));
echo $datetime->format('c');
// 2022-02-02T15:16:23+09:00
echo json_encode(['datetime' => $datetime]);
// {"datetime":{"date":"2022-02-02 15:16:23.710491","timezone_type":3,"timezone":"Asia\/Tokyo"}}
+0900 がついた文字列ではなく オブジェクトになっていてタイムゾーン情報がプロパティとして追加されています
それでもベースとなる日時の文字列は日本時間です
JavaScript でもタイムゾーンを考慮させる
探せば JavaScript と同様 UTC 化する言語もあるかもしれませんが PHP も Python もタイムゾーンを考慮してくれるようなので JavaScript もこれに合わせればいいと思うんですJSON.stringify は JSON 化処理をカスタマイズできるので 変更してみます
const replacer = function(k, v) {
if (this[k] instanceof Date) return dateToString(this[k])
return v
}
JSON.stringify({ date: new Date("2022-02-01 15:00:00") }, replacer)
// '{"date":"2022-02-01T15:00:00.000+09:00"}'
dateToString は上の pg ライブラリのものを使ってます
日本時間前提なら + 以降のタイムゾーン情報はなしにしても良いです
UTC 化する標準の文字列化はいらないので Date.prototype.toJSON を置き換えてしまって replacer を不要にもできます
Date.prototype.toJSON = function() {
return dateToString(this)
}
JSON.stringify({now: new Date()})
// '{"now":"2022-02-02T14:05:48.018+09:00"}'
JSON 化の途中で Date 型かをひとつひとつ見なくて済む分 パフォーマンスはよくなりそうですが ライブラリ内の動作にも影響します
ライブラリが JSON 化する時でも日本時間にできるメリットはありますが そのライブラリが UTC を前提としてなにかする場合には意図しない動きになるかもしれません
タイムゾーンをつけるデメリットは
これで without time zone でも大丈夫になったと思いましたが 考えてみるとタイムゾーンありを使ったらいい気がしましたタイムゾーンがあると余計に考慮する部分が増えると思って避けてましたが むしろあったほうが良いのかもしれません
余分に情報が増えるので データ量が増えるかもとも思いましたが 大したデータ量ではないはずです
一応ドキュメントを見に行ったら なんとどっちも 8 byte でサイズ的には一緒でした
どのタイムゾーンにしても 12 時は 12 時のままでないといけない みたいなケースを除けばとりあえずタイムゾーンはありにしておくで良い気がします