26

I will be using MySQL and PHP and need to store the timezone in the DB. Spent a bit reading up on it, and it seems like I need to use something like America/Los_Angeles, and not PST, Pacific Standard Time, PST8PDT, +02.00, etc.

Do I store the whole America/Los_Angeles string, or is there some sort of integer ID for it such as https://stackoverflow.com/a/11580459/1032531 describes for c#? If the who string, what is the maximum number of characters?

Community
  • 1
  • 1
user1032531
  • 24,767
  • 68
  • 217
  • 387

2 Answers2

38

The timezone strings like America/Halifax and Asia/Kolkata refer to entries in the so-called zoneinfo data base. Read this. https://www.iana.org/time-zones It's presently maintained by the Internet Assigned Numbers Authority. It changes a lot, because its maintainer scrambles to keep up with changes to daylight saving rules and other temporo-political stuff.

On UNIX-like systems the entries are stored in a filesystem directory hierarchy, and not by some code number. That is a successful implementation of my suggestion. You can take a look to see how it works.

You asked:

Do I store the whole America/Los_Angeles string

Yes.

is there some sort of integer ID for it?

No, not if you want your application and data base to be future-proof. There is a time_zone_id in the part of MySQL that holds the zoneinfo data, but nobody has made any promises about keeping those numbers unchanged.

The longest string in the current (2021e, still current as of March 2022) data base is posix/America/Argentina/ComodRivadavia. It's 38 characters long. The mysql.time_zone_name table has a Name column defined with 64 characters. It makes sense to use VARCHAR(64) for storing this information; that matches the way the names are stored in the system. By the way, the names are all in 7-bit ASCII, so you can use the ascii_general_ci collation to define the column where you stash them. This should be a good definition for a user preference time zone column.

time_zone VARCHAR(64) 
          NOT NULL 
          DEFAULT 'UTC' 
          COLLATE ascii_general_ci

WordPress offers the user a picklist (drop down list) of available time zone names, and stores the user's choice as text. It's a great example to imitate.

http://www.iana.org/time-zones

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • 1
    it's a weird feeling in 2022 to stumble into an SO answer that gives me literally everything I wanted to know... down to the `VARCHAR(64)`. Kudos and muchas gracias – Nicholas Franceschina Mar 05 '22 at 03:38
9

Check here MySql DateTime

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store.

You should add a new column in your table to keep your timezone and when extract information convert your time zone.

Community
  • 1
  • 1
Laurentiu
  • 574
  • 6
  • 26
  • 1
    Please describe the "new column in your table to keep your timezone" you referenced. – user1032531 Nov 01 '15 at 17:46
  • 1
    if you need to know exactly what time zone was before your database convert your datetime you need to keep it, because Database automaticaly convert your datetime in server time zone and store it. – Laurentiu Nov 01 '15 at 17:50
  • I mean should I use `VARCHAR(32)` to store it? Will that be large enough for all timezones? Also, confirm that there isn't some integer ID for timezones. – user1032531 Nov 01 '15 at 17:54
  • Yes you should store it as string, check here http://stackoverflow.com/questions/8663590/data-type-to-use-in-mysql-for-timezones – Laurentiu Nov 01 '15 at 17:58
  • and about length yes, is it 32, here have a confirmation http://stackoverflow.com/questions/3477347/php-tz-setting-length – Laurentiu Nov 01 '15 at 17:59
  • 32 was just a wild guess :) – user1032531 Nov 01 '15 at 18:01
  • Careful! There's nothing magic about the length of 32. It just happens to be the length of the currently longest time zone name. – O. Jones Nov 01 '15 at 18:19
  • TIMESTAMP is terrible. DATETIME should be used, with the additional column for timezone as you suggest. Maybe don't quote that paragraph, it almost seems like you're supporting TIMESTAMP. – Matt Mc Nov 22 '19 at 23:54
  • Actually, the longest entry is now America/Argentina/ComodRivadavia at 38 chars. – quikchange Sep 23 '20 at 10:04