2012年9月27日木曜日

PostgreSQLの列の型のtimestampのtime zoneについて

投稿者 kkino90h 時刻: 15:58 ラベル: ,
PostgreSQLの列をtimestamp with time zoneで定義して、Npgsqlでアクセスした時、どんなふうになるのか気になったのでテスト。

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)
列のdatetime1をtimestamp without time zoneで作成し、列のdatetime2をtimestamp with time zoneで作成しています。

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.
PostgreSQLを再起動して、table1の内容を確認します。
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)
datetime1はタイムゾーンに関係なく入れた時のまま(タイムゾーンが東京のcurrent_timestampの値)で、datetime2は入れた時の台北の時間が表示されます。

ここで、先ほどの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
となり、datetime2は台北での時間となります。
ただ、このとき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
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
となり、datetime2の値はサーバー側のタイムゾーンには関係なく、クライアント側のタイムゾーンで取得されています。
ここで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
サーバーでtable1を確認すると、 
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)
となっており、datetime2もタイムゾーンは東京で、C#側のDateTime型で指定された時間になっています。
ここで、一旦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
サーバーでtable1を確認すると、 
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)
となり、C#側でDateTimeの時間を9:00にしてinsertすると、サーバー側には台北での9:00がinsertされ、クライアント側でその時刻を取得すると、10:00(台北時間の9:00を東京時間で表示)となります。

さらに、今度はクライアント側のタイムゾーンを台北にしてやってみると、 
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
サーバーでtable1を確認すると、 
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)
となり、クライアント側の結果は、クライアントとサーバーでタイムゾーンが一致しているので、insertした9:00となり、データベース側はクライアントのタイムゾーンが東京の時と同じ結果となっています。
サーバー側から見ると、クライアントのタイムゾーンがなんであっても、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
サーバーでtable1を確認すると、 
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:000:00
台北9:00東京10:001:00
台北9:00台北9:001:00
東京9:00台北8:000:00
となり、クライアントのタイムゾーンがなんであっても、サーバー側には9:00でinsertされ、それをクライアント側が取得するときは、サーバー側のタイムゾーンでの9: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));
というように、NpgsqlDbTypeをTimestampTZからTimestampに変更した場合、どういう動作になるのか。
試してみると、
タイムゾーンと結果UTC
サーバークライアント
東京9:00東京9:000:00
台北9:00東京10:001:00
台北9:00台北9:001:00
東京9:00台北8:000: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:000:00
台北8:00東京9:000:00
台北9:00台北9:001:00
東京10:00台北9:001:00
テーブルの列の型がTimestamp with time zoneで、且つNpgsqlCommandのパラメータの型をNpgsqlDbType.TimestampTZにしているなら、サーバー側に書き込まれる時刻のUTCはクライアントの書き込もうとしている時刻のUTCと一致するようになるようなイメージ。そうだとクライアント側はサーバーのタイムゾーンを意識しなくてもいい気がするんだけど。。。もしかして、私が気がついていなくて考え方が間違っているのかな(^^;

追記：別のエントリで、もう少しツッコんで解決方法を考えてみました。

0 コメント :

コメントを投稿

登録: コメントの投稿 ( Atom )