[MySQL] Adjusting for timezones and DST

Question

On my website, users can set preferences for their current timezone. I have a bunch of datetimes in my database, but I want to show each time appropriately, given A) what timezone they are in, and B) whether or not Daylight Savings is in effect.

What is the best way to go about this?

Answer 1

We use a date formatting method/function that adjusts for the user's timezone. Date/times are stored in our local timezone (PST) and converted appropriately.

I've seen other systems that store everything in GMT using unix timestamps. You will still need to convert it to the user's timezone.

Answer 2

This is a several-step process.

1. Allow the users to choose their timezone from the Olson timezone database. It's probably already installed on your machine if you're on Linux or other Unices.

2. Store all dates and times as UTC. No exceptions.

3. When the user requests a page, load it up and whenever you need to format a time, use the user's timezone preferences and use timezone-aware formatting functions. In PHP, it seems like the best way to do this is to set the timezone for the whole script. In Python, you can convert datetime objects between timezones freely. This is what I used when I customized PunBB to display timezones properly (I've since moved on from there...)

   /**
    * Sets up the timezone system. $timezone is the timezone. In PunBB, this is
    * called by setup_dates($pun_user['timezone']) in common.php.
    */
   function setup_dates($timezone) {
       if(function_exists('date_default_timezone_set')) {
           date_default_timezone_set($timezone) ;
       } else {
           putenv('TZ='.$timezone) ;
       }
   }
   

   In Python, use the pytz library, which should also already be installed on your server (again, assuming *nix). If it's not, ask your sysadmin to install it (distributions usually customize it to work off of the builtin timezone database), or alternately just install it using easy_install. pytz provides great usage examples. You'll want to use the astimezone method:

   >>> utc_dt = datetime(2002, 10, 27, 6, 0, 0, tzinfo=utc)
   >>> loc_dt = utc_dt.astimezone(eastern)
   >>> loc_dt.strftime(fmt)
   '2002-10-27 01:00:00 EST-0500'
   

   Do this each time you format a time for users to read.

Answer 3

MySQL stores its native date/time types as UTC on the server and converts them to other timezones on a per-connection basis. If a connection does not specify a timezone it tries to use the servers time. You can read more about this in the documentation. But you should be able to do:

SET time_zone = timezone;

to the client timezone at the start of the query or if you do per request connections.

Answer 4

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: