Save ZonedDateTime in MySQL and Glassfish
This is my first question on this forum, please be patient with me.
Oracle says, "ZonedDateTime is a date and time with a full time zone. It can allow offset at any point in time. A rule of thumb: if you want to represent a date and time without relying on a specific server context, you should use ZonedDateTime." this is exactly what I want to do, because the app deals with interactions around the world, but MySQL only stores DATETIME as TIMESTAMP, but apparently stores that as UTC so that it can be converted to any time zone. The servers we will be running will run across multiple time zones and we will not know which one will run where the cloud provider will dynamically move them based on demand and service.
So maintaining the date / time / zone in this app seems to be perfect for the new ZonedDateTime construct, but I have repeatedly got confused trying to keep everything right between the deprecated date that PrimeFaces and other component code still deliver and MySQL. who wants to deal with Timestamps that will eventually rise in 2038.
We don't want to use external date libraries like Joda or Apache.
My question is pretty straight forward, but the answer seems elusive to me, and the nuances seem to be a lot: what are the best practices for storing java ZonedDateTime in a MySQL database, reading it so that work can be done globally by users via java instant computation that looks right on local user, and will be correct regardless of the location of the Glassfish server or MySQL server, which may be in different time zones from each other and from day to day?
source to share
I think this shocked us: MySQL stores the date in UTC when it stores it as a timestamp, so as long as I do that, it doesn't matter where MySQL lives.
Glassfish can tell you where it lives by querying the server, but it can also set a property for the home office, which gives you a base of operations that will match wherever the server lives. You can do this in web.xml
<context-param>
<param-name>GLASSFISH_HOME_TIME_ZONE</param-name>
<param-value>America/New_York</param-value>
</context-param>
The data bean must do most of the work to match the data in all the data used. Problems with component libraries that are either not updated to ZonedDateTime, or only partially updated, will generally call the data using a getter, so using an overload should allow the component libraries to find the specific method it prefers. I have created a data bean that looks something like this:
public class DataBean {
private final ZoneId GLASSFISH_HOME_TIME_ZONE = ZoneId.of(FacesContext.getCurrentInstance().getExternalContext().getInitParameter( "GLASSFISH_HOME_TIME_ZONE"));
private ZonedDateTime dateToUseInGlassfish = null;
public DataBean (
Timestamp dateFromMySQL)
{
if ( dateFromMySQL == null ) {
this.dateToUseInGlassfish = null;
} else {
this.dateToUseInGlassfish = LocalDateTime.ofInstant(dateFromMySQL.toInstant(), GLASSFISH_HOME_TIME_ZONE ).atZone( GLASSFISH_HOME_TIME_ZONE );
}
}
/** Formatter for Date/Time */
private final DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("MM/dd/yyyy ' at ' h:mm a z");
/** Formatter for Date only */
private final DateTimeFormatter dateFormatter = DateTimeFormatter.ofPattern("MM/dd/yyyy");
/** Get the date string formatted with date and time */
public String getDateToUseInGlassfishDateTimeFormatted() {
if ( dateToUseInGlassfish == null ) { return null; }
String formattedDate = dateTimeFormatter.format( dateToUseInGlassfish );
return formattedDate;
}
/** Get the date string formatted with date only */
public String getgetDateToUseInGlassfishDateFormatted() {
if ( dateToUseInGlassfish == null) { return null; }
String formattedDate = dateFormatter.format( dateToUseInGlassfish );
return formattedDate;
}
/** Get the date ZDT formatted (for calculations) */
public ZonedDateTime getgetDateToUseInGlassfish() {
return dateToUseInGlassfish;
}
/** Get the date as Date (for component libraries that automatically fetch then throw up with ZDT) */
public Date getDateToUseInGlassfishDate() {
if ( dateToUseInGlassfish == null) { return null; }
return Date.from( dateToUseInGlassfish.toInstant());
}
/** Set the date from ZDT (results from calculations stored in bean) */
public void setDateToUseInGlassfish( ZonedDateTime dateToUseInGlassfish ) {
this.dateToUseInGlassfish = dateToUseInGlassfish;
}
/** Set the date from Date with an automatic convert to ZDT */
public void setDateToUseInGlassfish( Date dateToUseInGlassfish ) {
if (dateToUseInGlassfish == null) {
this.dateToUseInGlassfish = null;
} else {
this.dateToUseInGlassfish = LocalDateTime.ofInstant( Instant.ofEpochMilli( dateToUseInGlassfish.getTime()), GLASSFISH_HOME_TIME_ZONE ).atZone( GLASSFISH_HOME_TIME_ZONE );
}
}
Getting a date as a timestamp from MySQL gets it as a UTC point in time, and it looks something like this:
ResultSet resultSet = preparedSelectQuoteSql.executeQuery()) {
while (resultSet.next()) {
quoteBean = new QuoteBean(
resultSet.getTimestamp("MySQLDateColumn")
);
}
}
Insert / update into MySQL from ZonedDateTime to a timestamp that MySQL will automatically convert to UTC so that we can let MySQL live anywhere we want it to live and read the same Instant in time:
if ( insertValue instanceof ZonedDateTime ) {
if ( insertValue != null ) {
Timestamp convertedDate = Timestamp.from( ((ZonedDateTime) insertValue).toInstant() );
preparedStatement.setTimestamp( paramNumber, convertedDate );
} else {
preparedStatement.setNull ( paramNumber, Types.TIMESTAMP );
}
}
I think it works, but I WELCOME criticism.
source to share