Question 420151 in PHP Question posted in
Official manual can be found here.

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:

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

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:

  • 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.

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    The issue occurs because CakePHP may apply timezone adjustments when processing the date, causing unexpected shifts. By using FrozenDate::parse() in your bulkUpload method, you ensure that the date is processed without timezone conversion. The i18nFormat('yyyy-MM-dd') ensures the date is stored in the correct format (yyyy-MM-dd) for MariaDB’s DATE field. This approach prevents the date from being altered when saved.

    public function bulkUpload($data = array())
{
   foreach ($data as &$record) {
        // Ensure the date is converted to a FrozenDate (no timezone)
        $record['absent_date'] = FrozenDate::parse($record['absent_date']);
    }

    $entities = $this->newEntities($data);
    $this->saveMany($entities);
    return true;
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search