PostgreSQLの動いている環境はこんな感じ。
$ psql --version
psql (PostgreSQL) 8.3.10
contains support for command-line editing
$ cat /etc/lsb-release
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=8.04
DISTRIB_CODENAME=hardy
DISTRIB_DESCRIPTION="Ubuntu 8.04.4 LTS"
ここにdb1というデータベースを作成して、確認用のテーブルtable1を作成します。
$ createdb db1
$ psql db1
db1=> create table table1
db1-> (
db1(> id integer not null primary key
db1(> , datetime1 timestamp without time zone
db1(> , datetime2 timestamp with time zone
db1(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "table1_pkey" for table "table1"
CREATE TABLE
db1=> insert into table1(id, datetime1, datetime2) values (1, current_timestamp, current_timestamp);
INSERT 0 1
db1=> select * from table1;
id | datetime1 | datetime2
----+----------------------------+-------------------------------
1 | 2012-09-27 11:23:28.487888 | 2012-09-27 11:23:28.487888+09
(1 row)
C#で次のコードを実行して、table1の内容を取得してみます。
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;
}
// テーブルの生成
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 from table1", conn);
da.Fill(table1);
// 取得したデータの表示
foreach (DataRow row in table1.Rows)
{
Console.WriteLine("id=" + row["id"].ToString()
+ ", datetime1=" + ((DateTime)row["datetime1"]).ToString()
+ ", datetime2=" + ((DateTime)row["datetime2"]).ToString());
Console.WriteLine("dateTime2(UTC)=" + ((DateTime)row["datetime2"]).ToUniversalTime().ToString());
}
// データベース切断
try
{
conn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadKey();
}
}
}
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 11:23:28
dateTime2(UTC)=2012/09/27 2:23:28
ここで、サーバー側のタイムゾーンを台北に変更してみます。
$ sudo dpkg-reconfigure tzdata
Current default timezone: 'Asia/Taipei'
Local time is now: Thu Sep 27 10:34:32 CST 2012.
Universal Time is now: Thu Sep 27 02:34:32 UTC 2012.
sudo /etc/init.d/postgresql-8.3 restart
$ psql db1
db1=> select * from table1;
id | datetime1 | datetime2
----+----------------------------+-------------------------------
1 | 2012-09-27 11:23:28.487888 | 2012-09-27 10:23:28.487888+08
(1 row)
ここで、先ほどのC#のコードを実行してみると
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 11:23:28
dateTime2(UTC)=2012/09/27 2:23:28
datetime2の値は、タイムゾーンが東京での時間になっています。
ここで、C#のコードを実行しているPCのタイムゾーンの設定を台北に変更してみます。
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 10:23:28
dateTime2(UTC)=2012/09/27 2:23:28
ただ、このときToUniversalTime()で取得した時間は、どのパターンでも同じ時間となっています。
今度は、サーバー側のタイムゾーンを東京に戻してPostgreSQLを再起動します。
$ sudo dpkg-reconfigure tzdata
Current default timezone: 'Asia/Tokyo'
Local time is now: Thu Sep 27 11:52:27 JST 2012.
Universal Time is now: Thu Sep 27 02:52:27 UTC 2012.
$ sudo /etc/init.d/postgresql-8.3 restart
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 10:23:28
dateTime2(UTC)=2012/09/27 2:23:28
ここでPCのタイムゾーンも東京に戻します。
今度は、C#のコードでtable1に行を追加してみます。
次のようなコードを用意します。
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;
}
// テーブルの生成
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 from table1", conn);
da.Fill(table1);
// データの追加
da.InsertCommand = new Npgsql.NpgsqlCommand
(
"insert into table1 ("
+ "id"
+ ", datetime1"
+ ", datetime2"
+ ") values ("
+ " :id"
+ ", :datetime1"
+ ", :datetime2"
+ ")"
, 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.TimestampTZ, 0, "datetime2", ParameterDirection.Input, true , 0, 0, DataRowVersion.Current, DBNull.Value));
DateTime value = DateTime.Parse("2012-10-01 09:00:00");
DataRow newRow = table1.NewRow();
newRow["id" ] = 2;
newRow["datetime1"] = value;
newRow["datetime2"] = value;
table1.Rows.Add(newRow);
// トランザクション開始
Npgsql.NpgsqlTransaction tran = null;
try
{
tran = conn.BeginTransaction();
da.InsertCommand.Transaction = tran;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadKey();
return;
}
// 保存
try
{
da.Update(table1);
}
catch (Exception ex)
{
tran.Rollback();
Console.WriteLine(ex.Message);
Console.ReadKey();
return;
}
// コミット
try
{
tran.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.ReadKey();
return;
}
// データベースからtable1の内容を取得しなおす
table1.Rows.Clear();
da.Fill(table1);
// 取得したデータの表示
foreach (DataRow row in table1.Rows)
{
Console.WriteLine("id=" + row["id"].ToString()
+ ", datetime1=" + ((DateTime)row["datetime1"]).ToString()
+ ", datetime2=" + ((DateTime)row["datetime2"]).ToString());
Console.WriteLine("dateTime2(UTC)=" + ((DateTime)row["datetime2"]).ToUniversalTime().ToString());
}
// データベース切断
try
{
conn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadKey();
}
}
}
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 11:23:28
dateTime2(UTC)=2012/09/27 2:23:28
id=2, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 9:00:00
dateTime2(UTC)=2012/10/01 0:00:00
db1=> select * from table1;
id | datetime1 | datetime2
----+----------------------------+-------------------------------
1 | 2012-09-27 11:23:28.487888 | 2012-09-27 11:23:28.487888+09
2 | 2012-10-01 09:00:00 | 2012-10-01 09:00:00+09
(2 rows)
ここで、一旦id=2のレコードを削除します。
db1=> begin;
BEGIN
db1=> delete from table1 where id=2;
DELETE 1
db1=> commit;
COMMIT
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 11:23:28
dateTime2(UTC)=2012/09/27 2:23:28
id=2, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 10:00:00
dateTime2(UTC)=2012/10/01 1:00:00
db1=> select * from table1;
id | datetime1 | datetime2
----+----------------------------+-------------------------------
1 | 2012-09-27 11:23:28.487888 | 2012-09-27 10:23:28.487888+08
2 | 2012-10-01 09:00:00 | 2012-10-01 09:00:00+08
(2 rows)
さらに、今度はクライアント側のタイムゾーンを台北にしてやってみると、
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 10:23:28
dateTime2(UTC)=2012/09/27 2:23:28
id=2, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 9:00:00
dateTime2(UTC)=2012/10/01 1:00:00
db1=> select * from table1;
id | datetime1 | datetime2
----+----------------------------+-------------------------------
1 | 2012-09-27 11:23:28.487888 | 2012-09-27 10:23:28.487888+08
2 | 2012-10-01 09:00:00 | 2012-10-01 09:00:00+08
(2 rows)
サーバー側から見ると、クライアントのタイムゾーンがなんであっても、9:00としてinsertされたら、それはサーバー側のタイムゾーンでの時刻としてinsertされるようです。
念のため、今度はサーバー側のタイムゾーンを東京に戻して(クライアントのタイムゾーンは台北のまま)やってみると、
id=1, datetime1=2012/09/27 11:23:28, datetime2=2012/09/27 10:23:28
dateTime2(UTC)=2012/09/27 2:23:28
id=2, datetime1=2012/10/01 9:00:00, datetime2=2012/10/01 8:00:00
dateTime2(UTC)=2012/10/01 0:00:00
db1=> select * from table1;
id | datetime1 | datetime2
----+----------------------------+-------------------------------
1 | 2012-09-27 11:23:28.487888 | 2012-09-27 11:23:28.487888+09
2 | 2012-10-01 09:00:00 | 2012-10-01 09:00:00+09
(2 rows)
datetiime2の列の値を表にしてみると
| タイムゾーンと結果 | UTC | |||
| サーバー | クライアント | |||
| 東京 | 9:00 | 東京 | 9:00 | 0:00 |
| 台北 | 9:00 | 東京 | 10:00 | 1:00 |
| 台北 | 9:00 | 台北 | 9:00 | 1:00 |
| 東京 | 9:00 | 台北 | 8:00 | 0:00 |
ここで、少し気になるのが、C#のコードの中のDataAdapterのInsertCommandのNpgsqlParameterの設定で、
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));
試してみると、
| タイムゾーンと結果 | UTC | |||
| サーバー | クライアント | |||
| 東京 | 9:00 | 東京 | 9:00 | 0:00 |
| 台北 | 9:00 | 東京 | 10:00 | 1:00 |
| 台北 | 9:00 | 台北 | 9:00 | 1:00 |
| 東京 | 9:00 | 台北 | 8:00 | 0:00 |
NpgsqlDbType.TimestampTZとNpgsqlDbType.Timestampの使い分けがよくわからない感じですが、PostgreSQL側のテーブルの列がtimestamp with time zoneなら、NpgsqlDbType.TimestampTZを使って、timestamp without time zoneならNpgsqlDbType.Timestampを使っておけばいいのかな。
ただ、私のイメージしていた動きだと、C#のコードでDateTimeの時刻に9:00と入っていて、それをデータベースに書き込み、さらに読みなおしたときは9:00になっていて欲しい感じです。
| タイムゾーンと結果 | UTC | |||
| サーバー | クライアント | |||
| 東京 | 9:00 | 東京 | 9:00 | 0:00 |
| 台北 | 8:00 | 東京 | 9:00 | 0:00 |
| 台北 | 9:00 | 台北 | 9:00 | 1:00 |
| 東京 | 10:00 | 台北 | 9:00 | 1:00 |
追記：別のエントリで、もう少しツッコんで解決方法を考えてみました。
