LoginSignup
1
1

More than 1 year has passed since last update.

@charoninTIS株式会社

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

Last updated at Posted at 2022-10-15

What's？

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

調べてみました。

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

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

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

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

Issuing a Query and Processing the Result / Using Java 8 Date and Time classes

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

image.png

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

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

環境

今回の環境は、こちら。

$ 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で用意します。

$ 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

テーブルを作成する

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

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)
);

定義確認。

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プロジェクトを作成。

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>

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

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 {

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

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

確認してみる

データの登録とクエリー

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

    @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で問題なく各カラムに値を設定できます。

            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クラスの指定が必要です。

            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を使うことで、テーブルの各カラムのデータ型を見ることができます。

    @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");
            }
        }
    }

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

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

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

                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がありましたが、解決していないようです…。

まとめ

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

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