Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Storing timezone in a database

Tags:

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?

like image 621
user1032531 Avatar asked Nov 01 '15 17:11

user1032531


People also ask

Should you store timezone in database?

You'll need to store the offset and timezone name, because the timezone where the server is could change. The timezone could change to daylight savings time, which means your times in the database will be inconsistent (some will be in standard time, others in daylight savings time).

Does SQL save timezone?

SQL Server does not store time zone data when storing timestamps. It uses the host server time as the basis for generating the output of getdate() .

How can I save UTC time in SQL?

2 Answers. SELECT GETDATE() AS CurrentTime, GETUTCDATE() AS UTCTime. SELECT DATEADD(second, DATEDIFF(second, GETDATE(), GETUTCDATE()), YOUR_DATE);


1 Answers

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

like image 51
O. Jones Avatar answered Oct 02 '22 15:10

O. Jones