Question:How do I store, and then show the proper times at different time zones with php and SQL?
Solution:Working with timestamps and time zones can be initially confusing with PHP. A common, but misguided solution when storing times in a database involves offseting a time by a number of hours, depending on what time zone you are in. This will lead to confusion later.
The best way to go about times and time zones is to use Unix time stamps. These are always measured in time passed since midnight, January 1, 1970 GMT, regardless of where you are in the world.
For instance, a time stamp of 1335092594 will be Sun, 22 Apr 2012 04:03:14 -0700 in PST (e.g. Seattle, WA), Sun, 22 Apr 2012 11:03:14 +0000 in UTC (e.g. Grenwich, United Kingdom), and Sun, 22 Apr 2012 21:03:14 +1000 in Sydney, Australia.
- To get the current time with PHP, you can use the time() function
- To get the date of your server, use date()
- To get the date at UTC, use gmdate()
- To change timezones in order to render that zone's local time, use date_default_timezone_set() followed by date()
$timestamp_now = time();
echo ' My Server Time: ' . date('r', $timestamp_now) . PHP_EOL;
echo ' UTC: ' . gmdate('r', $timestamp_now) . PHP_EOL;
echo 'Sydney, Australia: ' . date('r', $timestamp_now) . PHP_EOL;
This should yield (at a time stamp of 1335092594):
My Server Time: Sun, 22 Apr 2012 04:03:14 -0700
UTC: Sun, 22 Apr 2012 11:03:14 +0000
Sydney, Australia: Sun, 22 Apr 2012 21:03:14 +1000
Using SQL, you'll want to simply save this time stamp whenever you want to record a time, regardless of where in the world you are. A caveat is that you will want to make sure that your server is set to the correct time zone so it records the proper time stamp to begin with.
Update:Just found a really good blog post related to this topic (PHP time and SQL). Enjoy :)