CODEWITHSUNDEEP
javamysqltimequerydsl
dushshantha
dushshantha

Reputation: 447

How to handle Time Durations stored in MySQL TIME field?

I ran into an issue where my MySQL Table has a field that stores a UTC Time Offset. The field is of the type TIME. This is for Legacy reasons and I cannot change the column type to a better representation. MySQL, however, supports this. It allows the user to store a negative value in a time field to represent a negative time difference. I use querydsl for the Entity class generation and it mapped this field to a java.sql.Time object. Now I am facing an issue when there is a negative value in the DB. The java.sql.Time class converts any negative value into the time in the previous day. For example, if the value in the DB is -04:00:00, the Java object has the time 20:00:00. I was considering an option of converting this somehow to a Duration but then I ran into the issue where UTC timezone offsets overlap in certain regions.

Here, the negative offset goes down to -12:00:00 and the positive side goes up to +14:00:00. Now the problem is that I can't tell the difference between the values in the Java object for -12:00 and +12 because they both set to 12:00:00 in the java object. +12:00:00 to +14:00:00 also has overlapping values from both sides.

Any suggestion to handle this situation is highly appreciated.

Upvotes: 0

Views: 230

Answers (1)

Basil Bourque
Basil Bourque

Reputation: 338316

I cannot help with QueryDSL.

java.time

In java.time, represent an offset-from-UTC with java.time.ZoneOffset class.

You might try either of these two approaches to see if they work.

Parse MySQL TIME textually as java.time.ZoneOffset

Perhaps you could retrieve your unfortunate MySQL TIME values as text. Then parse as ZoneOffset objects.

String o = myResultSet.getString( … ) ;  // "-12:00:00" or "14:00:00", and such.
ZoneOffset zoneOffset = ZoneOffset.of( o ) ;

Retrieve as ZoneOffset

I have no idea if this works, but you could try extracting the MySQL TIME as a ZoneOffset directly. Specify the desired Java class as an argument.

ZoneOffset zoneOffset = myResultSet.getObject( … , ZoneOffset.class ) ;

Upvotes: 2

Related Questions