Saving the time zone in the database

Question

I will use MySQL and PHP and you will need to save the time zone in the database. Spend a little on it, and it seems to me that I need to use something like America/Los_Angeles , and not PST , Pacific Standard Time , PST8PDT , +02.00 , etc.

Am I America/Los_Angeles whole line of America/Los_Angeles , or is there some kind of integer identifier for it, for example, https://stackoverflow.com/a/167188/ for C #? If which string, what is the maximum number of characters?

Answer 1

Time zone strings such as America/Halifax and Asia/Kolkata refer to entries in the so-called zoneinfo database. Read it. https://www.iana.org/time-zones. It is currently supported by the Internet Office of Assigned Numbers. He is changing a lot, because his companion is trying to keep up with changes in the rules for daylight saving and other political issues.

On UNIX-like systems, entries are stored in the file system directory hierarchy, and not by any code number. This is a successful implementation of my proposal. You can see how it works.

You asked:

Keep a whole row of America/Los_Angeles

Yes.

is there any integer id for it?

Not if you want your application and database to be future-oriented. In the MySQL part, there is time_zone_id that contains the zoneinfo data, but no one made any promises about keeping these numbers intact.

The longest row in the current database (2019b) is America/Argentina/ComodRivadavia . These are 32 characters in length. The mysql.time_zone_name table has a Name column defined with 64 characters. It makes sense to use VARCHAR(64) to store this information; this corresponds to how the names are stored in the system.

By the way, WordPress offers the user a selection list (drop-down list) of available time zone names and saves the user's choice in the form of text. This is a great role model.

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

Answer 2

Check here for 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 happen for other types, such as DATETIME.) By default, the current time zone for each connection is server time. A time zone can be set for each connection separately. As long as the time zone setting remains constant, you get the same value that you store.

You must add a new column to your table to save your time zone, and convert your time zone when extracting information.