Adjust date/time based on timezone


#1

I’m new to CakePHP and have my first app almost done in 3.7.1. The default app & MySQL timezone is UTC. Users will enter data that includes date/time and a location. I have timezones associated with these locations. A simple view of the adjusted date/time seems easy enough using:

<?= h($race->date->i18nFormat(null, $race->racetrack->timezone, null)) ?>

The date/time is displayed in the proper timezone. I’m struggling with the add and edit functions. On an add, I need to adjust the entered timezone to UTC before it is saved. Then, on edit, I need to adjust the date/time from UTC to the stored local timezone, allow editing, and then convert it back to UTC. This should be an easy task.

I’m trying to do this and stay within the framework rather than hacking something together. Any help in doing this properly would be most appreciated.
Thanks


#2

So if I understand correctly,
Users will enter their date/time along with their location.
Based on their location you convert them to the UTC equivalent (to store them in the database).
You then save it in the database.
Later you load it from the database again, then convert it back to the local time of the user, then add/remove some time to this, convert it back to UTC and then store it again?

If that’s the case, why not store and handle it like UTC but only have the display have the local user’s timezone?
It should save quite a bit of hassle along with some resources.

also, have you taken a look at this class?
https://book.cakephp.org/3.0/en/core-libraries/time.html


#3

Thanks for the feedback. To provide a little more context for what I’m trying to do: The user will enter details about a race which includes the date/time the race took place and the racetrack where the race was held. The race table has a relationship with the racetrack table. The local timezone is located in the racetrack table so users do not enter their location per se.

I did look through the page you referenced and already use the i18n for displaying the correct time in my view and index screens, I use h($race->date->i18nFormat(null, $race->racetrack->timezone, null)) to convert the UTC date/time to the appropriate local time. This works very well.

My problem is when I add or edit the date/time field for a race. On the add, how do I take the added data and convert it to UTC before it is saved. And on the edit screen, how do I convert it from UTC to local time (based on that relationship) before displaying?

I know I’m probably missing something simple but have been struggling with this.


#4

so on the add, you want to have the “current” time and add some time to it (eg. 2 hours) and take the end-result? or?

and on the edit screen, you basically do something like this:

$now = Time::now();
$now->timezone = 'Europe/Amsterdam';

#5

Focusing on the add function, when I present the form, it already defaults to the current time in UTC. For display on the form, I need to adjust this using the actual TZ. Would this be done in the controller? Something like $race->date->timezone = ‘America/New_York’ ? And then when it comes back, set it to UTC in the controller before the save?


#6

Still struggling with this. I have an edit() function in the controller. Before the set(), I adjust the timezone:

$race->date = $race->date->setTimezone(‘America/New_York’);

This seems to work and it renders the time correctly in the view.
Before the setTimezone():
object(Cake\I18n\FrozenTime) {
‘time’ => ‘2018-12-02T01:37:00+00:00’,
‘timezone’ => ‘UTC’,
‘fixedNowTime’ => false
}

After the setTimezone():
object(Cake\I18n\FrozenTime) {
‘time’ => ‘2018-12-01T20:37:00-05:00’,
‘timezone’ => ‘America/New_York’,
‘fixedNowTime’ => false
}

The problem is what I get back from the view but before the save() is:
object(Cake\I18n\FrozenTime) {
‘time’ => ‘2018-12-01T20:37:00+00:00’,
‘timezone’ => ‘UTC’,
‘fixedNowTime’ => false
}

I cannot figure out where the timezone is being changed. UTC was not passed in. The time is correct but the timezone is wrong. Any ideas?


#7

Maybe share your current code so we can see what’s going on. “Get back from the view but before the save” doesn’t make sense given standard Cake code structure, so it might be an order of operations problem at this point.


#8

Here are some snippets. I tried to remove some of the boring code. I started out fully baking the code and then edited from there. It’s pretty generic. I left the debug() statements in and included what they returned. I got tired continually fixing the date/time in the database so I just crash it before the save().

CONTROLLER:
    public function edit($id = null)
    {
        $userGroups = $this->Auth->user('user_group_id');
        $race = $this->Races->get($id, [
            'contain' => []
        ]);
        if ($this->request->is(['patch', 'post', 'put'])) {
            $race = $this->Races->patchEntity($race, $this->request->getData());
debug($race->date);     <- #3
crashit();              <- Just used to crash the code so it doesn't save the wrong date/time
            if ($this->Races->save($race)) {
                $this->Flash->success(__('The race has been saved.'));

                return $this->redirect(['action' => 'index']);
            }
            $this->Flash->error(__('The race could not be saved. Please, try again.'));
        }
        $racetracks = $this->Races->Racetracks->find('list', [
			'keyFields' => 'id',
			'valueField' => 'name',
			'limit' => 200
		]);
		if ($racetracks->count() === 0) {
			return $this->redirect(['action' => 'index']);
        }

        $racecars = $this->Races->Racecars->find('list', [
			'conditions' => ['Racecars.user_group_id' => $userGroups],
			'keyFields' => 'id',
			'valueField' => 'description',
			'limit' => 200
		]);
		if ($racecars->count() === 0) {
			return $this->redirect(['action' => 'index']);
        }

        $users = $this->Races->Users->find('list', [
			'keyFields' => 'id',
			'valueField' => 'name',
			'limit' => 200
		]);

debug($race->date);   <- #1
		$race->date = $race->date->setTimezone('America/New_York');
debug($race->date);   <- #2

        $this->set(compact('race', 'racetracks', 'racecars', 'users'));
        $this->set('userGroups', $userGroups);
    }

DEBUG RESULTS (from above):
#1 (Correct)
object(Cake\I18n\FrozenTime) {
‘time’ => ‘2018-12-02T01:37:00+00:00’,
‘timezone’ => ‘UTC’,
‘fixedNowTime’ => false
}

#2 (Correct)
object(Cake\I18n\FrozenTime) {
‘time’ => ‘2018-12-01T20:37:00-05:00’,
‘timezone’ => ‘America/New_York’,
‘fixedNowTime’ => false
}

#3 (Wrong)
object(Cake\I18n\FrozenTime) {
‘time’ => ‘2018-12-01T20:37:00+00:00’,
‘timezone’ => ‘UTC’,
‘fixedNowTime’ => false
}

VIEW:
<div class="races form large-9 medium-8 columns content">
    <?= $this->Form->create($race) ?>
    <fieldset>
        <legend><?= __('Edit Race') ?></legend>
        <?php
            echo $this->Form->control('date', ['label' => 'Date']);
            echo $this->Form->control('rnd', ['label' => 'Rnd']);
            echo $this->Form->control('elim', [
				'options' => [
					'Y' => 'Yes',
					'N' => 'No'
				],
				'label' => 'Elim',
				'type' => 'select'
			]);

        ...(removed some boring stuff)...

        ?>
    </fieldset>
    <?= $this->Form->button(__('Submit')) ?>
    <?= $this->Form->end() ?>
</div>

TABLE:
    public function validationDefault(Validator $validator)
    {
        $validator
            ->integer('id')
            ->allowEmpty('id', 'create');

        $validator
            ->dateTime('date')
            ->requirePresence('date', 'create')
            ->notEmpty('date');

        $validator
            ->integer('rnd')
            ->requirePresence('rnd', 'create')
            ->notEmpty('rnd');

        ...(removed some boring stuff)...

        return $validator;
    }

#9

So, you patch, then you save, then you set the timezone, and you’re surprised that the change to the timezone isn’t saved?


#10

Not sure it works that way. Based on my reading of the docs and a lot of debug() statements, the controller is called twice. It is called to setup the view, the view is called, and then the controller is called again. Based on my testing, here is the series of events that take place:

  1. Controller edit() function called from a link
  2. The record ID and a usergroup is identified
  3. It is not a patch, post, or put so the save() if logic is ignored
  4. The associated data is identified
  5. The debug() is called and shows the correct time
  6. The timezone is changed which properly adjusts the date/time
  7. The debug() is called and shows the correct date/time in a different timezone
  8. The set()s are called
  9. The View is rendered
  10. The form end() is reached in the view once the Submit is clicked
  11. Control is sent back to the controller edit() function
  12. The record ID and a usergroup is identified again which seems like a total waste of time but that’s how it was Baked
  13. This time through, the request type is a post so it triggers the if statement
  14. The data is retrieved from the view form
  15. The debug is called and shows the incorrect date/time
  16. The save() is called
  17. The user is returned to the index() screen

Based on how it actually works, I’m not sure I understand your comment. When it comes back from the view, it never executes that code at the bottom of the edit() function.


#11

It does everything from 1 through 9 in one process, then PHP ends and you are presented with the form. When you click Submit, it does everything from 12 through 16, in a new PHP process. So, the second time through, the variable is an entirely new instance, which has not yet had the time zone changed on it.


#12

It may be a new instance, but why is the data changing? When I debug() the date/time in the view, it is still correct. When the data is retrieved from the view:

($race = $this->Races->patchEntity($race, $this->request->getData());

the timezone has been changed. Is this normal behavior that the timezone of data in flight will be changed back to UTC? If this is the case, I go back to my original question: How do you properly manage timezones and moving between timezones?


#13

Dates are always stored in the database as UTC. That’s just how the database works. You can set the timezone that you are connecting to the database with, and then they’ll be read back as being in that timezone instead, with any required adjustments. And if you write a date with a different timezone into the database, it’ll convert it to UTC before saving it.

What’s presumably happening here is that you are connecting with UTC. The date in the database is 01:37 UTC. You read that and change the timezone, getting 20:37, so that’s what is displayed in the form. Note that the form doesn’t include anything about a timezone, just the numbers.

Now, you submit the form, which has 20:37 as the time, to the edit function. It starts by reading the old data, and once again it’s back to 01:37 UTC, because the database hasn’t been changed yet. Without changing the timezone of that data, you patch it with 20:37. So, now it’s 20:37 UTC, which you save to the database.

Does that make sense?

If you’re always using the same time zone, you can set that in your app.php. If not, you’ll presumably want to set the timezone on the data right away after reading it, before patching. Just move the existing call to do that up, from between your debug #1 and 2, to right after the get call.


#14

If I could just set a default timezone for the session, this wouldn’t be a problem. My issue is it’s dynamic based on the location information in a table for a specific event that takes place.

I think I understand the problem. Looking at what’s returned from getData() shows no timezone so patchEntity() applies what it pulls from the database which is UTC.

Based on that, your recommendation makes sense. I moved the setTimezone() after the get() but didn’t see the results I was expecting. The date/time/timezone was correct before the patchEntity(getData()) but incorrect after.

BEFORE patchEntity():
‘date’ => object(Cake\I18n\FrozenTime) {
‘time’ => ‘2018-12-01T20:37:00-05:00’,
‘timezone’ => ‘America/New_York’,
‘fixedNowTime’ => false
}

AFTER patchEntity():
‘date’ => object(Cake\I18n\FrozenTime) {
‘time’ => ‘2018-12-01T20:37:00+00:00’,
‘timezone’ => ‘UTC’,
‘fixedNowTime’ => false
}

Not sure about the nuances of patchEntity(), but does this result have to do with the date field being marked as dirty? Or is the date field being returned in getData() assumed to be UTC?