Adjust all date/times to users current timezone

Question

I've done a bunch of research and I can't seem to find a suitable answer for this issue without hacking which I'd like to avoid.

My client needs the entries in the DB to always assume they're relative to the users current timezone. So content set to display at 9am on Monday morning will display at 9am Monday in every timezone.

The locale options in Twig only adjust the UTC timestamp to the users current timezone but it's still displaying content based on UTC timestamp in the DB.

Through MySQL I can use the following command per session to accomplish what I'm looking for:

SET SESSION time_zone = '+10:00';

This appropriately offsets the MySQL time to match a users timezone +10 hours ahead of UTC, in this case Australia.

Reading this article it seems that "initSQLs" might be the answer but it's a Yii article that talks about adding that command to the db array within the protected/config/main.php file which I'm unsure of how to tie into.

Nevertheless, I'm hoping someone might have some insight into how I can bootstrap the DB calls per user session or another solution potentially utilizing a plugin.

We're doing timezone lookups by IP address, I realize the potential issue, so the option will need to work for both logged in and logged out users.

Answer 1

I wouldn’t recommend hacking the MySQL timezone, since that would happen on a global basis, not just for the current user, and will result in some weird race conditions whenever two people are using the site/CP at the same time.

Getting an entry to go live at a certain time based on their time zone is a fundamentally different thing than the feature provided by entry Post Dates. So your best bet here is to code the functionality yourself, rather than trying to bend Post Dates into something they’re not.

To do that, you should create your own custom Date/Time field to hold the post dates. Give it a handle that’s different than “postDate” so you don’t run into naming conflicts in your templates - let’s say it’s “liveDate”.

In your templates, you can query against your liveDate field like so:

{% set timezone = 'Europe/Paris' %}
{% set localDate = date()|date('Y-m-d H:i:s', timezone) %}
{% set entries = craft.entries.section('blog').liveDate('<=' ~ localDate) %}

That will only give you entries where the liveDate field is before or equal to the current date/time, in whatever timezone you’ve passed in.

Couple gotcha’s:

• Twig’s |date filter requires that you specify the timezone using one of PHP’s supported timezone names, not a numeric offset. Your script that is determining the timezone based on the user’s IP will need to account for that.
• The built-in Post Date fields will still be enforced, so you will need to get in the habit of making sure those are set at least 14 hours before the custom liveDate field is set (UTC-14 is the earliest time that “tomorrow” begins). That’s assuming you’ve left your system timezone set to UTC in Settings > General.

Answer 2

I just created this simple function in my custom extensions service

 public function getTimeByZone(\DateTime $time, $zone)
{

    $timeZ = new \DateTime($time->format('H:i:s'), new \DateTimeZone('UTC)'));
    $timeZ->setTimezone(new \DateTimeZone($zone));
    return $timeZ;

}

Where:

-$time is the UTC stored datetime object -$zone is the user's time zone, for instance, CET

To call it from twig, in my case would be:

{{  getTimeByZone(dateObject, app.request.headers.get('timezone')) | date( 'H:i') }}