Best practice storing PHP time zones supported in mysql database

Question

I'm wondering what is best for storing PHP "supported time zones . " I save every time zone of the user, so I can do conversions to / from UTC in my local time. Would you save it as a string in a field of type varchar? In this case, the longest supported timezone line? Is there a more efficient storage practice? Simply saving the time zone offset is not an option, as it does not automatically take DST into account, as the PHP datetime object does.

Answer 1

This is an almost exact duplicate of this question: The correct way to store the time zone in a database?

But I will consider a few other points that you mentioned:

• PHP Time Zone IANA Time Zone .

• Just save the name in varchar . The following is a discussion about the length of the time zone name zone. varchar(32) will work, but to be safe, I would leave an extra room for future changes. 255 will probably be redundant, but varchar(50) may be reasonable.

• Many recommend storing UTC in a database. This is sometimes good practice, but I don’t like it when recommended, like what 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 you will need to convert it to and from local time at the input and output.

• If you use local time, you should always keep the time zone offset in addition to local time. This is due to potentially controversial local times during daylight saving time. Some databases are of type only for this, for example TIMESTAMP WITH TIMEZONE in Oracle or Postgres or DATETIMEOFFSET in SQL Sever. Unfortunately, MySql does not have this type, so you will need two columns.

• If your data has just been recorded once and never changes (for example, the recorded time of an event), then any of these options is viable. The advantage of UTC is that it is ready for math and conversions. The advantage of preserving local time is to preserve the perspective of the observer. See DateTime vs DateTimeOffset for .Net, but concepts still apply here.

• If you are going to edit these times, you will need the name of the time zone anyway so that you can save it. You can decide whether it makes sense to store it for each recorded timestamp or only once per user. You should think about what you want if the user changes his time zone. Should it be applied everywhere? Or just for newly recorded recordings?

There are several practical reasons why I can think of not storing in UTC:

• If you run a report in which many hundreds or thousands of lines will be displayed, and the desired result is recorded in local time, then when converting from UTC repeatedly, the report execution time may be slowed down repeatedly in a narrow cycle.

• Sometimes there is a legal requirement that time be stored exactly as it is presented. The non-technical auditor may not allow any changes and will see UTC time as illegal. If they looked at the local time plus-offset value, they probably don't care about part of the offset and will be satisfied with the local time value. I know this seems silly, but there are industries and jurisdictions where these requirements exist.

• Sometimes you don’t want to instantly refer to one moment in time, but specifically to a localized representation of that time. Let's say you have restaurants around the US that open at 6:00. It’s good that 6:00 in the morning is a different moment in Pacific time than in Eastern time. Saving this data in UTC format may lead to unacceptable assumptions, especially regarding DST changes.

In most other cases, I recommend storing in UTC. But you will need to decide what works best for your specific requirements.

Answer 2

store all the time as UTC, then create another field with a time zone offset.

This is really not a PHP problem, but the best database methods for using time zones. Always save the data in UTC format. You have another field for the TZ offset, for example. -10 or as a time zone, for example. 'Europe / Amsterdam. For me, I am using pytz in python to solve this problem.

You can always restore DST.

Answer 3

I always store dates and times as unix timestamps in an integer field. That way, I know exactly what I want to get from my database. I would recommend storing the time zone in a text field using string representations in this list , which you already know about.

For example, such a table is called stackoverflow, the database also calls stackoverflow: -

 +--+----------+-------------+ |id|date_time |time_zone | +--+----------+-------------+ |1 |1373212914|Europe/London| |2 |1373212914|Europe/Rome | +--+----------+-------------+ 

Then you moisten the DateTime objects like this: -

 $dsn = 'mysql:dbname=stackoverflow;host=127.0.0.1'; $user = 'stackoverflow'; $password = 'stackoverflow'; try { $dbh = new PDO($dsn, $user, $password); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { echo 'Connection failed: ' . $e->getMessage(); } $sql = 'select date_time, time_zone from stackoverflow'; $statement = $dbh->prepare($sql); $statement->execute(); $results = $statement->fetchAll(); foreach($results as $result){ $datetimes[] = (new \DateTime())->setTimestamp((int)$result['date_time'])->setTimezone(new \DateTimeZone($result['time_zone'])); } var_dump($datetimes); 

What gives this conclusion: -

 array (size=2) 0 => object(DateTime)[3] public 'date' => string '2013-07-07 17:01:54' (length=19) public 'timezone_type' => int 3 public 'timezone' => string 'Europe/London' (length=13) 1 => object(DateTime)[4] public 'date' => string '2013-07-07 18:01:54' (length=19) public 'timezone_type' => int 3 public 'timezone' => string 'Europe/Rome' (length=11) 

Checking the table shows that both records have the same time 2013-07-07 16:01:54 UTC , which corresponds to the value 2013-07-07 16:01:54 UTC , but displays correctly in the saved time zone when sending to the client.