Dealing with multiple timezones in PHP/Mysql

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.

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?

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?

Thanks

>>before any script load
Just so we are clear, if currently your server is configured as:
 

 default-time-zone='America/Eastern'

Select allOpen 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.

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.

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

Thanks

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.