Welcome Guest, Not a member yet? Register   Sign In
Code Iginiter 4 + SQL Server + Date Formats in Models
#1

(This post was last modified: 03-16-2022, 08:28 AM by antidote.)

I am running XAMPP on a local machine, with PHP 7.1, MS SQL Server Express and obviously Ci4 MVC. I'm based in the UK and the SQL Server is setup to British English (date format d-m-y).
Been trying to debug this issue for a while now, when I try to use either softDelete or Timestamps, I get a date format error (i.e. d-m-y numbers are in the wrong positions). I had put a fix in the Entity to use callback to reformat the date. That worked fine on the update date, but not on the delete date (that's because the date is not in the $data array for the callback, only the PK of the record to delete.
I eventually checked the BaseModel.php file to see if I could fine the cause, and noticed the two date format functions are using HARDCODED date formats when returning the dates for the update_at and delete_at fields. And the format is Y-M-D ! Fine if I'm using MariaDB but not if I use SQL Server.
PHP Code:
    /**
    * A utility function to allow child models to use the type of
    * date/time format that they prefer. This is primarily used for
    * setting created_at, updated_at and deleted_at values, but can be
    * used by inheriting classes.
    *
    * The available time formats are:
    *  - 'int'      - Stores the date as an integer timestamp
    *  - 'datetime' - Stores the data in the SQL datetime format
    *  - 'date'    - Stores the date (only) in the SQL date format.
    *
    * @param integer $value value
    *
    * @return integer|string
    *
    * @throws ModelException
    */
    protected function intToDate(int $value)
    {
        switch ($this->dateFormat)
        {
            case 'int':
                return $value;
            case 'datetime':
                return date('Y-m-d H:i:s'$value);
            case 'date':
                return date('Y-m-d'$value);
            default:
                throw ModelException::forNoDateFormat(static::class);
        }
    }

    /**
    * Converts Time value to string using $this->dateFormat
    *
    * The available time formats are:
    *  - 'int'      - Stores the date as an integer timestamp
    *  - 'datetime' - Stores the data in the SQL datetime format
    *  - 'date'    - Stores the date (only) in the SQL date format.
    *
    * @param Time $value value
    *
    * @return string|integer
    */
    protected function timeToDate(Time $value)
    {
        switch ($this->dateFormat)
        {
            case 'datetime':
                return $value->format('Y-m-d H:i:s');
            case 'date':
                return $value->format('Y-m-d');
            case 'int':
                return $value->getTimestamp();
            default:
                return (string) $value;
        }
    
I'm not sure if anyone else has suffered this, I couldn't fine any articles that referenced this issue, mainly because most people seem to be using MariaDB so it wouldn't be an issue.
I change the format to d-m-y and the softDeletes started working without issue. I don't like changing the system code and would rather not. It's a shame this formatting can't be set in the App.php config file or in the constants so we have more control over it. I quickly checked and there are 40 places in the system code where the date format is hardcoded to Y-M-D. Happy to supply this list if its useful.
Anyway, I hope this helps.
Reply
#2

YYYY-MM-DD is the ISO 8601 date format.
The date format may differ from country to country, so the software uses ISO to store dates.
All DBMS support it. It is correctly read by both machine and human.

I would like to suggest that you use the ISO standard for the database. But to display the date to the user, convert it to the format used in your country.
If you are using an Entity, then it is easy to do this with a mutation (setter and getter)
Reply
#3

(This post was last modified: 03-17-2022, 09:26 AM by antidote.)

Thank you for the quick reply. Fair comment, if that's the ISO standard. It's my preferred format but in this particular case I inherited the db and it has other interfaces that already use the d-m-y format - in magnitude. In this rare case, I need to tell CI to perform the timestamp auto-saves with dates in the format d-m-y.

My impression was that constants are a better implementation option than assumed hardcoded values (even if they follow any particular standard) because there will always be exceptions. What is the harm in converting harcoded values to a single constant?

Of course, I have indeed set a human-readable mutator in the entity, which handles the display aspect of the date. However, again, this issue is more specifically that as there is no control over the delete_at date format, a model->save() fails when attempting to save data because the date format is in opposition to the date format of the database, and, given the database in this one rare case cannot be changed, what would my next option be if I wish to continue to keep it within the model? Forgive me if I am being stupid. 



(03-16-2022, 11:59 AM)iRedds Wrote: YYYY-MM-DD is the ISO 8601 date format.
The date format may differ from country to country, so the software uses ISO to store dates.
All DBMS support it. It is correctly read by both machine and human.

I would like to suggest that you use the ISO standard for the database. But to display the date to the user, convert it to the format used in your country.
If you are using an Entity, then it is easy to do this with a mutation (setter and getter)
Reply
#4

The simplest solution would be to create your own model class extending the base class.
In your class, you can change the behavior of the logic you need.
Reply
#5

An excellent suggestion , thank you. I will do precisely that and extend the BaseModel. Clearly I was being stupid to miss this. Thank you for your help.

(03-17-2022, 06:39 PM)iRedds Wrote: The simplest solution would be to create your own model class extending the base class.
In your class, you can change the behavior of the logic you need.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB