CODEWITHSUNDEEP
mysqltimezone
Tom
Tom

Reputation: 2634

data type to use in mysql for timezones

I store a date/time in my database that is UTC. I'm working to add conversion of this once a user selects a timezone so it shows local time for them. Basically the user selects from a 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 option value anything I want but an easy to remember one is as above. What data type would I use for mysql? Does it really matter in this case? Would TINYINT do? Maybe using an ENUM would be a good idea?

Any suggestions would be appreciated.

Upvotes: 4

Views: 6565

Answers (2)

KManish
KManish

Reputation: 1861

Although this is very old. But for anybody still looking for the solution. I used date-fns with time zones. I am using reactjs with node.js . Only had to add one extra column to the table - and always store time in UTC format only - and the corresponding zone in the additional column.

https://date-fns.org/v2.23.0/docs/Time-Zones

import { zonedTimeToUtc } from 'date-fns-tz'

const date = getDatePickerValue() // e.g. 2014-06-25 10:00:00 (picked in any time zone)
const timeZone = getTimeZoneValue() // e.g. America/Los_Angeles

const utcDate = zonedTimeToUtc(date, timeZone) // In June 10am in Los Angeles is 5pm UTC

postToServer(utcDate.toISOString(), timeZone) // post 2014-06-25T17:00:00.000Z, America/Los_Angeles

And for the scenario of timezones - list. https://www.npmjs.com/package/countries-and-timezones

Upvotes: 0

user149341
user149341

Reputation:

The scheme you're proposing (storing time zones as an integer offset from GMT) may fail for the following cases:

  • India, which is on UTC + 05:30 (not an integer number of hours).
  • Kiribati, which is on UTC + 14:00 (over 12 hours).
  • Distinguishing British Time and GMT. (The former uses Daylight Saving Time; the latter is not.)
  • Distinguishing between some pairs of countries which use the same GMT offset, but which switch at different times of the year.

For full time zone support, I'd recommend using the zoneinfo database and storing time zones as strings (e.g, "America/New_York").

Upvotes: 9

Related Questions