Convert server date timestamp stored in central time based on user timezone

Question

The dates with timestamps are inserted/saved into the database with datatype datetime in Central Time (CT) regardless of the timezone. When I access the application in eastern timezone and retrieve those dates from my time zone, which is Eastern Time (ET), I would like the dates to render in my Eastern Time zone and not central time. For example, if the stored date is 2018-11-19 13:08:44 in the database in Central Time, I need to get it back as 2018-11-19 14:08:44 if I connect from Eastern or 2018-11-19 12:08:44 if I connect from Mountain Time Zone. I have a Java bean and I need to set the retrieved date converted to my timezone and display the date in a csv file. How do I accomplish this? Java is the server side language. Thank you very much.

Answer 1

You have not provided enough detail to give you precise help. But here are some general guidelines.

 Data type of column

To record moments, your database table column must be defined with a type akin to the SQL standard TIMESTAMP WITH TIME ZONE. The equivalent in MySQL seems to be TIMESTAMP.

Beware of tools with an anti-feature of dynamically applying a default time zone to the value extracted from the database. This creates the illusion of the stored value having that particular time zone. This is certainly not the case in MySQL 8 where TIMESTAMP is documented as always being stored in UTC.

Do not use the type TIMESTAMP WITHOUT TIME ZONE as that records only the date and time-of-day but no context of time zone or offset-from-UTC. The equivalent in MySQL seems to be DATETIME.

UTC is your friend

Generally best to track moments as seen through UTC, that is, with an offset-from-UTC of zero hours-minutes-seconds. You should learn to think of UTC as the One True Time, all other zones and offsets are but mere variations. All programmers and sysadmins should be working in UTC for their logging, debugging, data storage, and data exchange. Apply a time zone only as required by business logic or for presentation to the user.

inserted/saved into the database with datatype datetime in Central Time (CT) regardless of the timezone

I have no idea what that means.

If you want to track moments, specific points on the timeline, you must have a time zone or offset-from-UTC. As I suggested, using UTC itself is strongly recommended.

In java.time, the Instant class represents a moment in UTC, always in UTC.

Instant instant = Instant.now() ;  // Capture current moment in UTC.

Oddly, the JDBC 4.2 standard does not require support for the two most commonly used java.time classes: Instant and ZonedDateTime. The third class for moments, OffsetDateTime, is required. No matter, we can easily convert. Specify an offset of zero using the constant ZoneOffset.UTC.

OffsetDateTime odt = instant.toOffset( ZoneOffset.UTC ) ;

Or skip the Instant.

OffsetDateTime odt = OffsetDateTime.now( ZoneOffset.UTC ) ;

Send to the database.

myPreparedStatement.setObject( … , odt ) ;

Retrieve from the database.

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

Notice that all this code is working in UTC exclusively. No time zones involved, just an offset of zero hours-minutes-seconds.

Time zones

Central Time (CT) …Eastern Time (ET) …

These are not time zones. These are pseudo-zones, commonly used in the media, but never to be used in your programming.

Specify a proper time zone name in the format of Continent/Region, such as America/Montreal, Africa/Casablanca, or Pacific/Auckland. Never use the 2-4 letter abbreviation such as EST or IST as they are not true time zones, not standardized, and not even unique(!).

ZoneId

While ZoneOffset is for mere offsets (a number of hours-minutes-seconds), the ZoneId class is for time zones (a history of past, present, and future changes to the offset used by the people of a particular region).

ZoneId z = ZoneId.of( "America/Montreal" ) ;  

ZonedDateTime

We can apply a ZoneId to an Instant or OffsetDateTime to view that moment through the wall-clock time (offset) used by the people of that region.

ZonedDateTime zdt = odt.atZoneSameInstant( z ) ; 

Text

To create strings in standard ISO 8601 format representing the value of our java.time objects, simply call toString.

To generate text in other custom formats, or even to automatically localize, see the DateTimeFormatter and DateTimeFormatterBuilder classes. Search Stack Overflow as this has been covered many many times already.

Refactoring

You mentioned "datetime" in your Question. If you meant that your column is literally of type DATETIME, you have a mess on your hands. You should refactor your database to use proper types.

For that refactoring, extract the values from that column, retrieving as the Java type LocalDateTime. This class is for a date and time-of-day but lacking the context of a zone or offset. As such, this value is not a moment, is not a point on the timeline.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;  // Retrieving value from a MySQL 8 column of type `DATETIME`. 

You claim to know the time zone intended, but not recorded, for those values. Keep in mind that you are only guessing. You do not really know for sure what the original zone/offset was, as that value was discarded.

But if you are certain of the intended zone, specify as a ZoneId. By "Central Time (CT)" I will assume you meant a time zone such as America/Chicago.

ZoneId zChicago = ZoneId.of( "America/Chicago" ) ;

Apply this ZoneId object to each retrieved LocalDateTime object.

ZonedDateTime zdt = ldt.atZone( zChicago ) ;

Convert our ZonedDateTime to an OffsetDateTime given the support required by the JDBC 4.2 spec. (Your JDBC driver may optionally support ZonedDateTime, so you could try that if you like.)

OffsetDateTime odt = zdt.toOffsetDateTime() ;  // Stripping away the rich time zone information, trading it for simply a number of hours-minutes-seconds ahead/behind UTC. 

Send to the database in a new column of type TIMESTAMP, as should have originally been done when the database was designed.

myPreparedStatement.setObject( … , odt ) ;

Notice that the OffsetDateTime object we are sending is not in UTC. That object will carry the offset used by the Chicago-region at that time-of-day on that date (several hours behind UTC). Of course that offset varies because of politician-imposed anomalies such as Daylight Saving Time (DST).

But you should not have to worry about the offset in the OffsetDateTime. Your MySQL-specific JDBC driver should take care of converting to UTC per the requirements of that type as defined in MySQL. You should do a bit of testing to be sure this is working properly, and according to your understanding. If while debugging you want to see the moment of this OffsetDateTime in UTC, convert to an Instant: odt.toInstant().toString().

When you have finished populating the new column of type TIMESTAMP, drop the old column of type DATETIME.

---