Time zones

[kzn]

Hi I have a question regarding time zones and best to make it work. Basically I am creating a website that will capture registration dates for subscription purposes from all around the world. So therefore when the user logs in they can see the date they registered, if my mysql statement uses now(), that will be capturing the time and date but the server time.

What is the best way to manage this, should I use / set up something with mysql timezones or should I manipulate everything through php. example use the now() insert and then add or subtract the time according to the location of person and then insert into the database?

Any thoughts appreciated.

Many thanks,

 

[jpadie]

The browser knows which timezone it is in. Provide it with utc and let JavaScript make a local conversion.
The only alternative is to let the user tell you what tz he wants to use.
IMO always store times and dates in MySQL (etc) in utc.

 

[kzn]

Hi Japadie

Thanks for the reply, so would I use something like getTimezoneOffset() then either add or subtract when I do the entry into mysql using UTC?

Many thanks,

 

[jpadie]

in keeping everything at UTC server side, you need to change php.ini and the mysql startup parameters.

typically mysql is set to use the system time zone. and typically servers use UTC as system time. so nothing need be changed in this case (at the system level).

the alternative to changing php.ini is to make sure that the time zone is expressly set in each script.

date_default_timezone_set('UTC');

to determine the current mysql time zone do this from a mysql client

SELECT @@global.time_zone, @@session.time_zone;

once you have your various systems talking the same language, just make sure that your php script knows what timezone the client is talking. either you store this in a cookie or some other parameter. Then use the datetime class (or strtotime function or whatever you like) to convert from a provided time to a unix timestamp and then back to whatever datatype you use for your mysql storage.

or if you are using a pure javascript solution, just provide a utc timestamp and let the browser convert.