Link to home
Avatar of Mark
Mark

asked on

Dealing with multiple timezones in PHP/Mysql

I have an application which will have users from different time zones. What is the best way to deal with this factoring in DST.

Is there perhaps a class to deal with this automatically?

Thanks...
Avatar of hielo
hielo
Flag of Wallis and Futuna image

MySQL has a function that can convert between timezones:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_convert-tz

You could save your timestamps in utc:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_utc-timestamp

and for every user, you would need to know his/her timezone setting/preference.  Then when you need to show his/her records, use the convert_tz() function to convert between the saved timezone and the user's timezone.
Avatar of Mark
Mark

ASKER

Can I just stored the user's timezone and set it upon every page load..

Example:

function set_locality($timezone='America/New_York') {
      define('TIMEZONE', $timezone);
      date_default_timezone_set($timezone);
      dbdo("SET time_zone = '{$timezone}';"); // executes mysql query
}

So for example, our home office is in New York but let's say I have a user in California.

Let's say my system is parsing emails for that user, before inserting any records, I would call set_locality('America/Los_Angeles')

Then when that user logs in, the system would call the same: set_locality('America/Los_Angeles')

Wouldn't that display records in the users timezone without me having to convert timestamps as said?
ASKER CERTIFIED SOLUTION
Avatar of hielo
hielo
Flag of Wallis and Futuna image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Mark

ASKER

Good thinking:

So should I call "SET time_zone = 'UTC'" before any script load and then use use CONVERT_TZ function everytime I am asking about a timestamp?
Avatar of Mark

ASKER

Thanks
>>before any script load
Just so we are clear, if currently your server is configured as:
 
 default-time-zone='America/Eastern'

Open in new window


if you were to execute the following immediately after connecting to the db:
SELECT NOW() as `mycurrenttimestamp`;

you will see the expected Eastern time.  However if you now disconnect from the db (to kill your session), reconnect, and then you were toimmediately execute:
SET time_zone='America/Pacific';
SELECT NOW() as `mycurrenttimestamp`;

you will no longer see an Eastern timestamp.  So, you most likely are interested in executing:
SET time_zone='America/Pacific';

as soon as you connect to the server.

>>and then use use CONVERT_TZ function everytime I am asking about a timestamp
Strictly speaking, you need to use CONVERT_TZ everytime you want a date which you originally saved in timezone A, but want it rendered in timezone B.  In your case I am just suggesting you keep timezone A constant, since the user has complete control of timezone B.
Avatar of Mark

ASKER

Ok, thanks.
PHP and MySQL typically use different timezones; the setting of one may not affect the other, and they may go "out of sync.".  You might want to consider posting a more detailed question with some test data so we can help you explore these ideas in a little more depth.
Avatar of Mark

ASKER

I'm going to test this concept out tomorrow fully and if I have any questions--I will follow-up.

Thanks
Avatar of Mark

ASKER

Quick question:

MySQL states that all versions after 5.4 store timestamps in UTC by default and display them in the timezone set.

If this is the case, do I ever have to use TZ Convert?

Can't I execute 'SET time_zone = 'America/Los_Angeles' after connecting to the database and then supply all update timestamps in 'America/Los_Angeles' offset and it would automatically store it in UTC so if say the user changes their timezone from Los_Angeles to New York, It won't be a problem since the timestamp was stored it UTC originally.

Please advise, thanks.