Modifying data from database before building the result array

I'm planning to create an API for CRUD operations. Retrieving data from the database works flawlessly as usual and also returning is no issue. But when it comes to dates, I'm facing issues with the format. The dates are stored as DATETIME in the database and are automatically created at insert, but I want to deliver an unix timestamp when accessing via API. I've stripped down my code to the crucial parts:

PHP Code:

<?php 
// Controllers/Config.php
namespace App\Controllers\API;
 
use CodeIgniter\RESTful\ResourceController;
use CodeIgniter\API\ResponseTrait;
use App\Models\ConfigModel;
 
class Config extends ResourceController
{
    use ResponseTrait;
    public function index()
    {
        $model = new ConfigModel();
        return $this->respond($model->getConfig());
    }
}

// Models/ConfigModel.php
namespace App\Models;
  
use CodeIgniter\Model;
  
class ConfigModel extends Model
{
    protected $table = 'configuration';
    protected $primaryKey = 'id';
    protected $allowedFields = ['name','value','is_default'];

    protected function getCurrentConfig()
    {
        $query = $this->query('SELECT * FROM configuration WHERE id IN (SELECT MAX(id) FROM configuration GROUP BY name)');
        return $query->getResult();
    }

    public function getConfig()
    {
        return $this->getCurrentConfig();
    }
} 


Code:

// Result
[
    {
        "id": "1",
        "name": "ticket_number_format",
        "value": "default",
        "is_default": "1",
        "create_timestamp": "2021-09-22 09:11:07" // need: 1632294667
    },
    {
        "id": "3",
        "name": "ticket_number_format_default_alphanumeric",
        "value": "true",
        "is_default": "1",
        "create_timestamp": "2021-09-22 09:21:40" // need: 1632295300
    },
    // ...
}

I know that I could simply select each field in separate and enclose UNIX_TIMESTAMP(create_timestamp) but I'm planning to operate on tables with much more columns in the future as well. Also I may use the query builder instead of custom SQL for more basic selects. I've red about the getCustomResultObject(), but I can't figure out how to use it.

Okay nevermind, I managed to solve it by myself:
I figured out I was on the right path. The key to this issue are those entity classes. I added following entity class:

PHP Code:

// Entities/Config.php
namespace App\Entities;

use CodeIgniter\Entity\Entity;
use CodeIgniter\I18n\Time;

class Config extends Entity
{
    public function getCreateTimestamp()
    {
        $this->attributes['create_timestamp'] = $this->mutateDate($this->attributes['create_timestamp']);
        return $this->attributes['create_timestamp']->getTimestamp();
    }
} 


and modified the model:

PHP Code:

// Models/ConfigModel.php
namespace App\Models;
  
use CodeIgniter\Model;
  
class ConfigModel extends Model
{
    protected $table = 'configuration';
    protected $primaryKey = 'id';
    protected $allowedFields = ['name','value','is_default'];
    // I think this is only needed for those 
    // built-in CRUD function like findAll()
    protected $returnType    = 'App\Entities\Config';

    protected function getCurrentConfig()
    {
        $query = $this->query('SELECT * FROM configuration WHERE id IN (SELECT MAX(id) FROM configuration GROUP BY name)');
        // Use the entity here
        return $query->getResult('App\Entities\Config');
    }

    public function getConfig()
    {
        return $this->getCurrentConfig();
    }
} 


Et voilá:

Code:

// Result
[
    {
        "id": "1",
        "name": "ticket_number_format",
        "value": "default",
        "is_default": "1",
        "create_timestamp": 1632319867
    },
    {
        "id": "3",
        "name": "ticket_number_format_default_alphanumeric",
        "value": "true",
        "is_default": "1",
        "create_timestamp": 1632320500
    },
    // ...
}

Here are a couple of methods that should do what you want, NOT TESTED.

PHP Code:

/**
 *  convertToTimestamp ()
 * -----------------------------------------------------------------------
 */
if (! function_exists('convertToTimestamp'))
{
    /**
    * convertToTimestamp ()
    * -------------------------------------------------------------------
    *
    * convert datetime object to timestamp
    *
    * @param  string $datetime
    * @return string
    * @throws Exception
    */
 function convertToTimestamp(string $datetime): string
 {
        $date = new DateTime($datetime);

        return $date->getTimestamp();
 }
}

/**
 *  convertToDatetime ()
 * -----------------------------------------------------------------------
 */
if (! function_exists('convertToDatetime'))
{
    /**
    * convertToDatetime ()
    * -------------------------------------------------------------------
    *
    * convert timestamp to datetime
    *
    * @param  string $timestamp
    * @return string
    */
 function convertToDatetime(string $timestamp): string
 {
        $date = new DateTime();
        $date->setTimestamp($timestamp);

        return $date->format('Y-m-d H:i:s');
 }
} 


Let me know if they work for you.

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Here are a couple of methods that should do what you want, NOT TESTED.

PHP Code:

/**
 *  convertToTimestamp ()
 * -----------------------------------------------------------------------
 */
if (! function_exists('convertToTimestamp'))
{
    /**
    * convertToTimestamp ()
    * -------------------------------------------------------------------
    *
    * convert datetime object to timestamp
    *
    * @param  string $datetime
    * @return string
    * @throws Exception
    */
 function convertToTimestamp(string $datetime): string
 {
        $date = new DateTime($datetime);

        return $date->getTimestamp();
 }
}

/**
 *  convertToDatetime ()
 * -----------------------------------------------------------------------
 */
if (! function_exists('convertToDatetime'))
{
    /**
    * convertToDatetime ()
    * -------------------------------------------------------------------
    *
    * convert timestamp to datetime
    *
    * @param  string $timestamp
    * @return string
    */
 function convertToDatetime(string $timestamp): string
 {
        $date = new DateTime();
        $date->setTimestamp($timestamp);

        return $date->format('Y-m-d H:i:s');
 }
} 


Let me know if they work for you.

Thank you for your fast answer! I'm not sure where to put those functions (do they belong in the entity?), but I managed to solve the issue by myself.
The Post #2 just needs to be approved.