changing timezone of mysql

Question

I am using the CURRENT_TIMESTAMP for inserting records in database and I want to save the time in users timezone?

Thanks

Answer 1

Are you referring to multiple users with different time zones? If so, storing timestamps in for many different timezones is generally a bad idea. It's usually better to store timestamps as a single time zone (GMT or UTC, for example), then convert the time zone for display purposes. By doing it this way, you are able to more easily perform calculations, run reports, and manage your timestamps.

Answer 2

There are several basic approaches:

1. Use only local time
2. Use a single timezone for all users
3. Allow each user to customize a configuration parameter for a display timezone

Using only local time is an approach taken by devices which are not intended to inter-operate with other devices: simple programmable thermostats, wall clocks, and MSDOS.

At the other extreme is highly interactive systems, such as Unix and Linux, WANs, Wikipedia, blogs, etc., etc. This is the vast majority of distributed applications; they overwhelmingly use a standard timezone, usually UTC (note that GMT is no longer widely used). Users are usually tasked with converting between the standard timezone and what they find useful.

Another approach is to store the time with a standard timezone, but allow the user to customize the timezone for automatic translation. This approach is taken by all modern operating systems and some online applications, like airline reservation systems, etc.

To do that, every query which accessed a date or time would have to also retrieve the user's configured timezone and apply it.