Search code examples
mysqljdbcjooq

JOOQ date as is, ignore timezone

I get 2019-09-01 from a date column in SQL but 1 day before while use Jooq. I think it is because my application ( OS level and Java frontend ) uses US/Eastern, but the DB uses UTC. See the SQL below:

select kpisd1, convert_tz( kpisd1, 'US/Eastern', @@session.time_zone), convert_tz( kpisd1, @@session.time_zone, 'US/Eastern' ), @@session.time_zone from kpi where kpiprc = '00006330263815' and kpikpi = 'TURN' and kpists = 'HIST';
+---------------------+--------------------------------------------------------+---------------------------------------------------------+---------------------+
| kpisd1              | convert_tz( kpisd1, 'US/Eastern', @@session.time_zone) | convert_tz( kpisd1, @@session.time_zone, 'US/Eastern' ) | @@session.time_zone |
+---------------------+--------------------------------------------------------+---------------------------------------------------------+---------------------+
| 2019-09-01 00:00:00 | 2019-09-01 04:00:00                                    | 2019-08-31 20:00:00                                     | UTC                 |
+---------------------+--------------------------------------------------------+---------------------------------------------------------+---------------------+
1 row in set (0.00 sec)

My C program actually, which is what is updating the database, does not really do any timezone conversion before adding it to the DB. It gets the date based on the OS timezone, and then uses that date. So if it adds 9/1/2019, I expect to get back 9/1/2019. I tried select date( kpisd1 ), but that also did not work. Even when I print out the raw java.sql.Date, it does not match the DB, which means it is probably being converted inside JDBC, I guess. Any ideas?

My only other option is to convert the DB time zone from UTC to "US/Eastern" ( to match the timezone on my application server ), but I need to research the ramifications of that, so am trying to not rush to such a drastic step ( this is a production environment ).

Solution

  • Based on advice from @knutwannheden, I changed:

                    databaseUrl = "jdbc:mysql://" + host.trim() + ":3306/" + db.trim();

    to

                    Calendar now = Calendar.getInstance();
                databaseUrl = "jdbc:mysql://" + host.trim() + ":3306/" + db.trim() +
                        "?serverTimezone=" + now.getTimeZone().getID();

    I chose serverTimezone based on the documentation referenced above:

    serverTimezone

    Override detection/mapping of time zone. Used when time zone from server doesn't map to Java time zone

    Since version: 3.0.2

    Yes, in my case this is actually not true - my MySql server is set to UTC, which does map to the client, BUT since I did not want it mapping, I tried this and it worked. I did NOT try the other timezone parameters because this one worked right away, but there is a decent chance that noTimezoneConversionForDateType or one of the other time zone parameters would have worked.

    If anyone has an concerns about this solution, please mention them. I am sort of cheating, I expect, but I cannot think of any really dangerous side effects right now.