3

I'm using jooq (v3.11.9) to access a MySQL database that is running in UTC time. I've using generated entities and am using JSR-310 time types. The option I'm using in my config:

<javaTimeTypes>true</javaTimeTypes>

My understanding is that the MySQLdatetime and timestamp types both map to LocalDateTime which makes sense as MySQL doesn't store timezone information with the times. However when I run queries on a machine in a different timezone (in my case EST) the dates are all in my local machine timezone even though the session timezone is UTC.

I've confirmed that the session timezone is UTC

dslContext.fetch("SELECT @@system_time_zone,  @@global.time_zone, @@session.time_zone;")

returns 

|@@system_time_zone|@@global.time_zone|@@session.time_zone|
+------------------+------------------+-------------------+
|UTC               |SYSTEM            |SYSTEM             |
+------------------+------------------+-------------------+

Example of timezone conversion:

dslContext.select(MY_TABLE.EPOCH_DT_TM, MY_TABLE.CREATION_TIMESTAMP).from(MY_TABLE).limit(1).fetch()

+-----------------------+-----------------------+
|epoch_dt_tm            |creation_timestamp     |
+-----------------------+-----------------------+
|2019-04-18T13:57:39.163|2019-09-24T16:06:47.754|
+-----------------------+-----------------------+
// CAST to STRING PROPERLY USES SESSION TIMEZONE 
dslContext.select(MY_TABLE.EPOCH_DT_TM.cast(org.jooq.impl.SQLDataType.VARCHAR(100)), MY_TABLE.CREATION_TIMESTAMP.cast(org.jooq.impl.SQLDataType.VARCHAR(100))).from(MY_TABLE).limit(1).fetch()
+--------------------------+--------------------------+
|cast                      |cast                      |
+--------------------------+--------------------------+
|2019-04-18 17:57:39.163000|2019-09-24 20:06:47.754000|
+--------------------------+--------------------------+

The fields in my generated entities:

    public final TableField<MyTableRecord, LocalDateTime> EPOCH_DT_TM = createField("epoch_dt_tm", org.jooq.impl.SQLDataType.LOCALDATETIME, this, "");
    public final TableField<MyTableRecord, LocalDateTime> CREATION_TIMESTAMP = createField("creation_timestamp", org.jooq.impl.SQLDataType.LOCALDATETIME.nullable(false).defaultValue(org.jooq.impl.DSL.field("CURRENT_TIMESTAMP(6)", org.jooq.impl.SQLDataType.LOCALDATETIME)), this, "");

So my questions are:

  1. Is this expected behavior? Shouldn't the record get populated with the raw (non timezoned) date in the table. Are the dates still getting converted to java.sql.Timestamp under the hood for some reason?

  2. If this is expected behavior is there any way to ensure that you get dates in the session timezone regardless of the local timezone on the client machine? It is very hard to test locally if the behavior of the code is dependent on the machine timezone.

Thank you in advance for your help.

lights
  • 1,034
  • 1
  • 8
  • 22

1 Answers1

2

I recently found that depending on the database driver being used, jOOQ can exhibit some strange behavior in DateTime parsing. jOOQ returns offset date time as Z (UTC) even though it's not

Specifically, in my case, using a different Postgres driver resulted in DefaultBinding.java receiving a calendar object that has a timestamp, but calling toString on it in order to parse. Turns out, toString does not print the timezone, then jOOQ inferred that it was in local time.

For me, the offending lines in DefaultBinding.java (I was using a timestamp with timezone) were:

else if (type == OffsetDateTime.class) {
    result = (T) offsetDateTime(ctx.resultSet().getString(ctx.index()));
}

You may be on a different line in that series of else ifs based on not having a time zone.

In my testing, I also found that changing the system time changed the result, but changing the session time did nothing.

Fortunately for me, a switch to the standard Postgres driver resolved the problem. If it didn't, I was going to look in to overloading the binding for OffsetDateTime to fix the use of toString and it's associated stripping of the relevant time zone. You may need to pursue that path, unfortunately, unless you too are using a SQL driver than could be upgraded or replaced. Or, you could store it with a timezone and then convert to the desired timezone when you load from the database.

Evan
  • 2,441
  • 23
  • 36
  • 1
    I undeleted a follow up post regarding this with more detail so you could review it. https://stackoverflow.com/questions/57808703/jooq-seems-to-apply-the-current-system-timezone-when-selecting-postgres-timestam – Evan Sep 24 '19 at 18:36
  • cool! thanks for your answer. i will try to step through to see if this could be a similar issue. – lights Sep 24 '19 at 18:40
  • so jooq is calling t.toLocalDateTime() in TimestampToLocalDateTimeConverter which converts the timestamp to the same time as the timestamp in local time. im not sure why, if there's an implicit timezone (the local JVM time) why isn't that made explicit by creating a ZonedDateTime with the local timezone. – lights Sep 25 '19 at 15:21
  • You could pursue overloading the default binding to do what you want. https://www.jooq.org/doc/3.12/manual/sql-building/queryparts/custom-bindings/ – Evan Sep 25 '19 at 15:42
  • 1
    not sure if i want to go down that route, things are complicated enough as they are. I just wish it were better documented that LocalDateTime in jooq is treated as a date in the local time zone, as opposed to a date without a timezone (which is what its supposed to represent). – lights Sep 25 '19 at 16:04
  • Have you considered using timestamp with timezone and then setting it to the desired time when you load from the database? That would produce the desired effect too. Updated my answer accordingly. – Evan Sep 26 '19 at 14:59
  • so i'm using mysql which doesn't have a timestamp with timezone type so that isn't an option. what i ended up doing was I just went back to using timestamp for the time columns. java.sql.Timestamp represents an actual "point in time" which is what I want. Then I use Timestamp.from(instant) and timestamp.toInstant() methods to convert to an Instant. – lights Sep 26 '19 at 16:43