Some problems with time zones in PHP have long been in my mind, and I was wondering if they have better ways to handle this than what I'm doing right now.
All problems are related to changing the database retention date:
When working with a site that must support multiple time zones (for users) in order to normalize the timeout of the timeline of stored timestamps, I always store it in the server’s time zone using the CURRENT_TIMESTAMP attribute or the NOW() function.
This way, I don’t have to consider what time interval was set for PHP when the timestamp was introduced (since PHP time functions have time zone information). For each user, according to his preferences, I set the time zone somewhere in my boot file using:
date_default_timezone_set($timezone);
When I search for a date format using the php date() function, some form of conversion should happen, since MySQL currently stores the timestamp in Ymd H:i:s format. Ignoring the time zone, you can simply run:
$date = date($format,strtotime($dbTimestamp));
The problem is that date() and strtotime() are both time-dependent functions, which means that if PHP's time zone is set differently than the server’s time zone, the time zone offset will be applied twice (instead).
To handle this, I usually retrieve MySQL timestamps using the UNIX_TIMESTAMP() function, which has no timezone information, which allows me to apply date() directly to it, thereby applying only the time offset.
I don’t like this “hack” because I can no longer retrieve these columns, as usual, or use * to retrieve all columns (sometimes this simplifies queries). Also, sometimes it's just not an option to use UNIX_TIMESTAMP() (especially when used with open source packages without significant abstraction for query composition).
Another problem is saving the timestamp when using CURRENT_TIMESTAMP or NOW() not an option - saving the created PHP timestamp will save it with a timezone offset that I would like to avoid.
I probably lack something really elementary here, but so far I have not been able to find a common solution to solve these problems, so I have to consider them in each case. Your thoughts are very welcome