CakePHP 3.8 stores 2024-12-30 as 2025-12-30 in MariaDB, but 2024-12-29 works correctly

Need Help
1

I am working on a CakePHP 3.8 application where I save dates into a MariaDB database. The absent_date field is of type DATE. However, I’ve encountered a peculiar issue:

  • When I save 2024-12-30, it gets stored as 2025-12-30.
  • When I save 2024-12-29, it works as expected and remains 2024-12-29.

My application is set to use the Indian timezone (Asia/Kolkata). Here’s the table structure for reference:

CREATE TABLE attendance_details (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    student_id INT(11) NOT NULL,
    year_id INT(11) NOT NULL,
    absent_date DATE NOT NULL
);

Here is my bulkUpload method:

public function bulkUpload($data = array())
{
    $entities = $this->newEntities($data);
    dd($entities); // Debugging the entities

    $this->saveMany($entities);
    return true;
}

The debug output for 2024-12-30 shows the following:

array:3 [
  0 => Entity {#255 ▼
    #_properties: array:3 [▼
      "student_id" => 1
      "year_id" => 7
      "absent_date" => "2024-12-30"
    ]
    #_original: []
    #_hidden: []
    #_virtual: []
    #_className: null
    #_dirty: array:3 [▶]
    #_new: true
    #_errors: []
    #_invalid: []
    #_accessible: array:1 [▶]
    #_registryAlias: "AttendanceDetails"
    student_id: 1
    year_id: 7
    absent_date: "2024-12-30"
    [new]: true
    [accessible]: array:1 [▶]
    [dirty]: array:3 [▶]
    [original]: []
    [virtual]: []
    [hasErrors]: false
    [errors]: []
    [invalid]: []
    [repository]: "AttendanceDetails"
  }
]

If I directly store the date into the database using a manual query, it is stored correctly as 2024-12-30.

This issue seems to occur only when storing data through CakePHP. I suspect it might be related to timezone conversions or the way CakePHP handles DATE fields.

It works fine if I change the datatype of absent_date to VARCHAR, but I want to retain the DATE datatype for proper date handling and querying.

Has anyone encountered a similar issue or knows how to resolve this while keeping the DATE datatype?

2

I can’t reproduce your Problem with CakePHP 3.10.5 and MariaDB 11.6

I am more confused about the fact, what you do in your $this->newEntities($data); call.

I’d say you are creating the entities wrong because all I did was:

$locator = new TableLocator();
$table = $locator->get('Test');
$entity = $table->newEntity(['customdate' => '2024-12-30']);
debug($entity);
$table->save($entity);

be aware, that his is just inside a simple PHPUnit test of mine. you don’t need to fetch the table instance via the TableLocator because usually you have access to it via $this->MyModel inside your controller.

and the debug() output shows, that my customdate has been converted into a FrozenDate object.

object(App\Model\Entity\Test) {

        'customdate' => object(Cake\I18n\FrozenDate) {

                'time' => '2024-12-30 00:00:00.000000+00:00',
                'timezone' => 'UTC',
                'fixedNowTime' => false
        
        },
        '[new]' => true,
        '[accessible]' => [
                'customdate' => true
        ],
        '[dirty]' => [
                'customdate' => true
        ],
        '[original]' => [],
        '[virtual]' => [],
        '[hasErrors]' => false,
        '[errors]' => [],
        '[invalid]' => [],
        '[repository]' => 'Test'
}
3

I resolved the issue by adding the following code in the bootstrap.php file:

Type::build('date')
    // ->useImmutable() // Commented this line, as it caused records to store as 0000-00-00
    ->useLocaleParser()
    ->setLocaleFormat('yyyy-MM-dd'); // Added these lines to prevent incorrect parsing of dates like 2024-12-30 and 2024-12-31 as 2025-12-30 and 2025-12-31

This ensures that date fields are parsed and formatted correctly throughout the application. I also removed the beforeSave() function from the model, which was sanitizing the date field and causing the date field to be stored incorrectly. With this change, date fields now work as expected. Thank you.