What are the preferred MySQL column data types for CodeIgniter 4's created_at, updated_at, and deleted_at field definitions?

Background Information

Every time CodeIgniter runs, there are several base classes that are initialized automatically as part of the core framework. One of which is the CodeIgniterCodeIgniter system class.

In this particular class, a default timezone used by all date/time functions on your server (date_default_timezone_set) and a default runtime locale (Locale::setDefault) are set.

Excerpt from the CodeIgniterCodeIgniter system class

public function initialize()
{
    // ...

    // Set default locale on the server
    Locale::setDefault($this->config->defaultLocale ?? 'en');

    // Set default timezone on the server
    date_default_timezone_set($this->config->appTimezone ?? 'UTC');

    $this->initializeKint();
}

If you look closely enough, the application’s ‘locale’ and ‘timezone’ settings are primarily based on what you defined in the app/Config/App.php configuration file or in the .env environment variable file that resides at the root of your project.

Excerpt from the app/Config/App.php configuration file.

Default Locale and Application Timezone

    /**
     * --------------------------------------------------------------------------
     * Default Locale
     * --------------------------------------------------------------------------
     *
     * The Locale roughly represents the language and location that your visitor
     * is viewing the site from. It affects the language strings and other
     * strings (like currency markers, numbers, etc), that your program
     * should run under for this request.
     */
    public string $defaultLocale = 'en';

    // ...

    /**
     * --------------------------------------------------------------------------
     * Application Timezone
     * --------------------------------------------------------------------------
     *
     * The default timezone that will be used in your application to display
     * dates with the date helper, and can be retrieved through app_timezone()
     */
    public string $appTimezone = 'UTC';

So, to answer your inquiry,

• OP’s inquiry:

… I am not sure if CodeIgniter will store the time in UTC or in the
server’s time, which may not be UTC.

Answer: That will depend on your app timezone setting in the app/Config/App.php configuration file or in the .env environment variable file that resides at the root of your project. By default, the $appTimezone property is set to ‘UTC’.

Additional Explanation

With the background information out of the way, if you want to inform CodeIgniter 4 to automatically set the created_at and updated_at fields in addition to the deleted_at field for soft deleted records, you need to override the $useTimestamps property on your Model and set it to true.

$useTimestamps

This boolean value determines whether the current date is
automatically added to all inserts and updates. If true, will set the
current time in the format specified by $dateFormat. This requires
that the table have columns named created_at, updated_at and
deleted_at in the appropriate data type.

These fields/columns (created_at, updated_at & deleted_at) are automatically filled/set when you call the following methods on your Model instance:

1. ->save(...).
2. ->insert(...).
3. ->insertBatch(...).
4. ->update(...).
5. ->updateBatch(...).
6. ->delete(...).

Now, coming back to your question,

• OP’s question:

For that, should I set those fields as timestamp in MySQL or datetime?

Response:

Firstly, at the time of writing this post, CodeIgniter 4 supports 3 formats by default, namely:

1. ‘datetime’. I.e: ‘Y-m-d H:i:s’.
2. ‘date’. I.e: ‘Y-m-d’.
3. ‘int’. I.e: (a PHP timestamp).

$dateFormat

This value works with $useTimestamps and $useSoftDeletes to ensure
that the correct type of date value gets inserted into the database.
By default, this creates DATETIME values, but valid options are:
'datetime', 'date', or 'int' (a PHP timestamp). Using
useSoftDeletes or useTimestamps with an invalid or missing dateFormat will cause an exception.

With the linked resource above, CodeIgniter 4 expects the fields (created_at, updated_at & deleted_at) to have a DATETIME data type. To supplement on this, one of CodeIgniter 4’s official packages called CodeIgniter Shield also uses DATETIME as it’s preferred column data type for theses specific fields.

Excerpt from Shield’s Database/Migrations/2020-12-28-223112_create_auth_tables.php file

public function up(): void
{
    // Users Table
    $this->forge->addField([
        // ...
        'last_active'    => ['type' => 'datetime', 'null' => true],
        'created_at'     => ['type' => 'datetime', 'null' => true],
        'updated_at'     => ['type' => 'datetime', 'null' => true],
        'deleted_at'     => ['type' => 'datetime', 'null' => true],
    ]);
    $this->forge->addPrimaryKey('id');
    $this->forge->addUniqueKey('username');
    $this->forge->createTable($this->tables['users']);

// ...
}

CodeIgniter Settings, another official package also uses DATETIME as it’s preferred column data type for these specific fields.

Excerpt from Settings’s Database/Migrations/2021-07-04-041948_CreateSettingsTable.php file

public function up()
{
    $this->forge->addField('id');
    $this->forge->addField([
    // ...
        'created_at' => [
            'type' => 'datetime',
            'null' => false,
        ],
        'updated_at' => [
            'type' => 'datetime',
            'null' => false,
        ],
    ]);
    $this->forge->createTable($this->config->database['table'], true);
}

Important notes:

1. The deleted_at field must be nullable. In addition, it is expected to have a column data type of either DATETIME or INTEGER.

$useSoftDeletes

If true, then any delete() method calls will set deleted_at in the
database, instead of actually deleting the row. This can preserve data
when it might be referenced elsewhere, or can maintain a “recycle bin”
of objects that can be restored, or even simply preserve it as part of
a security trail. If true, the find()* methods will only return
non-deleted rows, unless the withDeleted() method is called prior to
calling the find()* method.

This requires either a DATETIME or INTEGER field in the database
as per the model’s $dateFormat setting. The default field name is
deleted_at however this name can be configured to any name of your
choice by using $deletedField property.

Important

The deleted_at field must be nullable.