確認したいこと
PostgreSQLのTIMESTAMP WITH TIME ZONE型を.NETでNpgsqlを使ってアクセスするとき、.NETのDateTimeOffset型を使うのかと思ったら、DateTime型を使うのが正しいようなのでサンプルソースで確認する。
準備
データベース・サーバーのタイムゾーンがAsia/Taipeiになっていることを確認。
確認のためのテーブルを生成
CREATE TABLE table1
(
id INTEGER
, date_tz TIMESTAMP WITH TIME ZONE
, description TEXT
);
DataTableとDataAdapterを使って、データの書き込み/読み込みを行うサンプル
次のソースを実行。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace pgTimestampTZ2
{
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();
// DataTable生成
DataTable table1 = new DataTable("table1");
table1.Columns.Add(new DataColumn("id" , typeof(int) ));
table1.Columns.Add(new DataColumn("date_tz" , typeof(DateTime)));
table1.Columns.Add(new DataColumn("description", typeof(string) ));
// DataAdapter生成
Npgsql.NpgsqlDataAdapter da = new Npgsql.NpgsqlDataAdapter();
// SELECTコマンド
da.SelectCommand = new Npgsql.NpgsqlCommand("SELECT id, date_tz, description FROM table1 ORDER BY id", conn);
// INSERTコマンド
da.InsertCommand = new Npgsql.NpgsqlCommand("INSERT INTO table1 (id, date_tz, description) VALUES (:id, :date_tz, :description)", conn);
da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("id" , NpgsqlTypes.NpgsqlDbType.Integer , 0, "id" ));
da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("date_tz" , NpgsqlTypes.NpgsqlDbType.TimestampTZ, 0, "date_tz" ));
da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("description", NpgsqlTypes.NpgsqlDbType.Text , 0, "description"));
// UpdateCommandとDeleteCommandは省略
// データを入れる
DataRow newRow = table1.NewRow();
newRow["id" ] = 1;
newRow["date_tz" ] = DateTime.Parse("2016-01-01");
newRow["description"] = "'2016-01-01 +09:00'";
table1.Rows.Add(newRow);
// 保存
da.Update(table1);
// データを取得
DataTable table2 = table1.Clone();
da.Fill(table2);
foreach (DataRow row in table2.Rows)
{
var id = row["id" ];
var date_tz = row["date_tz" ];
var description = row["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();
}
}
}
DataTable型のtable1を作成し、各列を
- id : int型
- date_tz : DateTime型
- description : string型
で定義。
1行データを入れて、DataAdapterのUpdateコマンドでデータベースに書き込む。
table1のCloseとしてtable2を生成し、DataAdapterのFillコマンドでデータを読み込む。
table2の中身を確認すると
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 行)
となっており、date_tzの値は台北のタイムゾーンで狙い通りの結果となった。
TIMESTAMP WITH TIME ZONE型の列をDateTimeOffset型でアクセスすると...
table1の列の定義の
table1.Columns.Add(new DataColumn("date_tz" , typeof(DateTime)));
を
table1.Columns.Add(new DataColumn("date_tz" , typeof(DateTimeOffset)));
に変更して、サンプルデータを入れるところも
newRow["date_tz" ] = DateTime.Parse("2016-01-01");
を
newRow["date_tz" ] = DateTimeOffset.Parse("2016-01-01");
に変更して実行してみる。
そうすると、DataAdapterのUpdateメソッドまではエラーは発生せず処理されるが、DataAdapterのFillメソッドで次のような例外が発生する。
指定されたキャストは有効ではありません。列 date_tz に <2016/01/01 0:00:00> を格納できませんでした。 必要な型は DateTimeOffset です。
DataAdapterのUpdateメソッドでデータベースに書き込まれたデータを確認すると
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 行)
となっており、データは正しく入っている。
ただ、DataAdapterのFillメソッドでは、取り出したデータをDateTimeOffset型に変換できずに例外が発生する。
結論
PostgreSQLのTIMESTAMP WITH TIME ZONE型はDateTimeOffset型ではなくDateTime型でアクセスする。
補足
Npgsqlのドキュメントによると、PostgreSQLのTIMESTAMP WITH TIME ZONE型は.NETのDateTime型で、PostgreSQLのTIME WITH TIME ZONE型は.NETのDateTimeOffset型を使うように書いてあった。