4

I have a table which has field with data type as DATETIME. I persist a record in this table using java and I calculate current date as as following.

Date date = Calendar.getInstance(TimeZone.getTimeZone("UTC")).getTime();

But When I check my table, it again converts this time into local timezone.

Can some one please explain to me, how to store date in UTC timezone in database.

@Code

I have a db entity 'Referral' corresponding to table with date member data type as java.util.Date and using hibernate to save it.

Referral ref = new Referral();
ref.setCreationDate(Calendar.getInstance(TimeZone.getTimeZone("UTC")).getTime());
referralDAO.save(ref);

Thanks

Jitendra

Dilum Ranatunga
  • 13,254
  • 3
  • 41
  • 52
RandomQuestion
  • 6,778
  • 17
  • 61
  • 97
  • Show us your code for retrieving and interpreting the DATETIME, that might be where things are going wrong. – jwd May 23 '11 at 16:02
  • Does database connection properties has got something to do with it? – RandomQuestion May 23 '11 at 16:12
  • In your code, all the timezone work you do is lost when you call calendarInstance.getTime(). Two questions: 1. Are you sure that the right value is not being stored? Try changing your machine's timezone. If the results are then presented in the new timezone, your data is correct, it is just a presentation issue. 2. What is your database schema -- are you using DateTime or Timestamp? See http://dev.mysql.com/doc/refman/5.1/en/datetime.html – Dilum Ranatunga May 23 '11 at 16:20
  • I am using DateTime in mysql. I am directly checking database records using mysql client. – RandomQuestion May 23 '11 at 16:44
  • @dilium, "In your code, all the timezone work you do is lost when you call calendarInstance.getTime().", then how could we retain time in UTC. – RandomQuestion May 23 '11 at 16:47
  • It seems like the problem lies in how the `Referral` class gets the information into the database. What is `Referral`? What does the documentation say for `setCreationDate`? As you have it written, you are passing a UTC time to it (as a `long`), but according to your investigation, it is getting converted to local time somewhere before it gets to MySql. Do you have control over the `Referral.setCreationDate` code? – jwd May 23 '11 at 18:31

2 Answers2

2

MySql DATETIME is stored in a time zone agnostic manner. It just stores year/month/day/hour/minute/second

How you interpret that information is up to you and your application. If you are storing it as UTC, then when you retrieve it you must make sure to interpret it as UTC as well.

You might be interested in this SO question: How can I get the current date and time in UTC or GMT in Java?

As mentioned in the answer to that question:

java.util.Date is always in UTC. What makes you think it's in local time? I suspect the problem is that you're displaying it via an instance of Calendar which uses the local timezone, or possibly using Date.toString() which also uses the local timezone.

Community
  • 1
  • 1
jwd
  • 10,837
  • 3
  • 43
  • 67
  • I am trying to store in UTC but It is getting stored in local timezone. – RandomQuestion May 23 '11 at 16:15
  • See @Dilum Ranatunga's comment - are you _sure_ it's not being stored in UTC, or is it just presentation? Try doing a SELECT from inside mysql's commandline interface, to remove all the layers of reinterpretation – jwd May 23 '11 at 16:24
  • @Jitendra - Have you looked into `Referral`, as I mentioned in a comment on the main question? – jwd May 23 '11 at 23:31
2

Date in Java is just a long. It is agnostic of any time zones. When you pass your Date/Timestamp to the database via JDBC driver, it interprets it as time in the session time zone which is your JVM time zone.

Some databases, like Oracle and PostgreSQL, have data type TIMESTAMP WITH TIMEZONE. I don't believe MySQL has it. You can either switch your JVM time zone to UTC or use a string field and format your Date with SimpleDateFormat.

Olaf
  • 6,249
  • 1
  • 19
  • 37