Method getColumnType(int) from ResulSetMetaData doesn't differentiate TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP WITH TIME ZONE

[staszko]

I'm submitting a ...

• bug report
• feature request

It seems like bug because Driver is inconsistent with itself. On the other hand I refer to mechanics introduced in Java 8, so feature request is also appropriate.

Describe the issue
Postgresql has two different types: TIMESTAMP WITHOUT TIME ZONE and TIMESTAMP WITH TIME ZONE
ResultSetMetaData.getColumnTypeName(int) differentiate them returning "timestamp" or "timestampz" respectively
JDBCType.valueOf(ResultSetMetaData.getColumnType(int)) in both cases returns JDBCType.TIMESTAMP

Since java 8 there is dedicated JDBCType.TIMESTAMP_WITH_TIMEZONE
https://docs.oracle.com/javase/8/docs/api/java/sql/JDBCType.html#TIMESTAMP_WITH_TIMEZONE

I believe that's the one to be returned by JDBCType.valueOf(ResultSetMetaData.getColumnType(int)). Firstly to be consistent. Secondly to provide pure java way to know if column has applied time zone or not.

Why is that important?
Let's say I'm writing a framework which deals with user provided queries. I don't know type structure of returned records, so I must rely on ResultSetMetaData information. I would like to use:

• LocalDateTime for TIMESTAMP WITHOUT TIME ZONE
• OffsetDateTime for TIMESTAMP WITH TIME ZONE

To do so I need to specify java class by calling ResultSet.getObject(int columnIndex, Class<T> type) (I must recognize the type difference)
See: https://jdbc.postgresql.org/documentation/head/java8-date-time.html

Alternatively I may use OffsetDateTime regardles of time zone, but since the driver returned java.time.* classes in UTC, running query like:

SELECT
    '2020-04-24 14:00:00'::TIMESTAMP WITHOUT TIME ZONE,
    '2020-04-24 14:00:00+2'::TIMESTAMP WITH TIME ZONE;

and calling on ResultSet of that query:

System.out.println(rs.getObject(1, java.time.OffsetDateTime.class));
System.out.println(rs.getObject(2, java.time.OffsetDateTime.class));

will produce

2020-04-24T14:00Z
2020-04-24T12:00Z

Both with offset 0, but with value of second column adjusted to UTC (12:00 from 14:00+2) So again in java code I need the SQL types distinction to properly handle values for the user.

As I mentioned earlier now driver isn't consistent with itself, but not only that. Usage of ResultSetMetaData.getColumnTypeName(int) is simply not good enough. What if in the future PostgreSQL release new date-time type will be introduced? Code with hard-coded, vendor specific type names wouldn't be "bullet proof". Relying on types from JDBC specification is better option. Not to mention that using JDBCType enum instead of hard-coded Strings is less error prone clean-coding-wise.

Driver Version?
42.2.8
Java Version?
(AdoptOpenJDK)(build 1.8.0_252-b09)
OS Version?
Fedora Linux 31 Gnu/Linux 5.5.17
PostgreSQL Version?
PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 9.2.1 20190827 (Red Hat 9.2.1-1), 64-bit
To Reproduce
Steps to reproduce the behaviour:
In Java program acquire connection to sample PostgreSQL database and run code from below:

    try (Connection conn = connect();
        PreparedStatement statement = conn.prepareStatement("SELECT now()::timestamp with time zone;"); ) {
      ResultSet rs = statement.executeQuery();

      ResultSetMetaData rsMetaData = rs.getMetaData();

      System.out.println(
          String.format(
              "Java class:    %s%nVendor type:   %s%nJDBC DB Type:  %s",
              rsMetaData.getColumnClassName(1),
              rsMetaData.getColumnTypeName(1),
              JDBCType.valueOf(rsMetaData.getColumnType(1))));

    } catch (SQLException ex) {
      logger.error("Error   {} ", ex.getMessage(), ex);
    }

The would print:

Java class:    java.sql.Timestamp
Vendor type:   timestamptz
JDBC DB Type:  TIMESTAMP

Expected behaviour
JDBCType.valueOf(rsMetaData.getColumnType(1))) should return JBDCType.TIMESTAMP_WITH_TIME_ZONE

So example from previous section would print:

Java class:    java.sql.Timestamp
Vendor type:   timestamptz
JDBC DB Type:  TIMESTAMP WITH TIME ZONE

[lopata2]

The problem is still valid for the newest driver 42.5.1. I have found few others issues describing the same problem, but somehow it is always without reaction/answer. The only reaction I have found to this problem is this comment #2485 (comment) , where @bokken mentioned that pg implementation of TIMESTAMP WITH ZONE does not fully comply with the specification and for that reason they returns TIMESTAMP type.

Which doesn't seem like a good reason, even if it's not exactly consistent with the specification, the metadata should return the column type as TIMESTAMP WITH ZONE, because that's what the user expects(when column is defined in schema as TIMESTAMP WITH ZONE). Currently, there is no way to tell from the column metadata whether or not I can store an OffsetDataTime in the column.

[davecramer]

It would be useful at this point to provide a PR which addresses the issue and then we can discuss the pros and cons.
Sadly timestamps are a PITA

[lopata2]

Hello, I am not sure if I understand what you propose. I thought PR is when I fix the issue and want to merge it, or do you mean I should create separate issue?

The problem is exactly same as @staszko described. I describe it in more reproducible way.

Statement stmt = con.createStatement();   
stmt.execute("CREATE TABLE test(testcolumn timestamp with time zone)");

DatabaseMetaData dbMeta = con.getMetaData();
ResultSet columnMetadata = dbMeta.getColumns(null,null, "test", null));
columnMetadata.next();

final String columnName = columnMetadata.getString(4);  // RETURN testcolumn -> OK
final String typeName = columnMetadata.getString(6);  // RETURN TIMESTAMP WITH ZONE -> OK
final int type = columnMetadata.getInt(5);  // RETURN 93 -> WRONG -> SHOULD RETURN 2014

Class java.sql.Types define types

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type
 * {@code TIMESTAMP}.
 */
        public static final int TIMESTAMP       =  93;


    /**
     * The constant in the Java programming language, sometimes referred to
     * as a type code, that identifies the generic SQL type
     * {@code TIMESTAMP WITH TIMEZONE}.
     *
     * @since 1.8
     */
    public static final int TIMESTAMP_WITH_TIMEZONE = 2014;

So I think the columnMetadata.getInt(5) should return 2014 instead of 93.

[davecramer]

Yes, I mean provide a fix to the issue in a Pull Request. Let's see what else it breaks.

[lopata2]

Hello I have created PR #2715 as you proposed.