Npgsql7.0.4を使ってTIMESTAMP WITH TIME ZONE列の更新を行ったとき、エラーが発生したのでその原因の確認
準備
適当なPostgreSQLサーバーに、テスト用のテーブルを準備
CREATE SEQUENCE test.test_timestamp_seq;
CREATE TABLE test.test_timestamp
(
id INTEGER NOT NULL DEFAULT NEXTVAL('test.test_timestamp_seq')
, with_timezone TIMESTAMP WITH TIME ZONE NOT NULL
, without_timezone TIMESTAMP WITHOUT TIME ZONE NOT NULL
, description TEXT
, CONSTRAINT pkey_test_timestamp PRIMARY KEY (id)
);
DateTimeKind.Localだとエラーになる
Npgsql7.0.4を参照に入れて(Nugetなどで持ってきて)以下のコードを実行
string npgsqlVersion = "Npgsql7";
Console.WriteLine(npgsqlVersion + " DateTimeKind.Local");
DateTime with_timezone = new DateTime(2023, 1, 1, 0, 0, 0, DateTimeKind.Local);
DateTime without_timezone = new DateTime(2023, 1, 1, 0, 0, 0, DateTimeKind.Local);
Console.WriteLine("with_timezone=" + with_timezone .ToString());
Console.WriteLine("without_timezone=" + without_timezone.ToString());
NpgsqlConnection conn = new NpgsqlConnection(～～～データベース接続情報～～～);
conn.Open();
try
{
NpgsqlCommand command = new NpgsqlCommand("INSERT INTO test.test_timestamp (with_timezone, without_timezone, description) VALUES (:with_timezone, :without_timezone, :description)", conn);
NpgsqlParameter param_with_timezone = command.Parameters.Add("with_timezone" , NpgsqlTypes.NpgsqlDbType.TimestampTz);
NpgsqlParameter param_without_timezone = command.Parameters.Add("without_timezone", NpgsqlTypes.NpgsqlDbType.Timestamp );
NpgsqlParameter param_description = command.Parameters.Add("description" , NpgsqlTypes.NpgsqlDbType.Text );
param_with_timezone .Value = with_timezone;
param_without_timezone.Value = without_timezone;
param_description .Value = npgsqlVersion;
int result = command.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
}
command.ExecuteNonQuery()で例外が発生。
System.InvalidCastException: Cannot write DateTime with Kind=Local to PostgreSQL type 'timestamp with time zone', only UTC is supported. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
TIMESTAMP WITH TIME ZONEの列に対しては、DateTimeKind.Utcじゃないとダメみたい。
TIMESTAMP型だとDateTimeKind.Utcでエラーになる
上記コードを一部修正
DateTime with_timezone = new DateTime(2023, 1, 1, 0, 0, 0, DateTimeKind.Utc);
DateTime without_timezone = new DateTime(2023, 1, 1, 0, 0, 0, DateTimeKind.Utc);
これで実行すると
System.InvalidCastException: Cannot write DateTime with Kind=UTC to PostgreSQL type 'timestamp without time zone', consider using 'timestamp with time zone'. Note that it's not possible to mix DateTimes with different Kinds in an array/range. See the Npgsql.EnableLegacyTimestampBehavior AppContext switch to enable legacy behavior.
今度がTIMESTAMP(WITHOUT TIME ZONE)に、DateTimeKind.UtcはダメでDateTimeKind.Localを使えということらしい。
TIMESTAMP WITH TIME ZONEをDateTimeKind.UtcにTIMESTAMPをDateTimeKind.Localに
上記コードを一部修正
DateTime with_timezone = new DateTime(2023, 1, 1, 0, 0, 0, DateTimeKind.Utc);
DateTime without_timezone = new DateTime(2023, 1, 1, 0, 0, 0, DateTimeKind.Local);
これでエラーは発生しなかった。
テーブルの内容を確認すると
xxxx=> SELECT * FROM test_timestamp;
id | with_timezone | without_timezone | description
----+------------------------+---------------------+-------------
1 | 2023-01-01 09:00:00+09 | 2023-01-01 00:00:00 | Npgsql7
TIMESTAMP WITH TIME ZONEの列(with_timezone)には日本時間の2023-01-01 9:00が書き込まれている。
確かに、変数のwith_timezoneにはUTCの2023-01-01 0:00が入っているので、タイムゾーンが日本のTIMESTAMP WITH TIME ZONEの列には2023-01-01 9:00が書き込まれているのは結果としては正しい。。。
いやいや、タイムゾーンが日本になっているPCで日本時間をTIMESTAMP WITH TIME ZONEの列に書き込みたいんだよ。
DateTimeKind.Localの日時は、Npgsql側でUtcに変換して更新してほしい気がするけど、やってくれないのなら事前にDateTime型の変数の値をUTCに変換した上でDateTimeKindをUtcに変更してってことをやる必要があるのかな。
でも、以前はそんなことしてなかった気がする。
Npgsql5.xでは？
最初のコードをNpgsql5.xで試してみました。
そうすると例外も発生せず、テーブルには
xxxx=> SELECT * FROM test_timestamp;
id | with_timezone | without_timezone | description
----+------------------------+---------------------+-------------
1 | 2023-01-01 00:00:00+09 | 2023-01-01 00:00:00 | Npgsql5
(1 rows)
という値が書き込まれました。
これで何も問題ないんだけど、なぜNpgsql7.x(どうやら6.xから)では仕様が変更(？)されたのか？
どういう理由なのか、ちょっとよくわからないところです。
すでにデータの入っているテーブルの型をTIMESTAMP WITH TIME ZONEからTIMESTAMP WITHOUT TIME ZONEに変更したらどうなるか？
開発しているソフトでNpgsql5.xからNpgsql7.xに変更した場合、TIMESTAMP WITH TIME ZONEの列に対する更新は例外が発生するようになってしまうおそれがあります。
といって、DateTimeKind.Utcに変更して例外を回避できたとしても、更新されるデータはUTCとして扱われるのでこちらの想定しているデータでは更新できない。
では、テーブル側の列の型をTIMESTAMP WITH TIME ZONEからTIMESTAMP WITHOUT TIME ZONEに変更してしまえば(異なるタイムゾーンからアクセスするようなシステムでなければ)なんとかなるか？
実際にやってみました。
変更前のデータは
xxxx=> SELECT * FROM test_timestamp;
id | with_timezone | without_timezone | description
----+------------------------+---------------------+-------------
1 | 2023-01-01 00:00:00+09 | 2023-01-01 00:00:00 | Npgsql5
(1 rows)
で、
ALTER TABLE test.test_timestamp ALTER COLUMN with_timezone TYPE TIMESTAMP WITHOUT TIME ZONE;
を実行。テーブルの中身は
xxxx=> SELECT * FROM test_timestamp;
id | with_timezone | without_timezone | description
----+---------------------+---------------------+-------------
1 | 2023-01-01 00:00:00 | 2023-01-01 00:00:00 | Npgsql5
(1 rows)
となりました。タイムゾーンの情報がなくなっただけで、日時はそのままでした。
コード内のNpgsqlParameterの型もNpgsqlTypes.NpgsqlDbType.TimestampTzからNpgsqlTypes.NpgsqlDbType.Timestampに変更する必要はあるけど、最小限の修正でなんとかなるかも。
(Npgsql5.xを使い続けるのがよいんだろうけど)