CODEWITHSUNDEEP
java
Displayname
Displayname

Reputation: 55

JDBC not returning same time zone as system

My database is set to default time zone UTC. My Driver should be using system time which I am running my application in a ubuntu container that is set to UTC. I know this because when I call Timezone.getDefault() is returns UTC. So according to my understanding, these times should be close to identical +-1 hour. I am currently in CST. The database time is retrieved with resultset.getTimestamp().getTime(). One (or two) of these numbers are not UTC.

Database time: 1674680930000
System.currentTime(): 1674703050253
Calendar time: 1674703050255

Upvotes: 0

Views: 555

Answers (1)

Basil Bourque
Basil Bourque

Reputation: 340178

My database is set to default time zone UTC.

Write your Java code in such a way that you don’t care about the current default time zone of the server OS nor the current default time zone of the database session.

Edit your Question to provide actual example code. Then we can critique.

My Driver should be using system time which I am running my application in a ubuntu container that is set to UTC.

No, incorrect. Your JDBC driver is unaware of the host OS’ current default time zone.

What makes you think otherwise? Edit your Question to provide details.

I know this because when I call Timezone.getDefault() is returns UTC.

That code gets the current default time zone of your JVM. Your JVM’s current default time zone may or may not be the same as host OS’ the current default time zone. The two defaults can be set independently of one another.

TimeZone is one of the legacy date-time classes that you should avoid. Use only java.time classes. Never use Dote, Calendar, Timestamp, etc.

To determine your JVM’s current default time zone:

ZoneId z = ZoneId.systemDefault() ;

I am currently in CST.

As I said above, you should write your Java code to avoid depending on any default time zone. Pass optional parameters with your desired/expected time zone.

Record the current moment as seen in UTC.

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;
myPreparedStatement.setObject( … , odt ) ;

Retrieve from database.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

Adjust to your desired time zone. CST is not a real time zone. Perhaps you meant America/Chicago.

ZoneId z = ZoneId.of( "America/Chicago" ) ;
ZonedDateTime zdt = odt.atZoneSameInstant( z ) ;

The database time is retrieved with resultset.getTimestamp().getTime().

Incorrect. The ResultSet class has no getTimestamp method taking no arguments.

Perhaps you mean to retrieve a date-time value from a column. How to do that depends on the data type of the column.

Edit your Question to document the exact data type of your column, the database engine, and the version.

For MySQL 8:

SQL standard MySQL Java
TIMESTAMP WITH TIME ZONE TIMESTAMP OffsetDateTime
TIMESTAMP WITHOUT TIME ZONE DATETIME LocalDateTime

And search Stack Overflow to learn more. Exchanging date-time values with a database has been covered extensively.

You commented:

So it looks like I missed an important distinction between session and global env variables in MySQL:

Neither of those need be relevant to your Java code for exchanging date-time values with a database.

Upvotes: 1

Related Questions