CODEWITHSUNDEEP
phpcakephpmariadb-10.4cakephp-3.8
Pavan Kumar
Pavan Kumar

Reputation: 31

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

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

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;
}

Debug output for 2024-12-30:

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"
  }
]

Observations:

Temporary workaround:

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.

Question:

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

Upvotes: 0

Views: 123

Answers (1)

Pavan Kumar
Pavan Kumar

Reputation: 31

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.

Upvotes: 2

Related Questions