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...
MarkProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hieloCommented:
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.
MarkProgrammerAuthor Commented:
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?
hieloCommented:
In the ideal case, that user in California will never leave California.  So, when you record his/her timestamps on the db, they will be California timestamps.  The more realistic case is that users will change other timezones.  Then what?  You will now have to detect a change in the users timezone, and for all his/her pre-existing records convert the timestamps from the old timestamp to the new.  Ultimately, timezone conversion is something you won't be able to avoid.

What I am suggesting is that you always keep the timestamps on the DB constant.  Then the users will specify their timezone as part of their settings.  When you query their records, since you know the timestamps on the DB is always the same (UTC), you would just need to do conversion on the fly.  So intead of:

"select lastUpdate FROM table"

you would use:
// assume that $timezone below ends up with 'US/Pacific'.  If the app requires user authentication,
$timezone = getuserTimezone();//this emits a query to for the user's saved preferences/settings

"select CONVERT_TZ( lastUpdate,'UTC','$timezone') as lastUpdate"

But once the user moves or changes his/her preferred timezone to 'US/Eastern', there's nothing else for you to do!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

MarkProgrammerAuthor Commented:
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?
MarkProgrammerAuthor Commented:
Thanks
hieloCommented:
>>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.
MarkProgrammerAuthor Commented:
Ok, thanks.
Ray PaseurCommented:
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.
MarkProgrammerAuthor Commented:
I'm going to test this concept out tomorrow fully and if I have any questions--I will follow-up.

Thanks
MarkProgrammerAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.