The Answer by Matt Johnson is correct.
- You can get a date if you are certain the moment stored in the database represented the first moment of a day in some time zone somewhere.
- You can guess about the time zone, but you cannot be certain of the original time zone when more than one zone shared an offset-from-UTC at that moment.
java.time
Here is some Java code using the modern java.time classes.
Imagine you take a local date, say 2016-12-28, in a given time zone, say America/New_York, and convert the start of that date into UTC (in this case 2016-12-28T05:00:00Z).
Notice that we let java.time determine the first moment of the day through the LocalDate::atStartOfDay method. Do not assume the day begins at 00:00:00. Anomalies such as Daylight Saving Time mean the day may start at another time such as 01:00:00.
LocalDate localDate = LocalDate.parse( "2016-12-28" ) ;
ZoneId zNewYork = ZoneId.of( "America/New_York" ) ;
ZonedDateTime zdtNewYork = localDate.atStartOfDay( zNewYork ) ; // First moment of the day in that zone on that date.
Adjust to a UTC value by extract an Instant. An Instant is always in UTC, by definition.
Instant instant = zdtNewYork.toInstant() ;
Store in the database in a column of type akin to SQL-standard TIMESTAMP WITH TIME ZONE.
myPreparedStatement.setObject( … , instant ) ;
get back to the original local date without knowing the time zone?
Retrieve.
Instant instant = myResultSet.getObject( … , Instant.class ) ;
Now experiment with every time zone. FYI, see list of zone names at Wikipedia, though that page may be outdated.
For each zone, adjust our Instant (our UTC moment) into that zone to get a ZonedDateTime object. Some moment, same point on the timeline, but different wall-clock time.
For each ZonedDateTime, extract the date only, without time-of-day and without time zone. Renders a LocalDate object. Ask that LocalDate to determine the first moment of the day (which may or may not occur at 00:00:00) in our time zone under consideration. This produces another ZonedDateTime object.
From that second ZonedDateTime, extract an Instant to adjust back into UTC. Compare this new Instant object with our original Instant. If they are the same, we have a hit. We have identified a time zone when a day began at that moment stored in the database. So the LocalDate we produced above is the same as the date originally stored in inappropriately in our database.
List< ZoneId > hits = new ArrayList<>() ;
LocalDate originalLocalDate = null ;
Set< String > zoneIds = ZoneId.getAvailableZoneIds() ; // Gets the set of available zone IDs.
for( String zoneId : zoneIds ) {
ZoneId z = ZoneId.of( zoneId ) ; // Get zone with that name.
ZonedDateTime zdt = instant.atZone( z ) ;
LocalDate ld = zdt.toLocalDate() ; // Extract the date-only value, dropping the time-of-day and dropping the time zone.
ZonedDateTime startOfDay = ld.atStartOfDay( z ) ; // Determine first moment of the day on that date in that zone.
Instant instantOfStartOfDay = startOfDay.toInstant() ; // Adjust back to UTC.
boolean hit = instant.equals( instantOfStartOfDay ) ;
if( hit ) {
originalLocalDate = ld ;
hits.add( z ) ; // Collect this time zone as the zone possibly used originally.
}
}
See this code run live at IdeOne.com.
When run we see that America/New_York time zone on that date had an offset of five hours behind UTC. You can see from this list, there are many other time zones sharing that same offset of -05:00.
originalLocalDate.toString(): 2016-12-28
hits.toString(): [America/Panama, America/Indiana/Petersburg, America/Eirunepe, Cuba, Etc/GMT+5, Pacific/Easter, America/Fort_Wayne, America/Havana, America/Porto_Acre, US/Michigan, America/Louisville, America/Guayaquil, America/Indiana/Vevay, America/Indiana/Vincennes, America/Indianapolis, America/Iqaluit, America/Kentucky/Louisville, EST5EDT, America/Nassau, America/Jamaica, America/Atikokan, America/Kentucky/Monticello, America/Coral_Harbour, America/Cayman, Chile/EasterIsland, America/Indiana/Indianapolis, America/Thunder_Bay, America/Indiana/Marengo, America/Bogota, SystemV/EST5, US/Eastern, Canada/Eastern, America/Port-au-Prince, America/Nipigon, Brazil/Acre, US/East-Indiana, America/Cancun, America/Lima, America/Rio_Branco, America/Detroit, Jamaica, America/Pangnirtung, America/Montreal, America/Indiana/Winamac, America/New_York, America/Toronto, SystemV/EST5EDT]
Beware that the resulting list of zones may not be distinct. Some zone names are aliases, where one zone has multiple names. For example, the old Asia/Calcutta is now Asia/Kolkata.
About java.time
The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.
The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.
To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.
You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.
Where to obtain the java.time classes?
The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.
