1

I need to store multiple user details from different country. How can I store the dates in DB (in UTC format) and how can I show the dates to the users accorning to their time zones. I am using Java and mySQL.

Siva Pasupathi
  • 494
  • 1
  • 3
  • 15
  • 1
    is there any solution using Java 8? – Siva Pasupathi Nov 11 '16 at 06:52
  • Please take a look on http://stackoverflow.com/questions/12487125/java-how-do-you-convert-a-utc-timestamp-to-local-time – Anptk Nov 11 '16 at 07:05
  • To convert time between timezones [JodaTime](http://stackoverflow.com/questions/19002978/in-joda-time-how-to-convert-time-zone-without-changing-time) is your best bet. – AleSod Nov 11 '16 at 09:01
  • @Thiefster If the op is using Java 8 there is probably no need for JodaTime. – assylias Nov 11 '16 at 09:16
  • @SivaPasupathi Please search Stack Overflow before posting. This has been handled in hundreds if not thousands of existing Questions and Answers. – Basil Bourque Nov 11 '16 at 19:38
  • 1
    Suggest we reopen this question. I believe the persons voting to close it assumed that it was all about Java calendar and time data types, whereas the OP seemed satisfied with an answer as simple as the question that addressed the DBMS. – O. Jones Nov 14 '16 at 14:13

1 Answers1

0

In MySQL, use the TIMESTAMP data type in your tables.

For each user, store a timezone column, VARCHAR(64) is a good data type for that column. When a user registers to use your system, ask for the time zone value. Mine is America/New_York. Yours might or might not be Asia/Kolkata. For a user interface for this user-preference setting, the WordPress.org software has a good example.

Finally, whenever you establish a connection from your Java program to your DBMS in behalf of a user, issue the SQL command 

 SET SESSION time_zone='(whatever tz string the user gave you)'

before you handle any user data.

This will cause all times going in to your tables to be converted to UTC, and all times coming out to be translated to local. It works properly for NOW() and CURDATE(). Again, you must use TIMESTAMP and not DATETIME or DATE data types for this.

Make sure your server OS and default MySQL time zones are set to UTC. If you don't do this before you start loading information into your database, it will be almost impossible to fix. If you use a vendor to run MySQL, insist they get this right and fire the vendor if they don't have it right.

O. Jones
  • 103,626
  • 17
  • 118
  • 172