CodeIgniter4: [Model] DateTime hardcoded format `Y-m-d H:i:s` drives to error on some databases

PHP Version

8.1

CodeIgniter4 Version

4.3.0

CodeIgniter4 Installation Method

Manual (zip or tar.gz)

Which operating systems have you tested for this bug?

Windows

Which server did you use?

apache

Database

SQL Server 2019

What happened?

My db expects datetime format like 'd-m-Y H:i:s'. Unfortunately, the format 'Y-m-d H:i:s' is hardcoded in several parts of the CI code and trying to insert something into a table with, for example, 'created_at' column drives me to varchar-to-datetime conversion error.

Steps to Reproduce

Extend CodeIgniter\Model and try to save something to a table with 'created_at' (or 'updated_at') column, without explicitly set that value (doing so leaves BaseModel the task to handle the 'created_at' column value). The table is hosted in a db with DMY dateformat.

Expected Output

The insertion should work

Anything else?

The discussion started here. Initially, I thought it was only a CodeIgniter Shield problem, but it’s not.

About this issue

Original URL
State: closed
Created a year ago
Comments: 23 (21 by maintainers)

Most upvoted comments

Personally, I think it is a bad practice to change the date/time format in a program depending on the locale. Locale should only be considered when displaying to end users.

However, if there is a database that does not accept Y-m-d H:i:s datetime, then it is better that we have the date/time format setting for database in somewhere.

kenjis on Jan 26, 2023

I agree, as far as a configurable solution goes. Dates/times are a unique because a) all databases (AFAIK) have a native scalar type for them, while PHP does not, and b) they are super common (unlike, e.g. spatial data). I do wonder if Time is too bulky/opinionated a solution for an equivalent PHP scalar, but it’s the framework’s universal date/time representation so it seems the obvious choice.

There’s one other assumption in the translation we should be mindful of, that Time is always timezone-specific whereas database DATETIME is not (TIMESTAMP is always UTC).

MGatner on Feb 7, 2024

@tswagger ~~I sent a PR for Entity bug fix: #7995 If it is merged, we can set column-level behavior with Entity (Casting).~~

kenjis on Dec 6, 2023

I think I would simplify it. We have datetime, date and int. And these values translate to the desired formats. In addition, we should add support for “normal” formatting, like:

protected $dateFormat = 'd-m-Y H:i:s';

And that’s it. We can use $this->dateFormat in the linked LoginModel in Shield. That would resolve the issue, I think.

But changing $dateFormat may be challenging if we deal with a package like Shield. Probably we want to avoid copying every model to set a custom date format. Therefore, maybe we should add some master config in Shield or CodeIgniter core to set the default value for $modelDateFormat. The default value would be null which would favor the settings set in the model file.

michalsn on Jan 28, 2023

But after all, we need the properties for the format strings (datetime and date)? We need to solve https://github.com/codeigniter4/shield/issues/608

kenjis on Jan 28, 2023