PostgreSQLのJDBCドライバーで、timestampとtimestamp with time zoneのjava.sql.Typesでのデータ型が同じになってしまうという話

# What's？

PostgreSQLをJDBCで扱っていて、`timestamp with time zone`型を使った時にちょっと困ったことになりまして。

調べてみました。

結論から言うと、JDBCのメタデータで見た時に`timestamp`と`timestamp with time zone`型を区別することができません。

# PostgreSQLのJDBCドライバーとDate and Time API（JSR-310）

PostgreSQLのJDBCドライバーのオフィシャルサイトはこちら。

https://jdbc.postgresql.org/

ドキュメント内に、Date and Time API（JSR-310）との関係が書かれています。

[Issuing a Query and Processing the Result / Using Java 8 Date and Time classes](https://jdbc.postgresql.org/documentation/query/#using-java-8-date-and-time-classes)

こちらに以下のような表があり、`date`、`time`、`timestamp`、`timestamp with time zone`がどのJavaのクラスにマッピングできるかが書かれています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/309669/e08e1bf0-978c-76bf-2b8b-8daddd5945c9.png)

その後に使用例が続きます。

使用例のような使い方では困らないのですが、JDBCのメタデータで見た時にはちょっと事情が変わるので、実際に使って確認してみます。

# 環境

今回の環境は、こちら。

```shell
$ java --version
openjdk 17.0.4 2022-07-19
OpenJDK Runtime Environment (build 17.0.4+8-Ubuntu-120.04)
OpenJDK 64-Bit Server VM (build 17.0.4+8-Ubuntu-120.04, mixed mode, sharing)

$ mvn --version
Apache Maven 3.8.6 (84538c9988a25aec085021c365c560670ad80f63)
Maven home: /home/charon/.sdkman/candidates/maven/current
Java version: 17.0.4, vendor: Private Build, runtime: /usr/lib/jvm/java-17-openjdk-amd64
Default locale: ja_JP, platform encoding: UTF-8
OS name: "linux", version: "5.4.0-128-generic", arch: "amd64", family: "unix"
```

PostgreSQLは、Dockerで用意します。

```shell
$ docker container run -it --rm -p 5432:5432 \
  -e POSTGRES_DB=example \
  -e POSTGRES_USER=charon \
  -e POSTGRES_PASSWORD=password \
  --name postgres \
  postgres:14.5
```

# テーブルを作成する

今回は、以下のようなテーブルを用意しました。

```sql
create table t(
  id integer,
  date_column date,
  time_column time,
  timestamp_column timestamp,
  timestamp_with_timezone_column timestamp with time zone,
  primary key(id)
);
```

定義確認。

```sql
example=# \d t
                                       Table "public.t"
             Column             |            Type             | Collation | Nullable | Default
--------------------------------+-----------------------------+-----------+----------+---------
 id                             | integer                     |           | not null |
 date_column                    | date                        |           |          |
 time_column                    | time without time zone      |           |          |
 timestamp_column               | timestamp without time zone |           |          |
 timestamp_with_timezone_column | timestamp with time zone    |           |          |
Indexes:
    "t_pkey" PRIMARY KEY, btree (id)

```

# 準備

以下のようなMavenプロジェクトを作成。

```xml:pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>postgres-jdbc-timestampz</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.5.0</version>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>5.9.1</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.assertj</groupId>
            <artifactId>assertj-core</artifactId>
            <version>3.23.1</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.22.2</version>
            </plugin>
        </plugins>
    </build>
</project>
```

確認は、テストコードで行うことにします。このような雛形を用意。

```java:src/test/java/com/example/PostgreSqlDateAndTimeApiTest.java
package com.example;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;

import org.junit.jupiter.api.Test;

import static org.assertj.core.api.Assertions.assertThat;

public class PostgreSqlDateAndTimeApiTest {

    // ここにテストを書く！！
}
```

ここから先は、上記のクラス内のテストメソッドを書いていくことにします。

# 確認してみる

## データの登録とクエリー

最初はデータの登録とクエリーをしてみます。

```java
    @Test
    public void testDateAndTime() throws SQLException {
        try (Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/example", "charon", "password")) {
            try (PreparedStatement ps = connection.prepareStatement("truncate table t")) {
                ps.executeUpdate();
            }

            connection.setAutoCommit(false);

            try (PreparedStatement ps =
                         connection.prepareStatement("insert into t(id, date_column, time_column, timestamp_column, timestamp_with_timezone_column) values(?, ?, ?, ?, ?)")) {
                ps.setInt(1, 1);
                ps.setObject(2, LocalDate.of(2022, 10, 15));
                ps.setObject(3, LocalTime.of(16, 30, 0));
                ps.setObject(4, LocalDateTime.of(2022, 10, 15, 16, 30, 0));
                ps.setObject(5, OffsetDateTime.of(
                                LocalDateTime.of(2022, 10, 15, 16, 30, 0),
                                ZoneOffset.of("+09:00")
                        )
                );

                ps.executeUpdate();
            }

            connection.commit();

            try (PreparedStatement ps =
                         connection.prepareStatement("select id, date_column, time_column, timestamp_column, timestamp_with_timezone_column from t where id = ?")) {
                ps.setInt(1, 1);

                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();

                    assertThat(rs.getInt("id")).isEqualTo(1);
                    assertThat(rs.getObject("date_column")).isInstanceOf(Date.class);
                    assertThat(rs.getObject("date_column", LocalDate.class)).isEqualTo(LocalDate.of(2022, 10, 15));
                    assertThat(rs.getObject("time_column")).isInstanceOf(Time.class);
                    assertThat(rs.getObject("time_column", LocalTime.class)).isEqualTo(LocalTime.of(16, 30, 0));
                    assertThat(rs.getObject("timestamp_column")).isInstanceOf(Timestamp.class);
                    assertThat(rs.getObject("timestamp_column", LocalDateTime.class)).isEqualTo(LocalDateTime.of(2022, 10, 15, 16, 30, 0));
                    assertThat(rs.getObject("timestamp_with_timezone_column")).isInstanceOf(Timestamp.class);
                    assertThat(rs.getObject("timestamp_with_timezone_column", OffsetDateTime.class)).isEqualTo(
                            OffsetDateTime.of(
                                    LocalDateTime.of(2022, 10, 15, 16, 30, 0),
                                    ZoneOffset.of("+09:00")
                            )
                    );
                }
            }
        }
    }
```

`insert`文に対しては、`PreparedStatement#setObject`で問題なく各カラムに値を設定できます。

```java
            try (PreparedStatement ps =
                         connection.prepareStatement("insert into t(id, date_column, time_column, timestamp_column, timestamp_with_timezone_column) values(?, ?, ?, ?, ?)")) {
                ps.setInt(1, 1);
                ps.setObject(2, LocalDate.of(2022, 10, 15));
                ps.setObject(3, LocalTime.of(16, 30, 0));
                ps.setObject(4, LocalDateTime.of(2022, 10, 15, 16, 30, 0));
                ps.setObject(5, OffsetDateTime.of(
                                LocalDateTime.of(2022, 10, 15, 16, 30, 0),
                                ZoneOffset.of("+09:00")
                        )
                );

                ps.executeUpdate();
            }
```

クエリーを実行する際は、`ResultSet#getObject`を呼び出す際の第2引数に`Class`クラスの指定が必要です。

```java
            try (PreparedStatement ps =
                         connection.prepareStatement("select id, date_column, time_column, timestamp_column, timestamp_with_timezone_column from t where id = ?")) {
                ps.setInt(1, 1);

                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();

                    assertThat(rs.getInt("id")).isEqualTo(1);
                    assertThat(rs.getObject("date_column")).isInstanceOf(Date.class);
                    assertThat(rs.getObject("date_column", LocalDate.class)).isEqualTo(LocalDate.of(2022, 10, 15));
                    assertThat(rs.getObject("time_column")).isInstanceOf(Time.class);
                    assertThat(rs.getObject("time_column", LocalTime.class)).isEqualTo(LocalTime.of(16, 30, 0));
                    assertThat(rs.getObject("timestamp_column")).isInstanceOf(Timestamp.class);
                    assertThat(rs.getObject("timestamp_column", LocalDateTime.class)).isEqualTo(LocalDateTime.of(2022, 10, 15, 16, 30, 0));
                    assertThat(rs.getObject("timestamp_with_timezone_column")).isInstanceOf(Timestamp.class);
                    assertThat(rs.getObject("timestamp_with_timezone_column", OffsetDateTime.class)).isEqualTo(
                            OffsetDateTime.of(
                                    LocalDateTime.of(2022, 10, 15, 16, 30, 0),
                                    ZoneOffset.of("+09:00")
                            )
                    );
                }
            }
```

例としても書いていますが、指定しなかった場合は`java.sql`パッケージのクラスのインスタンスが返ってくることになります。

## メタデータを見る

ここが本題です。

以下のように`DatabaseMetaData`を使うことで、テーブルの各カラムのデータ型を見ることができます。

https://docs.oracle.com/javase/jp/17/docs/api/java.sql/java/sql/DatabaseMetaData.html

```java
    @Test
    public void testDateAndTimeTypeMapping() throws SQLException {
        try (Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/example", "charon", "password")) {
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            try (ResultSet rs = databaseMetaData.getColumns(null, null, "t", "%")) {
                rs.next();
                assertThat(rs.getString("column_name")).isEqualTo("id");
                assertThat(rs.getInt("data_type")).isEqualTo(Types.INTEGER);
                assertThat(rs.getString("type_name")).isEqualTo("int4");

                rs.next();
                assertThat(rs.getString("column_name")).isEqualTo("date_column");
                assertThat(rs.getInt("data_type")).isEqualTo(Types.DATE);
                assertThat(rs.getString("type_name")).isEqualTo("date");

                rs.next();
                assertThat(rs.getString("column_name")).isEqualTo("time_column");
                assertThat(rs.getInt("data_type")).isEqualTo(Types.TIME);
                assertThat(rs.getString("type_name")).isEqualTo("time");

                rs.next();
                assertThat(rs.getString("column_name")).isEqualTo("timestamp_column");
                assertThat(rs.getInt("data_type")).isEqualTo(Types.TIMESTAMP);
                assertThat(rs.getString("type_name")).isEqualTo("timestamp");

                rs.next();
                assertThat(rs.getString("column_name")).isEqualTo("timestamp_with_timezone_column");
                assertThat(rs.getInt("data_type")).isEqualTo(Types.TIMESTAMP);
                assertThat(rs.getString("type_name")).isEqualTo("timestamptz");
            }
        }
    }
```

今回作成したテーブルのカラムのメタデータを取得しているのですが

```java
            try (ResultSet rs = databaseMetaData.getColumns(null, null, "t", "%")) {
```

よーく見ると`timestamp`と`timestamp with time zone`それぞれのカラムに対する`java.sql.Types`の扱いが同じ`TIMESTAMP`になっています。

```java
                rs.next();
                assertThat(rs.getString("column_name")).isEqualTo("timestamp_column");
                assertThat(rs.getInt("data_type")).isEqualTo(Types.TIMESTAMP);
                assertThat(rs.getString("type_name")).isEqualTo("timestamp");

                rs.next();
                assertThat(rs.getString("column_name")).isEqualTo("timestamp_with_timezone_column");
                assertThat(rs.getInt("data_type")).isEqualTo(Types.TIMESTAMP);
                assertThat(rs.getString("type_name")).isEqualTo("timestamptz");
```

`timestamp with time zone`の方は、`java.sql.Types#TIMESTAMP_WITH_TIMEZONE`を返してくれないんですね？

型名（`type_name`）は`timestamp`と`timestamptz`と区別できているんですけどね。

### issue

これに関してはまったく同じ問題を提起したissueがありましたが、解決していないようです…。

https://github.com/pgjdbc/pgjdbc/issues/2485

# まとめ

というわけで、PostgreSQLのJDBCドライバーのメタデータで見ると`timestamp`と`timestamp with time zone`型を区別できないという話でした…。