11

I am currently studying the best way to handle timezones on my website. People from many different countries will access it simultaneously, and I have to show them time-based information, so I thought:

Store every time on database according to my server (same timezone, defined by PHP) Then, the user has the option to choose his timezone, and I do the needed conversions by using mysql function DATEADD.

This seems to work fine, but my questions are:

  • Is this the best way?
  • Is DATEADD the most efficient function to handle the hour difference?

Thanks.

Renato Massaro
  • 544
  • 1
  • 8
  • 18
  • 2
    did you check http://stackoverflow.com/questions/518296/best-way-to-handle-storing-displaying-dates-in-different-timezones-in-php?rq=1 – Deepak Aug 31 '12 at 03:37

3 Answers3

7

As described in MySQL Server Time Zone Support:

The current session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

Therefore, if you use TIMESTAMP type columns, MySQL will handle timezone conversion for you automatically: just set the appropriate timezone for the session in its time_zone variable.

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • Lets suppose I insert user data using NOW(). Will it store on his timezone? If not, seems the best way. – Renato Massaro Aug 31 '12 at 04:25
  • 2
    @RenatoSiqueiraMassaro: If you store `NOW()` into a `TIMESTAMP` column, then MySQL will hold there the current time in UTC. When you subsequently retrieve the value of that column, it will be converted to the timezone of the then-current session. For example, if my session is in `UTC+05:00` and the time now is `09:30` in my local timezone, `04:30` will be stored and `09:30` will be retrieved when I fetch the data. – eggyal Aug 31 '12 at 04:30
  • Thanks. Thats exactly what I need, and seems to be very simple. – Renato Massaro Aug 31 '12 at 04:35
  • just set the appropriate timezone for the session in its time_zone variable. how to do this in php @eggyal – Shahid Karimi Jul 13 '15 at 12:48
  • 1
    @Kannu: issue a `SET time_zone = ...` query, in the normal way. – eggyal Jul 15 '15 at 12:36
2

You are thinking in the right direction.

I would not use the server's timezone. Instead, use Coordinated Universal Time (UTC) time. It is the World Time Standard. This is pretty much the same as Greenwich Mean Time (GMT). Note that UTC does not change with Daylight Savings Time.

TO use in PHP see: http://php.net/manual/en/function.gmdate.php

From here, you can either add hours via: http://www.php.net/manual/en/datetime.add.php

Or set the timezone based on the users preference: http://www.php.net/manual/en/datetime.settimezone.php

The one you use is based on how you get the user's timezone. If you ask them for it (most accurate) you can set the timezone in PHP with the user selecting from a combo box. If you get it from the header with JavaScript using getTimezoneOffset(); then it is best to add hours based on the timezone offset.

Todd Moses
  • 10,969
  • 10
  • 47
  • 65
0

I personally set all the times in my DB according to the GMT +0.00 timezone. So I use UTC_TIMESTAMP() (or UTC_DATE(),UTC_TIME() - whichever applies) when I want to add the current time, for example. This is server independent so I'm confident that even if I change my server I will not need to worry about this issue in the future.

Then the options are, if your visitors have a chance to pick their own timezones, you can use the DATE_ADD() and DATE_SUB() functions to format the result before providing results.

Otherwise if you have the chance (this is my favorite solution) you can use Javascript to format that date/time, which you can make it handle easily by something like 

function getLocalDate(dt) {
    var d = new Date(0);
    d.setUTCSeconds(dt);
    return d.toLocaleDateString();
    // or in some format that you choose
}

which gets the date echoed by PHP using strtotime($row['some_date']);.

inhan
  • 7,394
  • 2
  • 24
  • 35