javamysqldatetimejdbcoffsetdatetime

How to directly retrieve a UTC OffsetDateTime using MySQL Connector/J?

I prefer all times to be in UTC, except for when they are displayed. At the last moment, they can be converted to a local time for display only.

I've always stored UNIX timestamps (seconds since epoch) in MySQL before, in an integer field. I'm working with someone else's database schema that I cannot change which uses a DATETIME that stores a raw time without even an offset.

How can I retrieve and send time objects with the database in UTC, so the database is doing absolutely no conversions whatsoever?

Although written for PostgreSQL, I've tried to follow the excellent answers by Basil Bourque on the questions below. I use Instant everywhere, except as he shows I use an OffsetDateTime when interacting with JDBC, because it's the only modern moment class guaranteed to be supported by JDBC 4.2.

I'm using MySQL Connector/J driver version 8.0.27, and I am connecting with &preserveInstants=false&connectionTimeZone=UTC.

Let's look at a DATETIME in the database stored as "2022-01-14 11:00:00" which is understood to be UTC.

resultSet.getString(columnName); // "2022-01-14 11:00:00"
resultSet.getObject(columnName, OffsetDateTime.class).toString() // "2022-01-14T11:00-04:00"
resultSet.getObject(columnName, OffsetDateTime.class).toInstant().toString() // "2022-01-14T15:00:00Z"

When it's creating the OffsetDateTime, it's assigning either the system or server (same machine) offset of "-04:00" and messing everything up. I thought my connection options of &preserveInstants=false&connectionTimeZone=UTC should have stopped that.

I know I could immediately set the offset to "+00:00" afterward with .withOffsetSameLocal(ZoneOffset.UTC).toInstant(), but how do I avoid doing that and have it directly create an OffsetDateTime that is in UTC?

Solution

  • TIMESTAMP WITHOUT TIME ZONE

    You’ve pinpointed the core issue: The author of your table defined the column as a MySQL DATETIME type. That type represents only a date with time-of-day but lacks the context of a time zone or offset. That MySQL DATETIME column is akin to the SQL standard type of TIMESTAMP WITHOUT TIME ZONE.

    java.time.LocalDateTime

    So you are using the wrong Java class when retrieving a value from that DATETIME column. Rather than use OffsetDateTime, use LocalDateTime. Like DATETIME ( and TIMESTAMP WITHOUT TIME ZONE), the Java type LocalDateTime represents a date with time-of-day but no zone or offset.

    LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

    Determine a moment

    You can the assign an offset or time zone to pinpoint a moment, a point on the timeline.

    OffsetDateTime odt = ldt.atOffset( ZoneOffset.UTC ) ;

    Or:

    ZoneId z = ZoneId.of( "America/Edmonton" ) ;
ZonedDateTime zdt = ldt.atZone( z ) ;

    You said:

    it's assigning either the system or server (same machine) offset of "-04:00" and messing everything up.

    Some part along the route of traversal is trying to help you along with your code’s intention of starting with a date and time only value that ends up with an offset assigned. Likely your JDBC driver is the part assigning an offset. Since no offset is stored with a MySQL DATETIME, some offset needs to be assigned in order to produce the OffsetDateTime object you requested.

    As shown above, a better approach is to not ask for a OffsetDateTime object when using a database column lacking an offset.

    You asked:

    How can I retrieve and send time objects with the database in UTC, so the database is doing absolutely no conversions whatsoever?

    You cannot exchange a moment as seen in UTC with a database column lacking the concept of an offset-from-UTC.

    If you want to represent moments in a SQL database, you must use the appropriate data type, a type akin to the SQL standard type TIMESTAMP WITH TIME ZONE.

    What you are asking for in exchanging a date-time-with-offset value with a database column that is date-time-only is like asking to store a price in Japanese Yen in a mere numeric column. You can pretend that such recorded values represent a price in Yen, but you do not know. Someone could just as well store prices in Euros or Pesos there.

    Since you seem to be stuck in a pickle regarding your current work situation, you can pretend that your TIMESTAMP WITHOUT TIME ZONE column stores UTC values. That is what my code example above does, essentially.

    To pretend while writing values, adjust to UTC, then extract a LocalDateTime.

    ZonedDateTime zdt = ZonedDateTime.now( z ) ;
OffsetDateTime odt = zdt.withOffsetSameInstant( ZoneOffset.UTC ) ; 
LocalDateTime ldt = odt.toLocalDateTime() ;
myPreparedStatement.setObject( … , ldt ) ;

    This pretending approach is fraught with peril, and irresponsible. The database is tracking a fiction, not the truth. Be sure to document this hack, as any programmer new to the system would be thoroughly confused.

    By the way, notice that the type names TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE are misnomers. From what I’ve been told, and from what I’ve seen in drafts, the SQL standard actually meant offsets only, not really time zones. That is why OffsetDateTime is the only Java class mapped in JDBC for matching to TIMESTAMP WITH TIME ZONE.