Geek Answers Handbook

Data type to use in mysql for time zones

I store the date / time in my database, this is UTC. I am working on adding conversions when the user selects the time zone, so he shows the local time for them. Basically, the user selects from the form:

<select name="DropDownTimezone" id="DropDownTimezone"> <option value="-12.0">(GMT -12:00) Eniwetok, Kwajalein</option> <option value="-11.0">(GMT -11:00) Midway Island, Samoa</option> <option value="-10.0">(GMT -10:00) Hawaii</option> ...

I can make the parameter value something that I want, but it is easy to remember, as indicated above. What data type will i use for mysql? Does it really matter in this case? Would make a TINYINT? Maybe using ENUM would be a good idea?

Any suggestions would be appreciated.

+3
timezone mysql
source share
1 answer

The scheme you propose (saving time zones as an integer offset from GMT) may be unsuccessful for the following cases:

  • India, which is located at UTC + 05:30 (not an integer number of hours).
  • Kiribati, which is located at UTC + 14:00 (more than 12 hours).
  • The difference between British time and GMT. (The first uses daylight saving time, and the second does not.)
  • The difference is between some pairs of countries that use the same GMT offset, but which switch at different times of the year.

To support the full time zone, I recommend using the zoneinfo database and storing time zones as strings (for example, "America / New York").

+5
source share

More articles:


All Articles