Best practice concerning storage of PHP supported timezones in a mysql database

Question

I am wondering what the best practice is for storage of PHP's 'supported timezones' is. I am storing each user's timezone so I can do conversions to/from UTC to their local time. Would you store it as a string in a varchar type field? In this case what is the longest possible supported timezone string? Is there a better practice for storing these? Simply storing timezone offset is not an option as it does not take into account DST automatically like PHP's datetime object does.

Answer 1

This is almost an exact duplicate of this question: Proper way to store a timezone in a database?

But I will address the few other points you mentioned:

• PHP time zones are IANA time zones.

• Just store the name in a varchar. There is some discussion here about field length of time zone names. varchar(32) would work, but just to be on the safe side I would leave some extra room for future changes. 255 is probably overkill, but perhaps varchar(50) would be reasonable.

• A lot of folks recommend storing UTC in the database. This is sometimes a good practice, but I don't like it when recommended as something you should always do. There are good reasons to use UTC and there are good reasons to use local time.

• If you use UTC, you will need the time zone name and will have to convert to and from local time on input and output.

• If you use local time, you should always store the time zone offset in addition to the local time. This is because of potentially ambiguous local times during DST transitions. Some databases have a type just for this, such as TIMESTAMP WITH TIMEZONE in Oracle or Postgres or DATETIMEOFFSET in SQL Sever. Unfortunately, MySql doesn't have a type for this, so you will need two columns.

• If your data is just recorded once and never changed (such as a recorded event time), then either of these options is viable. UTC has the advantage of being ready for math and conversions. Local time has the the advantage of preserving the perspective of the observer. See DateTime vs DateTimeOffset which is about .Net, but the concepts still apply here.

• If you are going to be editing these times, then you're going to need the time zone name either way, so you might as well store it. You can decide whether it makes sense to store it per recorded timestamp, or just once per user. You should think about what you would want to happen if the user changes their time zone. Should it apply everywhere? Or only to newly recorded entries?

There are some practical reasons I can think of to not store as UTC:

• If you are running a report that will output many hundreds or thousands of rows, and the desired output is in the local time as recorded, then having to convert from UTC many times repeatedly in a tight loop may slow down the execution time of the report.

• Sometimes there is a legal requirement that the time is stored precisely as it is presented. A non-technical auditor might not allow for any type of conversions and would see UTC times as unlawful. If they looked at a local-time-plus-offset value, they would probably not care about the offset portion, and would be satisfied with the local time value. I know this seems silly, but there are industries and jurisdictions where these requirements do exist.

• Sometimes you don't want to actually refer to a single instant in time, but specifically to a localized representation of that time. Say you have restaurants around the USA that all open at 6:00 AM. Well, that 6:00 AM is a different moment in Pacific time than it is in Eastern time. Storing these as UTC could lead to invalid assumptions, especially around DST changes.

In most other cases, I recommend storing as UTC. But you will have to decide what works best for your particular requirements.