0
\$\begingroup\$

I have a web application which is being used by users from different parts of the world. In order for users of the site, to be able to view the timestamps in their local time, I've a settings page set up which will allow the user to store their preferred timezone (Europe/Berlin, America/Chicago) etc. When a user is authenticated successfully, value fetches from the DB and stores it in a session.

From what I've read so far, the best practice seems to be to store the datetime value in UTC format when a new record is created in a database and while displaying it, convert it to the user's timezone. So to achieve the following, this is what I'm doing:

if($this->userIsAuthenticated())
{
   $this->session->timezone = "Europe/Berlin"; // this value is coming from db
}

// inserting records
date_default_timezone_set("UTC");
$data = array("created_time"=>date("Y-m-d H:i:s"));
$this->db->insert("my_table", $data);

// fetching records
$date_from_db = $row->created_time;
$display_date = new DateTime($date_from_db , new DateTimeZone('UTC'));
$display_date->setTimeZone(new DateTimeZone($this->session->timezone));
return $display_date->format("m/d/Y h:i a");

So the workflow is as follows:

  1. Use date_default_timezone_set() for every insert query

  2. Pull the value from the db and set it to UTC using DateTime class

  3. Then set the timzeone to user's timezone

This solution works. But, is this best way of doing it? Or, are there any more modular ways of achieving the same result?

\$\endgroup\$
1
\$\begingroup\$

Some thoughts:

  • Consider using the timestamp-generation functionality of your database for record insert use cases. This way the database holds authority over the timezone used for the application, meaning it is not subject to misconfiguration in the application layer, as it will always use a consistent time zone. This also gets you out of the business of setting timezones in your application for record creation concerns and potentially even helps you promote immutability of key record timestamps if you simply don't allow you application code to write these fields (i.e. these fields are read-only in your data model). Trying to deal with setting timezones for every insert your application makes to the database seems like a bunch of unnecessary code that will make its way into your codebase.
  • It is likely that your application should be dealing with the issue of setting a display timezone early in the application boostrapping process and not in various places in your application code.

So perhaps something like this in application bootstrapping file:

$timezone = date_default_timezone_get();
if ( /* timezone is set in session */) {
    $timezone = /* timezone from session */;
}
// use the following throughout your application
$displayTimeZone = new DateTimeZone($timezone);
  • Your code example is a little incomplete and seems to suggest that this date format conversion is happening within the context of a class that has responsibility for some sort of record retrieval. If so, why is this class returning the formatted date? Should this not be more of a display concern than a data retrieval concern? Why not just return a DateTime object and let caller determine formatting concerns based on application context. Right now you are tightly coupling your data retrieval logic to your display logic. You should not need to go into a data model to make a change if you need to change date formats for display purposes.
  • Your code has some areas that are overly verbose:

For example, this code:

// fetching records
$date_from_db = $row->created_time;
$display_date = new DateTime($date_from_db , new DateTimeZone('UTC'));
$display_date->setTimeZone(new DateTimeZone($this->session->timezone));
return $display_date->format("m/d/Y h:i a");

Could be simplified to (assuming of course you have created DateTimeZone object previously as mentioned above):

return new DateTime($row->created_time, $displayTimeZone);

You have:

  • unnecessary variable assignments;
  • setting the DateTimeZone object on DateTime object on the very next line of code after you initialized it, when you could have simply instantiated the object with the desired DateTimeZone to begin with;
  • and of course the odd design for formatting the date before returning, which I mentioned above.
\$\endgroup\$
1
\$\begingroup\$

I personally would have saved it as a simple timestamp rather than a date, whether or not that's actually a better idea is debatable, but a timestamp requires half as much space as a datetime and is, imo, much easier to use.

On the client side you can get the user's UTC offset with a simple new Date().getTimezoneOffset() which will return the difference between the user's time and UTC in minutes (multiply by 60 to get seconds). Then you can use PHP's time() function to get the current timestamp and subtract the user's offset.

When you need to display it as a human readable date, PHP's date() can take a timestamp as a second parameter to display a specific time. Timestamps are just a lower level, more efficient way to handle time differences.

if($this->userIsAuthenticated())
{
   $this->session->timezone_offset = "-600"; // this value is coming from db
}

// inserting records
$data = array("created_time"=>time());
$this->db->insert("my_table", $data);

// fetching records
$date_from_db = $row->created_time;
$display_date = date("m/d/Y h:i a", $date_from_db - ($this->session->timezone_offset * 60));
\$\endgroup\$
  • \$\begingroup\$ My guess is that using field storage size as a driving design concern for how to store timestamps in a database is only applicable to a trivially small percentage of applications. Datetime/timestamp fields are better in most every other way when you inevitably need to begin using the fields in real-world type queries - time range filtering, aggregation, etc. \$\endgroup\$ – Mike Brant Sep 25 '17 at 21:22
  • \$\begingroup\$ Also, by the fact that you are doing integer math to create your formatted date, you have degraded the OP's original solution which has built-in handling for date/time edge case like dealing with daylight savings time boundary cases. \$\endgroup\$ – Mike Brant Sep 25 '17 at 21:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.