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

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

PostgreSQLにテーブルを定義。

```sql:テストテーブル
CREATE TABLE table1
(
    id          INTEGER
  , datetime_tz TIMESTAMP WITH TIME ZONE
  , datetime    TIMESTAMP WITHOUT TIME ZONE
);
```

```c#:検証ソース
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<br />2018-01-01 00:00:00       | → | 2018-01-01 00:00:00+09 | 2018-01-01 00:00:00 |
| Utc<br />2018-01-01 00:00:00         | → | 2018-01-01 09:00:00+09 | 2018-01-01 00:00:00 |
| Unspecified<br />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<br />2018-01-01 00:00:00 |
|  2 | 2018-01-01 09:00:00+09 | → | Local<br />2018-01-01 09:00:00 |
|  3 | 2018-01-01 09:00:00+09 | → | Local<br />2018-01-01 09:00:00 |
となり、`TIMESTAMP WITHOUT TIME ZONE`型のdatetimeは

| id | datetime            |   | 読み取り結果                          |
|---:|:--------------------|:-:|:-------------------------------------|
|  1 | 2018-01-01 00:00:00 | → | Unspecified<br />2018-01-01 00:00:00 |
|  2 | 2018-01-01 00:00:00 | → | Unspecified<br />2018-01-01 00:00:00 |
|  3 | 2018-01-01 00:00:00 | → | Unspecified<br />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<br />2018-01-01 00:00:00       | → | 2018-01-01 00:00:00+09 | 2018-01-01 00:00:00 |
| Utc<br />2018-01-01 00:00:00         | → | 2018-01-01 09:00:00+09 | 2018-01-01 00:00:00 |
| Unspecified<br />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<br />2018-01-01 00:00:00 |
|  2 | 2018-01-01 09:00:00+09 | → | Local<br />2018-01-01 09:00:00 |
|  3 | **2018-01-01 00:00:00+09** | → | Local<br />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などは確認していないけど、このあたりは事前によく確認してからやったほうがよさそう。