Time zones differences between database and JVM with java.util.Date and DATETIME

[juur]

Hi,

I think there is an issue when the JVM has a different TZ to the database. The code around here: https://github.com/j256/ormlite-core/blob/master/src/main/java/com/j256/ormlite/field/types/BaseDateType.java#L55 is silent on the Locale/TimeZone/Calendar. The JRE defaults to TimeZone.getDefault(), this can mean if your database is in one TZ and your JVM is in another TZ, time will drift. One option, I think, is to use TimeZone.setDefault() to override the JVM, but this may have other consequences.

My suggestion would be to include an option to set the ID of a TimeZone so that a Calendar can be retrieved somewhere in BaseDateType (or perhaps just DateType), and passed to the SimpleDateFormat object, assuming this solves the problem?

[j256]

Thanks for this @juur . There is a format string but it is designed as SImpleDataFormat string. Any idea how to hack in the TZ? Maybe start with {TZ XXX} at the start of the format or something?

[j256]

My fix handles the date-string type but I think your issue is more generic and applies when we are dealing anytime with Date or Calendar entries. Does it? Aren't Date's supposed to be timezone agnostic? It's the string format that gets us into trouble. I probably need to do a better job of parsing the default date string for example.

[juur]

As per the timestamps, it's been almost 3 years since I looked at the code, however...

The problem is: your database can be +0100 and your JVM can be +0200 In my example (from memory) MariaDB was configured to run in a specific timezone (default_time_zone='UTC') whereas the servers were in different timezones because they were distributed geographically. This is a problem (below is mariadb + Linux command line from the same server at the same time):

show global variables like 'time_zone';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| time_zone     | UTC   |
+---------------+-------+
select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2021-10-29 18:25:48 |
+---------------------+
1 row in set (0.001 sec)
$ date
Fri 29 Oct 19:25:59 BST 2021

I think as you convert from SQL DATETIME to in JVM representation, there is no TZ conversion performed. Whereas, if you set the global JVM TZ, or do something with Calendar objects maybe, you can ensures the timezones are consistent.

Not sure if this helps as I haven't touched the Java code for ages

[juur]

Is the correct way to handle this to specify an 'override' TZ in the DatabaseConnection, then anything that 'does' time should use a Calendar object (or whatever) derived from the DatabaseConnection - which defaults to the JVM's default?