LoginSignup
1
1

More than 5 years have passed since last update.

@kkino90h

データベースの日付型列とDateTimeのKindについて

Last updated at Posted at 2018-09-07

C#のDateTimeのKindの違いによって、PostgreSQLの日付型列にどう反映されるかを
Visual Studio 2017
Npgsql 4.0.2
PostgreSQL 9.2.4
で検証してみた。

PostgreSQLにテーブルを定義。

テストテーブル
CREATE TABLE table1
(
    id          INTEGER
  , datetime_tz TIMESTAMP WITH TIME ZONE
  , datetime    TIMESTAMP WITHOUT TIME ZONE
);
検証ソース
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Npgsql;

namespace DateTimeOffsetTestNpgsql
{
    class Program
    {
        static void Main(string[] args)
        {
            DateTime dateTimeLocal       = new DateTime(2018, 1, 1, 0, 0, 0, DateTimeKind.Local      );
            DateTime dateTimeUtc         = new DateTime(2018, 1, 1, 0, 0, 0, DateTimeKind.Utc        );
            DateTime dateTimeUnspecified = new DateTime(2018, 1, 1, 0, 0, 0, DateTimeKind.Unspecified);

            NpgsqlConnection conn = new NpgsqlConnection("Server=xxx.xxx.xxx.xxx;User Id=xxxx;Password=xxxx;Database=xxxx;Pooling=false;");
            conn.Open();

            using (NpgsqlCommand cmd = new NpgsqlCommand("DELETE FROM table1", conn))
            {
                cmd.ExecuteNonQuery();
            }

            using (NpgsqlCommand cmd = new NpgsqlCommand("INSERT INTO table1 (id, datetime_tz, datetime) VALUES (:id, :datetime_tz, :datetime)", conn))
            {
                cmd.Parameters.Add(new NpgsqlParameter("id"         , NpgsqlTypes.NpgsqlDbType.Integer    , 0, "id"         ));
                cmd.Parameters.Add(new NpgsqlParameter("datetime_tz", NpgsqlTypes.NpgsqlDbType.TimestampTz, 0, "datetime_tz"));
                cmd.Parameters.Add(new NpgsqlParameter("datetime"   , NpgsqlTypes.NpgsqlDbType.Timestamp  , 0, "datetime"   ));
                cmd.Parameters["id"         ].Value = 1;
                cmd.Parameters["datetime_tz"].Value = dateTimeLocal;
                cmd.Parameters["datetime"   ].Value = dateTimeLocal;
                cmd.ExecuteNonQuery();
            }
            using (NpgsqlCommand cmd = new NpgsqlCommand("INSERT INTO table1 (id, datetime_tz, datetime) VALUES (:id, :datetime_tz, :datetime)", conn))
            {
                cmd.Parameters.Add(new NpgsqlParameter("id"         , NpgsqlTypes.NpgsqlDbType.Integer    , 0, "id"         ));
                cmd.Parameters.Add(new NpgsqlParameter("datetime_tz", NpgsqlTypes.NpgsqlDbType.TimestampTz, 0, "datetime_tz"));
                cmd.Parameters.Add(new NpgsqlParameter("datetime"   , NpgsqlTypes.NpgsqlDbType.Timestamp  , 0, "datetime"   ));
                cmd.Parameters["id"         ].Value = 2;
                cmd.Parameters["datetime_tz"].Value = dateTimeUtc;
                cmd.Parameters["datetime"   ].Value = dateTimeUtc;
                cmd.ExecuteNonQuery();
            }
            using (NpgsqlCommand cmd = new NpgsqlCommand("INSERT INTO table1 (id, datetime_tz, datetime) VALUES (:id, :datetime_tz, :datetime)", conn))
            {
                cmd.Parameters.Add(new NpgsqlParameter("id"         , NpgsqlTypes.NpgsqlDbType.Integer    , 0, "id"         ));
                cmd.Parameters.Add(new NpgsqlParameter("datetime_tz", NpgsqlTypes.NpgsqlDbType.TimestampTz, 0, "datetime_tz"));
                cmd.Parameters.Add(new NpgsqlParameter("datetime"   , NpgsqlTypes.NpgsqlDbType.Timestamp  , 0, "datetime"   ));
                cmd.Parameters["id"         ].Value = 3;
                cmd.Parameters["datetime_tz"].Value = dateTimeUnspecified;
                cmd.Parameters["datetime"   ].Value = dateTimeUnspecified;
                cmd.ExecuteNonQuery();
            }

            using (NpgsqlCommand cmd = new NpgsqlCommand("SELECT id, datetime_tz, datetime FROM table1 ORDER BY id", conn))
            {
                using (NpgsqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            var id          = reader["id"         ];
                            var datetime_tz = reader["datetime_tz"];
                            var datetime    = reader["datetime"   ];
                            Console.WriteLine("id="          + id.ToString());
                            Console.WriteLine("datetime_tz=" + datetime_tz.ToString() + " Kind=" + ((DateTime)datetime_tz).Kind.ToString());
                            Console.WriteLine("datetime   =" + datetime   .ToString() + " Kind=" + ((DateTime)datetime   ).Kind.ToString());
                            Console.WriteLine();
                        }
                    }
                    reader.Close();
                }
            }

            conn.Close();
            Console.ReadKey();
        }
    }
}

結果は

id=1
datetime_tz=2018/01/01 0:00:00 Kind=Local
datetime   =2018/01/01 0:00:00 Kind=Unspecified

id=2
datetime_tz=2018/01/01 9:00:00 Kind=Local
datetime   =2018/01/01 0:00:00 Kind=Unspecified

id=3
datetime_tz=2018/01/01 9:00:00 Kind=Local
datetime   =2018/01/01 0:00:00 Kind=Unspecified

となり
PostgreSQLのtable1の中身は

id datetime_tz datetime
1 2018-01-01 00:00:00+09 2018-01-01 00:00:00
2 2018-01-01 09:00:00+09 2018-01-01 00:00:00
3 2018-01-01 09:00:00+09 2018-01-01 00:00:00

となった。

整理すると

元データ datetime_tz datetime
Local
2018-01-01 00:00:00		 → 2018-01-01 00:00:00+09 2018-01-01 00:00:00
Utc
2018-01-01 00:00:00		 → 2018-01-01 09:00:00+09 2018-01-01 00:00:00
Unspecified
2018-01-01 00:00:00		 → 2018-01-01 09:00:00+09 2018-01-01 00:00:00

TIMESTAMP WITH TIME ZONE型のdateteim_tzには

  • Kind=Localは、日本時間で書き込まれる。
  • Kind=Utcは、UTCから日本時間に変換されて書き込まれる。
  • Kind=Unspecifiedは、UTCから日本時間に変換されて書き込まれる。

となって、UtcとUnspecifiedは同じ挙動になっている。

NpgsqlDataReaderでデータを読み込むと
TIMESTAMP WITH TIME ZONE型のdatetime_tzは

id datetime_tz 読み取り結果
1 2018-01-01 00:00:00+09 → Local
2018-01-01 00:00:00
2 2018-01-01 09:00:00+09 → Local
2018-01-01 09:00:00
3 2018-01-01 09:00:00+09 → Local
2018-01-01 09:00:00

となり、TIMESTAMP WITHOUT TIME ZONE型のdatetimeは

id datetime 読み取り結果
1 2018-01-01 00:00:00 → Unspecified
2018-01-01 00:00:00
2 2018-01-01 00:00:00 → Unspecified
2018-01-01 00:00:00
3 2018-01-01 00:00:00 → Unspecified
2018-01-01 00:00:00

となった。

ちょっと気になったので Npgsql 2.x系で検証

これまでDateTimeのKindは特に気にすること無くやってきたので、Kind=Unspecifiedでコード書いていた。
ここで気になるのが、id=3のケースで

  • Kind=Unspecifiedは、UTCから日本時間に変換されて書き込まれる。

UnspecifiedのときDateTimeの値をUTCとして、UTCから日本時間に変換されているところ。
これがこれまで、UTCから日本時間に変換ではなく、そのまま日本時間としてデータベースには 2018-01-01 00:00:00+09 が書き込まれていた。
何が違うのかいろいろ調べてみたら、これまでの開発では、Npgsqlのバージョンが2.xを使用していた。
今回検証のために最新の4.0.2を使ったが、この違いによる影響のようなので検証してみた。

上記ソースをNpgsqlを2.2.7に変更して実行してみた。
※NpgsqlTypes.NpgsqlDbType.TimestampTzはNpgsqlTypes.NpgsqlDbType.TimestampTZに変更している。
その結果は、

id=1
datetime_tz=2018/01/01 0:00:00 Kind=Local
datetime   =2018/01/01 0:00:00 Kind=Unspecified

id=2
datetime_tz=2018/01/01 9:00:00 Kind=Local
datetime   =2018/01/01 0:00:00 Kind=Unspecified

id=3
datetime_tz=2018/01/01 0:00:00 Kind=Local
datetime   =2018/01/01 0:00:00 Kind=Unspecified

となり
PostgreSQLのtable1の中身は

id datetime_tz datetime
1 2018-01-01 00:00:00+09 2018-01-01 00:00:00
2 2018-01-01 09:00:00+09 2018-01-01 00:00:00
3 2018-01-01 00:00:00+09 2018-01-01 00:00:00

となった。

整理すると

元データ datetime_tz datetime
Local
2018-01-01 00:00:00		 → 2018-01-01 00:00:00+09 2018-01-01 00:00:00
Utc
2018-01-01 00:00:00		 → 2018-01-01 09:00:00+09 2018-01-01 00:00:00
Unspecified
2018-01-01 00:00:00		 → 2018-01-01 00:00:00+09 2018-01-01 00:00:00

TIMESTAMP WITH TIME ZONE型のdateteim_tzには

  • Kind=Localは、日本時間で書き込まれる。
  • Kind=Utcは、UTCから日本時間に変換されて書き込まれる。
  • Kind=Unspecifiedは、日本時間で書き込まれる。

となって、LocalとUnspecifiedは同じ挙動になっている。

NpgsqlDataReaderでデータを読み込むと
TIMESTAMP WITH TIME ZONE型のdatetime_tzは

id datetime_tz 読み取り結果
1 2018-01-01 00:00:00+09 → Local
2018-01-01 00:00:00
2 2018-01-01 09:00:00+09 → Local
2018-01-01 09:00:00
3 2018-01-01 00:00:00+09 → Local
2018-01-01 00:00:00

となった。
やはりid=3のKind=Unspecifiedのケースで挙動が異なる。

どうやら、Npgsqlの2.x系と3.x系以降ではDateTimeのKind=Unspecifiedの際に

Npgsql 挙動
2.x Localと同じ
3.x以降 Utcと同じ

となっているようだ。

ちなみにOracleだとどうなるの？

ということで、手元にあるOracle12cで検証してみる。
NpgsqlをOracle.ManagedDataAccess(バージョン18.3.0)に変更して実行してみると、

id=1
datetime_tz=2018/01/01 0:00:00 Kind=Unspecified
datetime   =2018/01/01 0:00:00 Kind=Unspecified

id=2
datetime_tz=2018/01/01 0:00:00 Kind=Unspecified
datetime   =2018/01/01 0:00:00 Kind=Unspecified

id=3
datetime_tz=2018/01/01 0:00:00 Kind=Unspecified
datetime   =2018/01/01 0:00:00 Kind=Unspecified

となり
Oracleのtable1の中身は

id datetime_tz datetime
1 18-01-01 00:00:00.000000 ASIA/TOKYO 18-01-01 00:00:00.000000
2 18-01-01 00:00:00.000000 ASIA/TOKYO 18-01-01 00:00:00.000000
3 18-01-01 00:00:00.000000 ASIA/TOKYO 18-01-01 00:00:00.000000

となった。
Oracle.ManagedDataAccessの場合、DBに書き込むときにKindを見てない？ような気がする。
また取り込むときも、KindがUnspecifiedになっているので何も設定していないようだ。

MySQLやSQLite、SQL Serverなどは確認していないけど、このあたりは事前によく確認してからやったほうがよさそう。

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1