Fixed (MySQL) time converted to client's time

Question

I've got a list of times on a php page retrieved from a sql database.

All I'd like to do is have the times change according to the offset between the client and server's times no matter where the client is in the world.

So a client's position in the world will affect what times he sees.

Answer 1

If you want to display times in the timezone of the user, you need to know in which timezone she is in. For example via an option/preferences she sets.

That time is often called "local time".

Then you need to know in which timezone you've stored the values inside the database. Often UTC is chosen for that, which then is "zulu time".

When retrieving the times from the database (zulu) you convert the timezone according the users preferences (local). And that's it.

See also

• How do you change the timezone in PHP for an existing timestamp?
• Working with timezone in PHP & MySQL
• Timezones and Localisation

Answer 2

1. Use the Geo IP module to identify your client timezone with his IP (geoip_record_by_name() then geoip_time_zone_by_country_and_region()).
2. Use date_default_timezone_set() to set the timezone of your PHP script. It'll make the date functions translate to the correct timezone, but you need to get your date as UNIX TIMESTAMP for MySQL (this is done easy with SELECT UNIX_TIMESTAMP(date) FROM table).
3. Optionally, you can have a look at MySQL's TIMESTAMP type, and tell him to do the conversion on the fly (execute SET time_zone = [timezone] before querying). This requires some server configuration though.