Database datetime to java.util.Date conversion with timezone

Question

I have a task of converting, time stored on one of my DB(Sql Server)'s table's column to a specified timezone. The column always contains time in UTC timezone.

The problem I am facing is, when hibernate READS the column and sets it to my entity class, it sets the time in the application server's timezone.

For Eg: if DB has value - 07 Jul 2012 10:30 (which is actually UTC), the hibernate sets the mapped date field as 07 Jul 2012 10:30 PST (assuming the JVM is running at PST).

Now if this date gets converted to any other timezone.. say GMT+5:30, i get unexpected result

To fix the above issue... i wrote the following code

 //Reading the DB time (which does not have timezone info)
 Date dbDate = entityObj.getDBUtcDate();

 //Setting GMT timezone to the date, without modifying the date
 Calendar c = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
 c.set(dbDate.getYear(), dbDate.getMonth(), dbDate.getDate()..., dbDate.getMinutes());

 Date utcDate = c.getTime();

Using above code.. I could get the DB stored date back in UTC timezone, but when I did conversion to some other timezone(say GMT+5:30) using below logic

Calendar outCal = Calendar.getInstance(TimeZone.getTimeZone("GMT+5:30"));
outCal.setTimeInMillis(utcDate.getTime());

Calendar cal = Calendar.getInstance();
cal.set(Calendar.YEAR, outCal.get(Calendar.YEAR));
cal.set(Calendar.MONTH, outCal.get(Calendar.MONTH));
cal.set(Calendar.DAY_OF_MONTH, outCal.get(Calendar.DAY_OF_MONTH));
cal.set(Calendar.HOUR_OF_DAY, outCal.get(Calendar.HOUR_OF_DAY));                                              
cal.set(Calendar.MINUTE, outCal.get(Calendar.MINUTE));
cal.set(Calendar.SECOND, outCal.get(Calendar.SECOND));
cal.set(Calendar.MILLISECOND, outCal.get(Calendar.MILLISECOND));

//Converted date
Date pstTime = cal.getTime();
//Converted time mill seconds
long timeMilSec = pstTime.getTime();

The time millisecond of the converted date started coming as negative (-54672...), which seems to be representing an invalid time.

My question here is How can i restore the timezone information from DB (without having to have any extra column in DB to specifically store timezone information)?

OR

How can i convert a DB time into a time having a specified timezone(UTC)?

PS: I expect the output in the form of java.util.Date/ Calendar because i need to do one more conversion on this date

Please help me resolving this issue

JB Nizet · Accepted Answer · 2012-07-30 18:14:30Z

5

Dates in Java don't have a time zone. They're just a universal instant in time. If you want to display the date in a given time zone, then simply use a DateFormat initialized with this time zone:

DateFormat df = DateFormat.getInstance();
df.setTimeZone(TimeZone.getTimeZone("UTC"));
System.out.println("The date in the database, in the UTC time zone, is " 
                   + df.format(date));

You don't need to convert anything. The date format prints the appropriate values based on the universal instant it formats, and the time zone you tell it to use.

Similarly, if you want to know if the date is a monday or a tuesday, or if it's 1 o'clock or 2 o'clock, you need to first choose a time zone, convert it to Calendar, and ask the calendar for the information:

Calendar cal = Calendar.getInstance(someTimeZone);
cal.setTime(date);
System.out.println("The day for the date stored in the database, for the time zone "
                   + someTimeZone
                   + " is " + cal.get(Calendar.DATE));

Side note: don't use deprecated methods. They're deprecated for good reasons.

edited Jul 30, 2012 at 18:14

answered Jul 30, 2012 at 18:06

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

sanbhat Over a year ago

>@JB Nizet thank you for your quick reply. Regarding using DateFormat... the df.format(dbDate) >does the time conversion again which is not required at this point. The "dbDate" stored in DB is actually a date stored after converting to utc. When i ran the code... following were the output... >dbDate --> 2012-07-30 13:45:00.0 >df.format(dbDate) --> 7/30/12 8:15 AM

sanbhat Over a year ago

>what i wanted to achieve is to attach a timezone information to dbDate (currently dbDate doesn't have the timezone attached to it, hence the dbDate gets default JVM's timezone). So that I can use the same date to convert again to some other timezone (say PST)

JB Nizet Over a year ago

You haven't understood. Forget about all your conversions. There is no cenversion to make. A date represent a universal moment in time and doesn't have any time zone, and can't have one. It's only when you format the date that you need to choose with which timezone it must be formatted. But that doesn't change the date. Think of a date as "When Neill walked on the moon". If you format "When Neill walked on the moon", it can be midnight, or 1 o'clock, or 2 o'clock depending on which timezone you choose to format this moment. But they all represent the same moment, differently.

sanbhat Over a year ago

Now I understood your point.. I thought date object does have timezone attached to them. Thank you for making it clear...

sanbhat Over a year ago

The reason for posting the question was.. I had stored 30Jul2012 10:30AM GMT on db... when i retrieved it on a server running on IST.. it started showing as 30Jul2012 10:30AM.. now when i took this date and converted to PST(GMT-8:00) it created ~12hr difference.. which is wrong, because it was understood to be in IST (i thought)... if the same date would have been read as GMT... the diff would have been 8hr.. I think i am able to convey it properly..

Collectives™ on Stack Overflow

Database datetime to java.util.Date conversion with timezone

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related