README

This plugin provides time_interval custom type for MySQL's TIME, Postgres's INTERVAL, and provides time_interval_int custom type for seconds as INTEGER. This is a custom type to represent intervals, which CakePHP can treat as a TimeInterval object that inherits from DateInterval.

Version Map

Installation

You can install this plugin into your CakePHP application using composer.

The recommended way to install composer packages is:

composer require elstc/cakephp-time-interval

Load plugin

Load the plugin by adding the following statement in your project's src/Application.php:

$this->addPlugin('Elastic/TimeInterval');

Usage

Add column definitions to Table class

use Cake\Database\Schema\TableSchema;

class WorkTimesTable extends Table
{
    protected function _initializeSchema(TableSchema $schema)
    {
        parent::_initializeSchema($schema);

        $schema->setColumnType('duration', 'time_interval');

        // If your column type is seconds as INTEGER, Use `time_interval_int` instead.
        $schema->setColumnType('duration_sec', 'time_interval_int');

        return $schema;
    }
}

Add column validation to Table class

Use timeInterval rule instead of time. The timeInterval rule is in the timeInterval validation provider.

use Cake\Validation\Validator;
use Elastic\TimeInterval\Validation\TimeIntervalValidation;

class WorkTimesTable extends Table
{
    public function validationDefault(Validator $validator)
    {
        // ...
        $validator->add('duration', 'timeInterval', [
            'rule' => 'timeInterval',
            'provider' => 'timeInterval',
        ]);

        return $validator;
    }
}

Adding a mutator to the Entity class is useful

use Cake\Database\Type;

class WorkTime extends Entity
{
    protected function _setDuration($value)
    {
        // convert to TimeInterval
        return Type::build('time_interval')->marshal($value);
    }
}

$workTime->duration = '00:15:00';
$workTime->duration = ($startTime)->diff($endTime); // $startTime, $endTime are DateTime objects.
$workTime->duration = 3600; // as seconds

NOTE

MySQL TIME column limitation

MySQL :: MySQL 8.0 Reference Manual :: 13.2.3 The TIME Type

By default, values that lie outside the TIME range but are otherwise valid are clipped to the closest endpoint of the range. For example,
'-850:00:00' and '850:00:00' are converted to '-838:59:59' and '838:59:59'. Invalid TIME values are converted to '00:00:00'.
Note that because '00:00:00' is itself a valid TIME value, there is no way to tell, from a value of '00:00:00' stored in a table,
whether the original value was specified as '00:00:00' or whether it was invalid.

DateInterval / TimeInterval constructed with date parts will have incorrect time interpretation

If you initialize DateInterval with date parts, time will not be interpreted correctly.

$workTime->duration = new DateInterval('PT75H4M5S'); // OK
$workTime->duration = new DateInterval('P1M2DT3H4M5S'); // can't get expected time