0

I have a question about storing time for users in different time zones. My wording may get a little coiled up, but please bear with me. If I just store the time a user accesses my web app in UTC into MySql, then is it okay for me to disregard different time zones?

For example, say there are two users living across the world. One says for both to chat in the web app at 4:00 his time. So then the other person has to access maybe at 1:00 her time so that both can successfully meet up. If I call UTC_Time() in Mysql when each of them logs onto the web app in their respective time, will I get one universal 4:00 for both of them? Thank you very much.

user2921187
  • 52
  • 8
  • 1
    The best standard to follow is to always have your system time and mysql time set to UTC. Always store UTC times in the database. Most people do not use mysql functions to convert for user timezone - rather, do this in your application code. You retrieve a timestamp as UTC from the database, then convert to the user's local timezone in the application before outputting it in the response. –  Jul 13 '14 at 02:46

1 Answers1

0

You've landed in one of the more complicated areas of programming.

If the time you store into you database is derived from 'now()' in MySQL, then you will store the event at a correct moment in time, and you use the UTC scale to measure it. Which, for international apps is probably the right thing to do.

If one person just says '4:00' this number will not change, unless you provide a form on your page to enter the time, and which can then be used for calculations. In that case, you can ask each user for its time zone (eg. once, on registration), and calculate UTC to save '4:00' in, say '11:00 UTC'. If the other user logs in, you can calculate his local time from the UTC stored.

By the way, if you use Linux, you can run your system on UTC instead of localtime (it will still show local time, but the internal clock runs on UTC). DO NOT do this if your system is Windows or dual-boot. Windows does not like systems running on different timezones.

From the MySQL manual:

CONVERT_TZ() converts a datetime value dt from time zone given by from_tz to the time zone given by to_tz and returns the resulting value

I.e. something like:

SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
        -> '2004-01-01 13:00:00'

In case you stored times/dates as GMT, and your friend registered and selected 'MET'

jcoppens
  • 5,306
  • 6
  • 27
  • 47
  • thank you for the response. Is there a MySql function that returns the time of each user's localtime? For example, when two users log on at 4:00 and 11:00 in each respective time, the function for each user should give me 4:00 and 11:00 respectively. Thanks again! – user2921187 Jul 13 '14 at 16:19
  • I've edited the reply, so that the information stays together. Also, have a look at the excellent documentation of MySQL. – jcoppens Jul 14 '14 at 04:20