Greg Schmidt Greg Schmidt - 4 months ago 44
MySQL Question

CakePHP 3 time column gets date added

I have a number of columns defined in my MySQL database as "time". That is, they have a time, but not a date. When they are read by the CakePHP 3 ORM, they are being converted into Cake\I18n\Time objects (through the Cake\Database\Type\TimeType class), but the result always has both a date and a time in it, with the date set to the current date. For example, if the value is "20:00:00",

debug($record['start_time'])
will generate:

object(Cake\I18n\Time) {
'time' => '2015-06-21T20:00:00+0000',
'timezone' => 'UTC',
'fixedNowTime' => false
}


And when I echo it in a template (without having used
setToStringFormat
), I get something like
6/21/15 8:00 PM
. Of course, I can use
$this->Time->format
to put it back into a time-only format, but it seems very strange that I would need to do so. Why is Cake ignoring the fact that this is just a time, and, more importantly, is there a way to make it stop?

ndm ndm
Answer

Date/Time values are all being casted to the same base structure, that is a DateTime object, and so naturally date-only values will have a time value added (00:00:00), and time-only values come with a date (the current date).

CakePHP will uses specific subclasses depending on the SQL datatype, that is

  • \Cake\I18n\Time or \Cake\I18n\FrozenTime for TIME, TIMESTAMP, and DATETIME
  • \Cake\I18n\Date or \Cake\I18n\FrozenDate for DATE

In earlier CakePHP 3 versions there was only \Cake\I18n\Time.

It would be nice if there would be a separate class for time-only types, which would have a proper time-only default output format set, but until something like that is added, you'll have to take care of the output format yourself.

Format in your views

It is up to you how to display this in your views. You can easily use the i18nFormat() method of the Time class instance

$record['start_time']->i18nFormat(
    [\IntlDateFormatter::NONE, \IntlDateFormatter::SHORT]
)

or the Time helper, to show only the time part

$this->Time->i18nFormat(
    $record['start_time'],
    [\IntlDateFormatter::NONE, \IntlDateFormatter::SHORT]
)

Guess it wouldn't hurt if bake would generate similar code according to the type of column, you may want to suggest that as an enhancement. As mentioned using additional classes (or maybe options) for time-only columns may be something worth to consider too.

Use a custom time class

If you'd wanted this behavior everywhere where the string representation of the object is being used, without having to manually invoke the formatter, then you could make use of an extended \Cake\I18n\Time or \Cake\I18n\FrozenTime class with an overriden $_toStringFormat property, so that it formats the date accordingly.

src/I18n/FrozenTimeOnly.php

namespace App\I18n;

use Cake\I18n\FrozenTime;

class FrozenTimeOnly extends FrozenTime
{
    protected static $_toStringFormat = [
        \IntlDateFormatter::NONE,
        \IntlDateFormatter::SHORT
    ];
}

src/config/bootstrap.php

use Cake\Database\Type\TimeType;
use App\I18n\FrozenTimeOnly;
TimeType::$dateTimeClass = FrozenTimeOnly::class;

// remove the default `useImmutable()` call, you may however
// want to keep further calls for formatting and stuff
Type::build('time'); 
// ...

This should pretty much self-explantory, time columns that are being mapped to TimeType, will now use App\I18n\FrozenTimeOnly instead of the default Cake\I18n\Time.

DateTimeType::$dateTimeClass is deprecated

In order to cope with that, a custom database type will be required, which is rather simple too.

src/Database/Type/TimeOnlyType.php

namespace App\Database\Type;

use App\I18n\FrozenTimeOnly;
use Cake\Database\Type\TimeType;

class TimeOnlyType extends TimeType
{
    public function __construct($name)
    {
        parent::__construct($name);
        $this->_setClassName(FrozenTimeOnly::class, \DateTimeImmutable::class);
    }
}

It should be note that currently this will instantiate a data/time class twice, as the parent constructor will invoke _setClassName() too, which is where an instance of the given class will be instantiated.

src/config/bootstrap.php

use App\Database\Type\TimeOnlyType;
Type::map('time', TimeOnlyType::class);

So what this will do, is override the default time type mapping to use the custom \App\Database\Type\TimeOnlyType class, which in turn will use the \App\I18n\TimeOnly class when converting database values to PHP objects, which when converted to a string, will use the time-only format.

See also