Best way store a date/time for different timezones

Question

What's the best way to store timezone information with dates/times in a uniform way so people can enter times from anywhere in the world in their own local time? Other users would then have the ability to view the times in their own local time and the original localtime on the web page.

Answer 1

My view has always been to store exact numeric timestamps (seconds or milliseconds since midnight 1st january 1970 GMT), as that's a format that is easily converted to an actual date and time in any timezone.

The downside of this is that the data isn't as immediately viewable through normal SQL tools. The mysql cli client does have methods for this though (FROM_UNIXTIMESTAMP).

Answer 2

Store the time data as GMT. Display it using local time. This requires a simple offset from GMT. Now if the politicians would leave the starting and end date for Day light savings time alone...

Answer 3

I agree with Staale. Just store the UNIX timestamp and output in local time, per user.

Answer 4

I'd agree with Staale, but add that times should be stored in Zulu time (commonly called UTC), to make for easier translation into other timezones. Don't rely on storing data in the timezone used by your server.

Answer 5

Always store as times as UTC/GMT. Check the mysql docs to see what column type is best for the range of dates and/or precision that you want to support.

On input, convert to UTC and store the client timezone offset in a second column. Then you can easily convert to any timezone you want for display, or use the submitted offset to recreate the original timestamp.

Answer 6

I would suggest inserting the date in UTC time zone. This will save you a lot of headache in the future (Daylight saving problems etc...)

"INSERT INTO abc_table (registrationtime) VALUES (UTC_TIMESTAMP())"

When I query my data I use the following PHP script

<?  while($row = mysql_fetch_array($registration)){ 

  $dt_obj = new DateTime($row['message_sent_timestamp']." UTC");
  $dt_obj->setTimezone(new DateTimeZone('Europe/Istanbul'));
  echo $formatted_date_long=date_format($dt_obj, 'Y-m-d H:i:s'); } ?>

You can replace the datetimezone value with one of the available php timezones here:

Answer 7

Would you suggest storing the UTC_TIMESTAMP as an int?