Can't store date before 01/01/1600 in CakePHP application

Question

I have a very simple table.

I generated the files with

bin\cake bake all [tableName]

I now have a form where I can add/edit/remove entries.

The problem is that if I put a date that's before 1st January 1600, I get an error saying that the date is invalid.

I assume that the error comes from the validator.

Here are the validation rules :

 /**
 * Default validation rules.
 *
 * @param \Cake\Validation\Validator $validator Validator instance.
 * @return \Cake\Validation\Validator
 */
public function validationDefault(Validator $validator)
{
    $validator
        ->integer('id')
        ->allowEmpty('id', 'create');

    $validator
        ->date('debut_date')
        ->allowEmpty('debut_date');

    $validator
        ->date('fin_date')
        ->allowEmpty('fin_date');

    $validator
        ->allowEmpty('nom');

    $validator
        ->allowEmpty('description');

    return $validator;
}

I don't understand why this error happens. Could you help me please?

Thanks

Answer 1

Not supported by validation

It looks like the validation simply doesn't allow years before 1600.

1[6-9]|[2-9]

https://github.com/cakephp/cakephp/blob/3.2.10/src/Validation/Validation.php#L404

I'm not sure about the reasoning behind this, maybe it is to cope with some DBMS's storage limitations, like SQL Servers 1/1/1753 minimum:

What is the significance of 1/1/1753 in SQL Server?

Consider alternative storage types, or even a different DBMS

Given that you want to store years before 1000, it's worth mentioning that officially, MySQLs DATE types supported range starts at 1000-01-01 (as mentioned by @user221931 in the comments). And while there seems to be unofficial support for years before 1000 (ie 0861-07-03 actually can be stored and worked with), this is probably not something that you want to rely on.

Possible solutions for this are a little offtopic here, and have been discussed on SO already, like

Does MySQL support historical date (like 1200)?

You can use a custom regex

Whether this is something that should maybe be changed in the core or not, in any case you could provide your own regex for the date validation to use, in order to accept pretty much any date you need, however you'd need to use add() as date() doesn't support custom regexes (might be worth an enhancement), like for example

->add('fin_date', [
    'rule' => ['date', null, '\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])']
])

That check may be too relaxed, but you should get the point.

You may want to open a ticket over at GitHub or visit IRC or Slack and ask the core team for clarification. Personally I think that supporting non-outdated/limited DBMS (given that this is actually the reason for that behavior) out of the box is something that could be worth an enhancement.