Is it possible to have a DateTime mapper for every underlaying RDBMS?

Question

I am trying to develop my own ORM Framework on top of PDO that follows the Active Record concept and is easy to use. As diving into it, I figured out, that every relational database have its own understanding of date and time data types. I would like to use in my ORM only PHP's DateTime and map it down to the underlaying RDBMS, so that we can use the advantages of those data types. But I am not sure, if it is possible, just think of comparsion between a period and so on. Actually, that's also where Doctrine's DBAL seems not to care about. They just say not supported on the different data types for date and time when it comes into the differences.

What I want is something like this:

MySQL:      PHP DateTime => maps to MySQL DateTime field
PostgreSQL: PHP DateTime => maps to a splitted date field and a time field
Oracle:     PHP DateTime => maps to TIMESTAMP
...

The user should be able to set his DateTime field with a setter method on that relational object. When the select, insert, update or delete is executed, it should be mapped to that underlaying maybe splitted date and time field. The same should appear if the user requests that value via the getter method.

Please tell me how it is possible and please also provide a few examples. Thanks a lot! :-)

Edit: I played a bit around with the adapter solution as mentioned below. I think this solves the issue:

<?php
namespace Diana\Core\Persistence\Sql\Dbal\Gateway;

interface IAdapter
{
    /**
     * Drills an DateTime instance down to underlaying RDBMS date and time string.
     *
     * @param \DateTime $date
     * @return string
     */
    public function drillDownDateTime(\DateTime $date);


    /**
     * Parse the RDBMS rows back to an DateTime instance.
     *
     * @param array $rows
     * @return \DateTime
     */
    public function parseDateTime(array $rows);
}


<?php
namespace Diana\Core\Persistence\Sql\Dbal\Gateway;

class OracleAdapter implements IAdapter
{
    const FORMAT = 'Y-m-d H:i:s e';
    /**
     * @inheritdoc
     */
    public function drillDownDateTime(\DateTime $date)
    {
        return $date->format(self::FORMAT);
    }

    /**
     * @inheritdoc
     */
    public function parseDateTime(array $rows)
    {
        return \DateTime::createFromFormat(self::FORMAT, $rows[0]);
    }
}

<?php
namespace Diana\Core\Persistence\Sql\Dbal\Gateway;

class PostgreSqlAdapter implements IAdapter
{
    const FORMAT = 'Y-m-d H:i:s e';

    /**
     * @inheritdoc
     */
    public function drillDownDateTime(\DateTime $date)
    {
        return $date->format(self::FORMAT);
    }

    /**
     * @inheritdoc
     */
    public function parseDateTime(array $rows)
    {
        return \DateTime::createFromFormat(self::FORMAT, $rows[0]);
    }
}

As I found in documentation in

Oracle

MySQL

PostgreSQL

This should work. In MySQL the timezone is a setting in database, so nothing that we can take care about. It is also just an example. But these files in the finished version will be soon part of my framework. Due to my own Date class, which automatically figures out the local date string when outputting, we use DateTime as a paramter here, so that both can be passed in.

Answer 1

Create an adapter interface!

<?php

interface DbAdapterInterface
{
    /**
     * @param DateTime $date
     * @return string
     */ 
    public function convertFromDateTime(DateTime $date);

    /**
     * @param DateTime $date
     * @return array
     */ 
    public function convertToDateTime(array $row);
}

class MySqlAdapter implements DbAdapterInterface
{
    public function convertFromDateTime(DateTime $date)
    {
        return $date->format('Y-m-d H:i:s');
    }

    public function convertToDateTime(array $row)
    {
        return new DateTime($row['date']);
    }
}

class OracleAdapter implements DbAdapterInterface
{
    public function convertFromDateTime(DateTime $date)
    {
        return $date->getTimestamp();
    }

    public function convertToDateTime(array $row)
    {
        return new DateTime('@'.$row['date']);
    }
}

class Db
{
    /** @var DbAdapterInterface $adapter */
    private $adapter;

    public function setAdapter(DbAdapterInterface $adapter)
    {
        $this->adapter = $adapter;
    }

    public function insert($data)
    {
        $date = $data['date'];
        $dateString = $this->adapter->convertFromDateTime($date);
        // do your insert
        return $dateString;
    }

    public function findById($id)
    {
        // fetch row by id here, this is just an example, I'm using the id as the date string
        $row = ['date' => $id];
        $row = $this->adapter->convertToDateTime($row);
        // do your insert
        return $row;
    }
}


// Example

$data = [
    'date' => new DateTime(),    
];

$db = new Db();
$db->setAdapter(new MySqlAdapter());
echo $db->insert($data)."\n";
$db->setAdapter(new OracleAdapter());
echo $db->insert($data)."\n";

$time = '1493308146';
var_dump( $db->findById($time));

$db->setAdapter(new MySqlAdapter());
$time = '2014-09-18 22:00:00';
var_dump( $db->findById($time));

See here for a working example: https://3v4l.org/pYgbE