All of the issues revolve around reformating database stored dates:
When dealing with a site that has to support multiple timezones (for users), to normalize the timezone offest of stored timestamps I always store it with the server timezone using the CURRENT_TIMESTAMP attribute or the NOW() function. This way I don't have to consider what timezone was set for PHP when the timestamp was entered (since PHP time functions are timezone aware). For each user, according to his preference I set the timezone somewhere in my bootstrap using:
Code: Select all
date_default_timezone_set($timezone);Code: Select all
$date = date($format,strtotime($dbTimestamp));To deal with this, I usually retrieve MySQL timestamp using the UNIX_TIMESTAMP() function which is not timezone aware, allowing my to apply date() directly on it - thereby applying the timezone offset only once.
I don't really like this 'hack' as I can no longer retrieve those columns as I normally would, or use '*' to fetch all columns (sometimes it simplifies queries greatly). Also, sometimes it's simply not an option to use UNIX_TIMESTAMP() (especially when using with open-source packages without much abstraction for query composition).
Another issue is when storing the timestamp, when usage of CURRENT_TIMESTAMP or NOW() is not an option - storing a PHP generated timestamp will store it with the timezone offset which I would like to avoid.
I'm probably missing something really basic here, but so far I haven't been able to come up with a generic solution to handle those issues so I'm forced to treat them case-by-case. Your thoughts are very welcome