Why are date/time values interpreted incorrectly when patching/saving?

Question

I try to save data from a cakephp 3 form. All data are well saved but datetime not. I've got 2 datetime fields. Those fields are filled by jquery-ui widget.

The problem seems to happened when pacthing entity.

$intervention = $this->Interventions->patchEntity($intervention, $this->request->data);

• Debug of $this->request->data :

  'user_id' => '1',
  'description' => 'test',
  'starttime' => '2015/11/15 10:00',
  'endtime' => '2015/11/15 12:10'
  

• Debug of my object $intervention after pacthEntity :

object(App\Model\Entity\Intervention)

'id' => (int) 3,
'user_id' => (int) 1,
'description' => 'test',
'starttime' => null,
'endtime' => null
...

starttime and endtime become null and I don't understand why.

Is somebody had this pb before ?

I tried (for debuging and understanding) to force fields value afer patching and datetime fields in mysql are ok.

$intervention->starttime = date('Y-m-d H:i:s', strtotime($this->request->data['starttime'])); 
$intervention->endtime = date('Y-m-d H:i:s', strtotime($this->request->data['endtime']));

Thanks for help

Answer 1

Date/time values are being casted/parsed in a locale aware fashion

---

Update: this is the default behavior with the CakePHP application template versions prior to 3.2.5. As of 3.2.5 locale parsing is not enabled by default anymore, which will make the date/time marshalling logic expect a default format of Y-m-d H:i:s instead.

---

In the marshalling process, values are being "casted" according to the respective column types. For DATETIME columns, this is done by the \Cake\Database\Type\DateTimeType type class.

To be exact, this is done in \Cake\Database\Type\DateTimeType::marshall().

With the default app template configuration, DateTimeType is configured to use locale aware parsing, and since no default locale format is being set, \Cake\I18n\Time::parseDateTime() will parse the values according to its default "to string format" (Time::$_toStringFormat), which defaults to the locale aware [IntlDateFormatter::SHORT, IntlDateFormatter::SHORT].

So, if for example your locale is set to en_US, then the value would be parsed with an expected format of M/d/yy, h:mm a, which your value wouldn't fit, and hence you'd finally end up with null being set for the entity property.

Make the parser use the proper format

tl;dr

In case the format for the jQuery widget is not being used everywhere in your app, you could for example either temporarily set the proper locale format, or disable locale parsing, like

// for time- or date-only comlumn types you'd use 'time' or 'date' instead of 'datetime'
$dateTimeType = Type::build('datetime')->setLocaleFormat('yyyy/MM/dd HH:mm');

// ...
$intervention = $this->Interventions->patchEntity($intervention, $this->request->data);
// ...

$dateTimeType->setLocaleFormat(null);

or

$dateTimeType = Type::build('datetime')->useLocaleParser(false);

// ...
$intervention = $this->Interventions->patchEntity($intervention, $this->request->data);
// ...

$dateTimeType->useLocaleParser(true);

It should be noted that this will affect all date/time input, not just your starttime and endtime fields!

Should the format used by the jQuery widget on the other hand be the format that you wish to use all the way through your app, then changing the default format could do it too, like

use Cake\I18n\Time;
use Cake\I18n\FrozenTime;

// To affect date-only columns you'd configure `Date` and `FrozenDate`.
// For time-only columns, see the linked SO question below.
Time::setToStringFormat('yyyy/MM/dd HH:mm');
FrozenTime::setToStringFormat('yyyy/MM/dd HH:mm');

in your bootstrap.php. Note that there's also Time/FrozenTime::setJsonEncodeFormat() and Time/FrozenTime::$niceFormat which you may want/need to modify too.

See also

• Cookbook > Internationalization & Localization > Parsing Localized Datetime Data
• Cookbook > Time > Setting the Default Locale and Format String
• CakePHP 3 time column gets date added

Convert the input before marshalling it

Another option would be to for example convert the data to Time instances before the marshalling process. This would avoid possible problems with the previous mentioned solution that would affect all input.

In your InterventionsTable class (could also be put in a behavior or an external listener):

use Cake\Event\Event;
use Cake\I18n\Time;

...

public function beforeMarshal(Event $event, \ArrayObject $data, \ArrayObject $options)
{
    foreach (['starttime', 'endtime'] as $key) {
        if (isset($data[$key]) && is_string($data[$key])) {
            $data[$key] = Time::parseDateTime($data[$key], 'yyyy/MM/dd HH:mm');
        }
    }
}

See also

Cookbook > Database Access & ORM > Saving Data > Modifying Request Data Before Building Entities