CODEWITHSUNDEEP
javamysqldatedatetime
basickarl
basickarl

Reputation: 40561

Java: Date/Time from Database to milliseconds, hour disappearing

I am having an issue with Dates and timezones.

I have a MySQL InnoDB database which holds two fields DATE(yyyy-MM-dd) and TIME(HH:mm:ss). These are known as to be UTC (0 GMT). My computer is based in CET (+1 GMT).

• dateObject is the result from this resultSet.getTime("date_field") (java.sql.Date)

• timeObject is a result from this resultSet.getDate("time_field") (java.sql.Time)

In the database DATE is stored as 2014-02-22 and TIME 15:00

System.out.println("Untouched "+dateObject+" "+timeObject);

long date = dateObject.getTime();
long time = timeObject.getTime();

System.out.println("Touched "+new Date(date+time));

Results in the following output:

Untouched 2014-02-22 15:00:00
Touched Sat Feb 22 14:00:00 CET 2014

Why is one hour being skipped off the Touched output? I was expecting the following:

Untouched 2014-02-22 15:00:00
Touched Sat Feb 22 15:00:00 CET 2014

To rumble things up I have tried with the following also:

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
System.out.println(sdf.format(new Date(date+time)));

And result:

2014-02-22 14:00:00

All in all. I am expecting GMT+1 to show 16:00(local) and GMT+0 to display 15:00

Upvotes: 3

Views: 1415

Answers (2)

Meno Hochschild
Meno Hochschild

Reputation: 44071

The reason is similar to this SO-answer. But note following details about toString().

java.util.Date.toString() => output dependent on your system time zone
                             in format pattern "EEE MMM dd HH:mm:ss zzz yyyy"

java.sql.Date.toString() => output in format "yyyy-MM-dd" (your dateObject)
java.sql.Time.toString() => output in format "HH:mm:ss" (your timeObject)

The sql representations are not dependent on time zone. So you compare apples and peaches.

Supplementary remark:

I have invested more in testing and found:

java.sql.Date dateObj = new java.sql.Date(2014 - 1900, Calendar.FEBRUARY, 22);
Time timeObj = new Time(15, 0, 0);
Time midnight = new Time(0, 0, 0);
Date d = new Date(dateObj.getTime() + timeObj.getTime());

System.out.println("dateObj: " + dateObj + "/" + dateObj.getTime()); 
// dateObj: 2014-02-22/1393023600000, one hour less than a full day because of UTC-long

System.out.println("timeObj: " + timeObj + "/" + timeObj.getTime()); 
// timeObj: 15:00:00/50400000 => one hour less as UTC-long

System.out.println("midnight: " + midnight + "/" + midnight.getTime()); 
// midnight: 00:00:00/-3600000 => one hour less, negative!

System.out.println(new Date(dateObj.getTime())); // Sat Feb 22 00:00:00 CET 2014
System.out.println(new Date(timeObj.getTime())); // Thu Jan 01 15:00:00 CET 1970
System.out.println(d); // Sat Feb 22 14:00:00 CET 2014

So I strongly suspect following effect: Both dateObject and timeObject are been calculated your system time zone, therefore their utc-long values show both one hour less - the time zone offset. If you combine both in one Date-object by just summarizing up then one of both deltas gets lost because one single date object cannot take in account two offsets.

Conclusion: You tried to combine date and time by summarize their utc-longs, but this is in general a faulty approach. Date plus Date is not Date, but undefined! In domain-specific language you can only add a duration/period to a date/time. So a solution having a midnight object could be:

Date d = new Date(dateObj.getTime() + timeObj.getTime() - midnight.getTime());
System.out.println(d); // Sat Feb 22 15:00:00 CET 2014, correct - what you wanted

Upvotes: 0

basickarl
basickarl

Reputation: 40561

I think I did answer ma question (Remember timeObject in the db is 15:00:00 at UTC):

TimeZone tz = TimeZone.getTimeZone("Gmt0");
SimpleDateFormat sdfFull = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
sdfFull.setTimeZone(tz);

Date updateDate = sdfFull.parse(dateObject.toString()+" "+timeObject.toString());

System.out.println(updateDate);

Results in what I was hoping for:

Sat Feb 22 16:00:00 CET 2014

Upvotes: 1

Related Questions