Can we say the default java.time.Instant type handler is not doing right ?

[WestFarmer]

java.time.Instant:

An instantaneous point on the time-line.

I think it means it's not time-zone aware.

but the current java.time.Instant type handler doing this:

public class InstantTypeHandler extends BaseTypeHandler<Instant> {

  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, Instant parameter, JdbcType jdbcType)
      throws SQLException {
    ps.setTimestamp(i, Timestamp.from(parameter));
  }

  @Override
  public Instant getNullableResult(ResultSet rs, String columnName) throws SQLException {
    Timestamp timestamp = rs.getTimestamp(columnName);
    return getInstant(timestamp);
  }

  @Override
  public Instant getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
    Timestamp timestamp = rs.getTimestamp(columnIndex);
    return getInstant(timestamp);
  }

  @Override
  public Instant getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
    Timestamp timestamp = cs.getTimestamp(columnIndex);
    return getInstant(timestamp);
  }

  private static Instant getInstant(Timestamp timestamp) {
    if (timestamp != null) {
      return timestamp.toInstant();
    }
    return null;
  }
}

while we know that java.sql.ResultSetr#getTimestamp(columnIndex) will be impacted by the JVM's timezone.

the actual Instant object got will have incorrect value.

why not doing something like this:

public Instant getNullableResult(ResultSet rs, String columnName) throws SQLException {
  LocalDateTime lt = rs.getObject(columnName, LocalDateTime.class);
  if(lt == null)
	  return null;
  return lt.toInstant(ZoneOffset.UTC);
}

the date time stuff is complex as hell, so this maybe a silly question, correct me I am wrong.

[harawata]

Hello @WestFarmer ,

You can say that Instant is not time zone aware, but it does not necessarily mean the date/time stored in DB should be in UTC+00:00 time zone. Those are two unrelated things.

And some DBs store submitted date/time in UTC+00:00 time zone implicitly (e.g. timestamp with time zone of PostgreSQL, timestamp with time zone of Oracle).
The proposed converter implementation won't work properly with these types.

Although I may not fully understand your app's requirements, you should check if LocalDateTime is a better fit than Instant.

[OT]
This is my personal view, but storing Instant as date/time does not make much sense to me.
It seems more appropriate to use DECIMAL(20, 9) or something.