PostgreSQLのマニュアルにも記述があり、timestamp with time zone型の列にアクセスするとき、at time zone構文を使うことでタイムスタンプを異なる時間帯に変換できるようです。 例えば、こんな感じ。
そこで、昨日のコードを修正して、次のようにしてみました。
ポイントは、DataAdapterのSelectCommand,InsertCommand,UpdateCommandのそれぞれのSQLで、datetime2の列にアクセスするところで、at time zone構文を使っているところです。
SelectCommandでは、datetime2の値を取得するところで、
datetime2 at time zone interval '+09:00' as datetime2 この'+09:00'は、
DateTime tmpTime = new DateTime(2000, 1, 1);
TimeSpan diffUTC = tmpTime - tmpTime.ToUniversalTime();
string strDiffUTC = (diffUTC.TotalHours >= 0 ? "+" : "") + diffUTC.Hours.ToString("00") + ":" + diffUTC.Minutes.ToString("00");
InsertCommandでは
:datetime2 at time zone interval '+09:00'
datetime2=:datetime2 at time zone interval '+09:00'
まず、クライアントPCのタイムゾーンを東京、サーバーのタイムゾーンも東京の状態で実行してみると
データの追加が終わったところ(にブレークポイントをいれて確認)で、コンソールに
id=1, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 9:00:00
db1=> select * from table1;
id | datetime1 | datetime2
----+---------------------+------------------------
1 | 2012-10-01 09:00:00 | 2012-10-01 09:00:00+09
(1 row)
そのまま処理を続けて、次の更新処理が終わったところで、コンソールに
id=1, datetime1=2012/12/01 9:00:00, datetime2=2012/12/01 9:00:00
db1=> select * from table1;
id | datetime1 | datetime2
----+---------------------+------------------------
1 | 2012-12-01 09:00:00 | 2012-12-01 09:00:00+09
(1 row)
次に、サーバー側のタイムゾーンを台北に変更して、同じ事をやってみます。
データの追加処理が終わったところでは、コンソールに
id=1, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 11:00:00
db1=> select * from table1;
id | datetime1 | datetime2
----+---------------------+------------------------
1 | 2012-10-01 09:00:00 | 2012-10-01 10:00:00+08
(1 row)
id=1, datetime1=2012/12/01 9:00:00, datetime2=2012/12/01 11:00:00
db1=> select * from table1;
id | datetime1 | datetime2
----+---------------------+------------------------
1 | 2012-12-01 09:00:00 | 2012-12-01 10:00:00+08
あれれれ、予定では追加処理のあと、datetime2の値は、コンソールでは2012/10/01 09:00:00と表示され、データベース側のtable1のdatetime2の値は、2012-10-01 08:00:00+08になっているはずなのに、2時間ズレているようです。
原因を調べるために、追加処理で実際に実行されたSQLを調べてみると、
insert into table1 (
id
, datetime1
, datetime2
) values (
((1)::int4)
, ((E'2012-10-01 09:00:00.000000')::timestamp)
, ((E'2012-10-01 09:00:00.000000')::timestamptz) at time zone interval '+09:00'
)
datetime2の値となる
((E'2012-10-01 09:00:00.000000')::timestamptz) at time zone interval '+09:00' これで2時間のズレが発生しているようです。
ようするに、
db1=> select timestamp with time zone '2012-10-01 9:00:00' at time zone interval '+09:00';
timezone
---------------------
2012-10-01 10:00:00
(1 row)
db1=> select timestamp without time zone '2012-10-01 9:00:00' at time zone interval '+09:00';
timezone
------------------------
2012-10-01 08:00:00+08
(1 row)
そうなると、今回のコードで問題になるのは、InsertCommandとUpdateCommandのdatetime2に対するパラメータの型ということになります。
da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime2", NpgsqlTypes.NpgsqlDbType.TimestampTZ, 0, "datetime2", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current, DBNull.Value)); da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime2", NpgsqlTypes.NpgsqlDbType.Timestamp, 0, "datetime2", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current, DBNull.Value)); コードを修正して、もう一度、クライアントは東京、サーバーは台北という状態で実行してみます。
追加処理が終わったところで、コンソールには
id=1, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 9:00:00
db1=> select * from table1;
id | datetime1 | datetime2
----+---------------------+------------------------
1 | 2012-10-01 09:00:00 | 2012-10-01 08:00:00+08
(1 row)
id=1, datetime1=2012/12/01 9:00:00, datetime2=2012/12/01 9:00:00
db1=> select * from table1;
id | datetime1 | datetime2
----+---------------------+------------------------
1 | 2012-12-01 09:00:00 | 2012-12-01 08:00:00+08
(1 row)
これで予定通りの処理となりました。
念のため、クライアントのタイムゾーンを台北、サーバーのタイムゾーンを東京にして同じ処理を実行してみます。
追加処理が終わると、コンソールに
id=1, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 9:00:00
db1=> select * from table1;
id | datetime1 | datetime2
----+---------------------+------------------------
1 | 2012-10-01 09:00:00 | 2012-10-01 10:00:00+09
(1 row)
id=1, datetime1=2012/12/01 9:00:00, datetime2=2012/12/01 9:00:00
db1=> select * from table1;
id | datetime1 | datetime2
----+---------------------+------------------------
1 | 2012-12-01 09:00:00 | 2012-12-01 10:00:00+09
(1 row)
これで、クライアント側のプログラムでの日時は、クライアントのタイムゾーンに従った日時が入り、データベース上の日時は、UTC(with time zone)で入るようにできそうです。
一応、修正したソースを載せておきます。
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace pgTimestamp
{
class Program
{
static void Main(string[] args)
{
// データベース接続
Npgsql.NpgsqlConnection conn = new Npgsql.NpgsqlConnection("Server=xxxx;"
+ "Port=5432;"
+ "User Id=yyyy;"
+ "Password=zzzz;"
+ "Database=db1;"
+ "Pooling=false;"
+ "Encoding=UNICODE;");
// データベース接続
try
{
conn.Open();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadKey();
return;
}
// クライアントの時間とUTCとの差を取得
DateTime tmpTime = new DateTime(2000, 1, 1); // 適当な日時をセットして
TimeSpan diffUTC = tmpTime - tmpTime.ToUniversalTime(); // UTCとの差を取得
string strDiffUTC = (diffUTC.TotalHours >= 0 ? "+" : "") + diffUTC.Hours.ToString("00") + ":" + diffUTC.Minutes.ToString("00");
// テーブルの生成
DataTable table1 = new DataTable("table1");
table1.Columns.Add(new DataColumn("id" , typeof(int) ));
table1.Columns.Add(new DataColumn("datetime1", typeof(DateTime)));
table1.Columns.Add(new DataColumn("datetime2", typeof(DateTime)));
table1.PrimaryKey = new DataColumn[] { table1.Columns["id"] };
// データアダプタの生成
Npgsql.NpgsqlDataAdapter da = new Npgsql.NpgsqlDataAdapter();
da.SelectCommand = new Npgsql.NpgsqlCommand
(
"select"
+ " id"
+ ", datetime1"
+ ", datetime2 at time zone interval '" + strDiffUTC + "' as datetime2"
+ " from"
+ " table1"
, conn
);
da.InsertCommand = new Npgsql.NpgsqlCommand
(
"insert into table1 ("
+ "id"
+ ", datetime1"
+ ", datetime2"
+ ") values ("
+ ":id"
+ ", :datetime1"
+ ", :datetime2 at time zone interval '" + strDiffUTC + "'"
+ ")"
, conn
);
da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("id" , NpgsqlTypes.NpgsqlDbType.Integer , 0, "id" , ParameterDirection.Input, false, 0, 0, DataRowVersion.Current, DBNull.Value));
da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime1", NpgsqlTypes.NpgsqlDbType.Timestamp, 0, "datetime1", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current, DBNull.Value));
da.InsertCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime2", NpgsqlTypes.NpgsqlDbType.Timestamp, 0, "datetime2", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current, DBNull.Value));
da.UpdateCommand = new Npgsql.NpgsqlCommand
(
"update table1 set"
+ " id=:id"
+ ", datetime1=:datetime1"
+ ", datetime2=:datetime2 at time zone interval '" + strDiffUTC + "'"
+ " where"
+ " id=:org_id"
, conn
);
da.UpdateCommand.Parameters.Add(new Npgsql.NpgsqlParameter("id" , NpgsqlTypes.NpgsqlDbType.Integer , 0, "id" , ParameterDirection.Input, false, 0, 0, DataRowVersion.Current , DBNull.Value));
da.UpdateCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime1", NpgsqlTypes.NpgsqlDbType.Timestamp, 0, "datetime1", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current , DBNull.Value));
da.UpdateCommand.Parameters.Add(new Npgsql.NpgsqlParameter("datetime2", NpgsqlTypes.NpgsqlDbType.Timestamp, 0, "datetime2", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current , DBNull.Value));
da.UpdateCommand.Parameters.Add(new Npgsql.NpgsqlParameter("org_id" , NpgsqlTypes.NpgsqlDbType.Integer , 0, "id" , ParameterDirection.Input, false, 0, 0, DataRowVersion.Original, DBNull.Value));
da.DeleteCommand = new Npgsql.NpgsqlCommand
(
"delete from table1"
+ " where"
+ " id=:org_id"
, conn
);
da.DeleteCommand.Parameters.Add(new Npgsql.NpgsqlParameter("org_id" , NpgsqlTypes.NpgsqlDbType.Integer , 0, "id" , ParameterDirection.Input, false, 0, 0, DataRowVersion.Original, DBNull.Value));
// データの取得
da.Fill(table1);
// データの一旦削除
try
{
foreach (DataRow row in table1.Rows)
{
row.Delete();
}
Update(conn, da, table1);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadKey();
return;
}
// データの追加
DateTime value = DateTime.Parse("2012-10-01 09:00:00");
DataRow newRow = table1.NewRow();
newRow["id" ] = 1;
newRow["datetime1"] = value;
newRow["datetime2"] = value;
table1.Rows.Add(newRow);
// 保存
try
{
Update(conn, da, table1);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadKey();
return;
}
// テーブルを一旦クリアして取得し直す
table1.Rows.Clear();
da.Fill(table1);
// テーブル内容の表示
Show(table1);
// データの更新
value = DateTime.Parse("2012-12-01 09:00:00");
table1.Rows[0]["datetime1"] = value;
table1.Rows[0]["datetime2"] = value;
// 保存
try
{
Update(conn, da, table1);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadKey();
return;
}
// テーブルを一旦クリアして取得し直す
table1.Rows.Clear();
da.Fill(table1);
// テーブル内容の表示
Show(table1);
// データベース切断
try
{
conn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadKey();
}
/// <summary>
/// データの保存
/// </summary>
/// <param name="conn">データベース接続子</param>
/// <param name="da">データアダプタ</param>
/// <param name="table1">データテーブル</param>
static private void Update(Npgsql.NpgsqlConnection conn, Npgsql.NpgsqlDataAdapter da, DataTable table1)
{
// トランザクション開始
Npgsql.NpgsqlTransaction tran = null;
try
{
tran = conn.BeginTransaction();
da.InsertCommand.Transaction = tran;
da.UpdateCommand.Transaction = tran;
da.DeleteCommand.Transaction = tran;
}
catch
{
throw;
}
// 保存
try
{
da.Update(table1);
}
catch
{
tran.Rollback();
throw;
}
// コミット
try
{
tran.Commit();
}
catch
{
throw;
}
}
/// <summary>
/// テーブルの内容を表示
/// </summary>
/// <param name="table1">データテーブル</param>
static private void Show(DataTable table1)
{
foreach (DataRow row in table1.Rows)
{
Console.WriteLine("id=" + row["id"].ToString()
+ ", datetime1=" + ((DateTime)row["datetime1"]).ToString()
+ ", datetime2=" + ((DateTime)row["datetime2"]).ToString());
}
}
}
}
