MySQL Handling Time Zones
Remarks
When you need to handle time information for a worldwide user base in MySQL, use the TIMESTAMP data type in your tables.
For each user, store a user-preference 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 Atlantic Time, America/Edmonton. Yours might or might not be Asia/Kolkata or Australia/NSW. 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 host program (Java, php, whatever) to your DBMS on 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 involving times. Then all the TIMESTAMP times you have install will render in the user's local time.
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.
