PostgreSQLのTIMESTAMP WITH TIME ZONE型のデータを、ADO.NETとNpgsqlを使って扱いたい。
確認したいこと
台北にあるPostgreSQLサーバーのTIMESTAMP WITH TIME ZONE型のデータを、日本にあるクライアントからADO.NETを使ってデータの書き込み/取得を行い、日付が正しく扱えることを確認する。
準備
データベース・サーバーのタイムゾーンがAsia/Taipeiになっていることを確認。
確認のためのテーブルを生成
CREATE TABLE table1
(
id INTEGER
, date_tz TIMESTAMP WITH TIME ZONE
, description TEXT
);
ADO.NETを使ってデータベースにデータを書き込み、それを読み込む
次のソースを実行。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace pgTimestampTZ
{
class Program
{
static void Main(string[] args)
{
// データベース接続
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection("Server=xxxx;Port=5432;User Id=xxxx;Password=xxxx;Database=xxxx;Pooling=false;Encoding=UNICODE;");
conn.Open();
// データを入れる
using (Npgsql.NpgsqlCommand insert1 = new Npgsql.NpgsqlCommand("INSERT INTO table1 (id, date_tz, description) VALUES (:id, :date_tz, :description)", conn))
{
int id = 1;
DateTimeOffset date_tz = DateTimeOffset.Parse("2016-01-01 +09:00");
string description = "'2016-01-01 +09:00'";
Npgsql.NpgsqlParameter param_id = new Npgsql.NpgsqlParameter("id" , NpgsqlTypes.NpgsqlDbType.Integer );
Npgsql.NpgsqlParameter param_date_tz = new Npgsql.NpgsqlParameter("date_tz" , NpgsqlTypes.NpgsqlDbType.TimestampTZ);
Npgsql.NpgsqlParameter param_description = new Npgsql.NpgsqlParameter("description", NpgsqlTypes.NpgsqlDbType.Text );
insert1.Parameters.Add(param_id);
insert1.Parameters.Add(param_date_tz);
insert1.Parameters.Add(param_description);
param_id .Value = id;
param_date_tz .Value = date_tz;
param_description.Value = description;
insert1.ExecuteNonQuery();
}
// データを取得する
using (Npgsql.NpgsqlCommand select1 = new Npgsql.NpgsqlCommand("SELECT id, date_tz, description FROM table1 ORDER BY id", conn))
{
Npgsql.NpgsqlDataReader reader1 = select1.ExecuteReader();
while (reader1.Read())
{
var id = reader1["id" ];
var date_tz = reader1["date_tz" ];
var description = reader1["description"];
Console.WriteLine("id[" + id.GetType().ToString() + "]=" + id.ToString());
Console.WriteLine("date_tz[" + date_tz.GetType().ToString() + "]=" + date_tz.ToString());
Console.WriteLine("description[" + description.GetType().ToString() + "]=" + description.ToString());
}
}
// データベース切断
conn.Close();
Console.ReadKey();
}
}
}
Npgsql.NpgsqlCommand insert1 = new Npgsql.NpgsqlCommand("INSERT INTO table1 (id, date_tz, description) VALUES (:id, :date_tz, :description)", conn)でINSERTコマンドを生成。
生成したINSERTコマンドのパラメータに
- idはINTEGER型(NpgsqlTypes.NpgsqlDbType.Integer)で、int型の値をセット
- date_tzはTIMESTAMP WITH TIME ZONE型(NpgsqlTypes.NpgsqlDbType.TimestampTZ)で、DateTimeOffset型の値をセット
- descriptionはTEXT型(NpgsqlTypes.NpgsqlDbType.Text)で、string型の値をセット
を設定。
結果は次のようになった。
id[System.Int32]=1
date_tz[System.DateTime]=2016/01/01 0:00:00
description[System.String]='2016-01-01 +09:00'
date_tzがDateTimeOffset型ではなくDateTime型で返ってきているが、その値は2016/01/01 0:00:00で、クライアントのタイムゾーンの値になっているので問題なし。
また、サーバー側の内容を確認してみると
SELECT id, date_tz, description FROM table1 ORDER BY id;
id | date_tz | description
----+------------------------+---------------------
1 | 2015-12-31 23:00:00+08 | '2016-01-01 +09:00'
(1 行)
となっており、date_tzの値はちゃんと台北のタイムゾーンになっている。
結論
PostgreSQLのTIMESTAMP WITH TIME ZONE型のデータを扱うときは、NpgsqlTypes.NpgsqlDbType.TimestampTZとDateTimeOffset型を使ってやる。
最初DateTime型でやろうとして、なかなかうまくいかずAT TIME ZONE INTERVALを組み合わせたりしてちょっと無理矢理やってみたが、DateTimeOffset型の存在を知って「なんだ.NETにもタイムゾーンに対応した日付型があるじゃん」ということでスッキリした。
ただ、DateTimeOffset型はサマータイムには対応していないみたいなので、サマータイムを実施している国では要注意。
あれれ
色々やってみてたら20行目の
DateTimeOffset date_tz = DateTimeOffset.Parse("2016-01-01 +09:00");
を
DateTime date_tz = DateTime.Parse("2016-01-01");
にしてもうまく動いた。
DateTimeOffset型を使う必要はないのかな？^^;
Npgsqlがクライアントのタイムゾーンとサーバーのタイムゾーンをうまいこと処理してくれているのかも(以前散々悩んだのは何だったんだろう...)
Npgsqlのマニュアルを見るとNpgsqlDbType.TimestampTZは.NETではDateTime型となっているし(SELECT文の結果もDateTime型で返ってくるし)DateTime型を使うのが正しいのか。
原因判明
当時うまく行かなかった原因は、Npgsqlのバージョンが古かったためのようだ。
今回試してみたのは、Npgsqlのバージョン2.2.3を使っている。
以前TimestampTZでうまくいかないときには、Npgsqlのバージョンが2.1.3を使っていた。
今回のソースをNpgsql 2.1.3でトライしたところ、結果は
id[System.Int32]=1
date_tz[System.DateTime]=2016/01/01 1:00:00
description[System.String]='2016-01-01 +09:00'
となり、サーバーのデータは
SELECT id, date_tz, description FROM table1 ORDER BY id;
id | date_tz | description
----+------------------------+---------------------
1 | 2016-01-01 00:00:00+08 | '2016-01-01 +09:00'
(1 行)
となった。
ソースはそのままでNpgsqlをバージョン2.2.3に差し替えると
id[System.Int32]=1
date_tz[System.DateTime]=2016/01/01 0:00:00
description[System.String]='2016-01-01 +09:00'
となり、サーバーのデータは
SELECT id, date_tz, description FROM table1 ORDER BY id;
id | date_tz | description
----+------------------------+---------------------
1 | 2015-12-31 23:00:00+08 | '2016-01-01 +09:00'
(1 行)
となって、狙い通りの結果となった。