Native Date Type

This article describes how it is possible for TYPO3 to store dates (and possibly time) as real native dates in your database and not as usual using a timestamp representation of them.

In a first step, we will show the trick to use date columns in your schema while still being able to transparently manage them within TYPO3, typically with its integrated date/time picker. In a second step, we will consider enhancing Extbase to support them as well as nowadays, it is more and more likely that you are using this framework when developing your own extensions.

Date/Time Picker of TYPO3

Introduction

As a TYPO3 developer, you know that your dates are stored as a Unix timestamp in the database and both TYPO3 and Extbase handle them transparently for you, either by showing a handy date/time picker (figure on the right) or, when using Extbase, by mapping them back and forth to a PHP DateTime object. So what is the point of wanting to use a "date" column type in the database?

First of all, the use of a timestamp is constraint by its range. A Unix timestamp is a number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT). So what if you want to store a date that is older than 1970? The behaviour is not really defined and an attempt in PHP to store or convert such an old date seems to work:

<?php
$birthday = '1950/08/25';
echo 'birthday as a timestamp: ' . strtotime($birthday) . "\n";
echo 'formatting from timestamp: ' . date('d.m.Y', strtotime($birthday)) . "\n";
?>

Output will be:

birthday as a timestamp: -610765200
formatting from timestamp: 25.08.1950

If you try with Extbase and its native DateTime mapping, it will work as well. So far so good!

The first problem you will encounter is that the native TYPO3 date/time picker does not let you properly handle dates before January 1 1970.

I first tried to solve this by enhancing the date/time picker but finally gave up because it is extremely inefficient for picking up a birthday, by definition far in the past. I then created a user control that hooks into a standard timestamp field but shows 3 dropdown fields instead:

'birthday' => array(
    'exclude' => 0,
    'label' => 'LLL:EXT:myext/Resources/Private/Language/locallang_db.xlf:tx_myext_domain_model_person.birthday',
    'config' => array(
        'type' => 'user',
        'userFunc' => 'Tx_MyExt_Tca_Form_DatePickerField->render',
        'parameters' => array(
            'lower' => 1900,
            'upper' => date('Y') - 17,	// 18 years old at least
            'eval' => 'required',
        ),
    ),
),

This results into:

I successfully used this approach for a while until I had to start querying my database to retrieve all persons whose birthday is… during current month. Of course I did not want to do the logic in PHP and having a timestamp prevented me from easily filtering by the month part. We all know MySQL has a few date and time functions and more specifically MONTH(). This MySQL function needs a date type as argument; as we have a timestamp, we use another function first, FROM_UNIXTIME(), which converts a timestamp into a date:

SELECT * FROM mytable WHERE MONTH(FROM_UNIXTIME(birthday)) = 7

But I got an empty or very partial result set… The reason is that FROM_UNIXTIME() expects a non-signed integer as argument, so the trick in PHP to use a negative timestamp for dates before January 1 1970 was pushed to the limit!

Another problem with a timestamp is that we have no chance to manually modify it in the database, we are forced to use our application (TYPO3) or have to do relatively complex handling of the information outside of the database client (phpMyAdmin, Sequel Pro, …).

At this point, I had no other choice than investigating how to make TYPO3 understand real date types.

Adding Support for TCA

The specification for supporting dates is simple: native date/time picker and derived controls such as my custom date picker should work as before without any single modification. That said, the solution is to transparently convert from native date to timestamp (possibly negative as we know it is working) and from a timestamp to a date when persisting back the information to the database.

First of all we need to know which "date" fields in TYPO3 are using a native date type in database instead of the standard timestamp, I decided to extend TCA with a new configuration key:

'birthday' => array(
    ...
    'config' => array(
'dbType' => 'date',
'type' => ... ... ), ),

By using "dbType" — Database Type — we are sure it will not conflict with any other definition, being thus transparent from a TYPO3/TCA perspective.

The table definition in ext_tables.sql needs to be updated as well:

#
# Table structure for table 'tx_myext_domain_model_person'
#
CREATE TABLE tx_myext_domain_model_person (
    uid int(11) NOT NULL AUTO_INCREMENT,
    pid int(11) DEFAULT '0' NOT NULL,
 
    ...
    birthday date DEFAULT '0000-00-00',
    ...
) ENGINE=InnoDB;

To convert from a date to a timestamp, we need to XCLASS t3lib_transferData, check if "dbType" is defined and convert the date to a timestamp if needed:

class ux_t3lib_transferData extends t3lib_transferData {
    function renderRecord($table, $id, $pid, array $row) {
        $tca = $GLOBALS['TCA'][$table];
        foreach ($tca['columns'] as $column => $config) {
            if (isset($config['config']['dbType']) && $config['config']['dbType'] === 'date') {
                    // Convert date column into a timestamp
                $row[$column] = (!empty($row[$column]) && $row[$column] !== '0000-00-00')
                        ? strtotime($row[$column]) : 0;
            }
        }
        parent::renderRecord($table, $id, $pid, $row);
    }	
}

Register the XCLASS as usual in ext_localconf.php:

    // Add support for native date/time fields in database
    // This XCLASS handles the database -> TYPO3 conversion
$GLOBALS['TYPO3_CONF_VARS'][TYPO3_MODE]['XCLASS']['t3lib/class.t3lib_transferdata.php'] =
    t3lib_extMgm::extPath($_EXTKEY) . 'Classes/XClass/class.t3lib_transferData.php';

At this point, if you open a database client (phpMyAdmin, …) and put a date in your birthday field, you should see it properly preset if you edit the corresponding record in TYPO3.

Next step is to convert back the timestamp to a date upon saving.

We have two TCEmain hook candidates for this task, one is processDatamap_preProcessFieldArray the other is processDatamap_postProcessFieldArray, both being executed before writing to the database.

Hook processDatamap_postProcessFieldArray is not the best one to pick because you will end up updating the date columns in the database even if their value did not change. The hook implementation becomes:

class Tx_MyExt_Hooks_TCEmain {	
    public function processDatamap_preProcessFieldArray(array &$row, $table, $id, t3lib_TCEmain $pObj) {
        if (!isset($GLOBALS['TCA'][$table]['columns'])) {
            t3lib_div::loadTCA($table);
        }
        $tca = $GLOBALS['TCA'][$table];
        foreach ($tca['columns'] as $column => $config) {
            if (isset($config['config']['dbType']) && $config['config']['dbType'] === 'date') {
                    // Convert timestamp into a date
                $row[$column] = $row[$column] ? date('Y-m-d', $row[$column]) : '0000-00-00';
            }
        }
    }
}
 

Register the hook implementation in ext_tables.php:

    // Add support for native date/time fields in database
    // This hook handles the TYPO3 -> database conversion
$TYPO3_CONF_VARS['SC_OPTIONS']['t3lib/class.t3lib_tcemain.php']['processDatamapClass'][] =
    'EXT:' . $_EXTKEY . '/Classes/Hooks/t3lib_TCEmain.php:Tx_MyExt_Hooks_TCEmain';

That’s it! We can now read and write native date type with TYPO3!

Adding Support for Extbase

Extbase already maps timestamp values to DateTime objects in the domain model. In order to support converting from a MySQL date to a DateTime object we need to extend Tx_Extbase_Persistence_Mapper_DataMapper:

class Tx_MyExt_Persistence_Mapper_DataMapper extends Tx_Extbase_Persistence_Mapper_DataMapper {	
    /**
     * Creates a DateTime from an unix timestamp or a string
     * representation of a date. If the input is empty
     * NULL is returned.
     *
     * @param integer|date $value
     * @return DateTime
     */
    protected function mapDateTime($value) {
        if (empty($value) || $value === '0000-00-00') { // 0 -> NULL !!!
            return NULL;
        } else {
            if (!t3lib_utility_Math::canBeInterpretedAsInteger($value)) {
                $value = strtotime($value);
            }
            return new DateTime(date('c', $value));
        }
    }
}
 

Extbase does not allow classes to be extended via the traditional XCLASS mechanism. Instead, it uses a clever Dependency Injection mechanism that lets us use our class instead of the original class. To register this change, we have to write a few lines of TypoScript:

config.tx_extbase.objects {
    Tx_Extbase_Persistence_Mapper_DataMapper {
        className = Tx_MyExt_Persistence_Mapper_DataMapper
    }
}

The other conversion, when Extbase will try to persist a DateTime into a native date column should be done within class Tx_Extbase_Persistence_Backend. We extend it as well:

class Tx_MyExt_Persistence_Backend extends Tx_Extbase_Persistence_Backend {
    protected function persistObject(Tx_Extbase_DomainObject_DomainObjectInterface $object) {		
        // ... same code as the original method
 
        foreach ($properties as $propertyName => $propertyValue) {
            // ... same code as the original method
 
            } elseif ($object->_isNew() || $object->_isDirty($propertyName)) {
                $tca = $GLOBALS['TCA'][$dataMap->getTableName()];
                $columnConfig = $tca['columns'][$columnMap->getColumnName()]['config'];
                if (isset($columnConfig['dbType']) && $columnConfig['dbType'] === 'date') {
                    $row[$columnMap->getColumnName()] = $this->getPlainValue($propertyValue, TRUE);
                } else {
                    $row[$columnMap->getColumnName()] = $this->getPlainValue($propertyValue);
                }
            }
        }
 
        // ... same code as the original method
    }
 
    protected function getPlainValue($input, $nativeDate = FALSE) {
        if ($input instanceof DateTime) {
            if ($nativeDate) {
                return $input->format('Y-m-d');
            } else {
                return $input->format('U');
            }
        } elseif ($input instanceof Tx_Extbase_DomainObject_DomainObjectInterface) {
            return $input->getUid();
        } elseif (is_bool($input)) {
            return $input === TRUE ? 1 : 0;
        } else {
            return $input;
        }
    }	
}

There is a trick here! The Backend implements t3lib_Singleton and is instantiated very early in the bootstrap; as such, we cannot override it with a TypoScript configuration and we have to use an non-API method of Extbase to achieve this goal, within ext_localconf.php:

/** @var $extbaseObjectContainer Tx_Extbase_Object_Container_Container */
$extbaseObjectContainer = t3lib_div::makeInstance('Tx_Extbase_Object_Container_Container');
$extbaseObjectContainer->registerImplementation(
    'Tx_Extbase_Persistence_BackendInterface',
    'Tx_MyExt_Persistence_Backend'
);

Good to know is that Extbase itself uses it so it’s not an API but it is the official way to register our implementation anyway!

Known Limitations

  • If you use a standard "input" TCA and extend it with dbType, it will fail to pass the checks (lower, upper, ...). Please check my TYPO3 Core patch targeted at TYPO3 6.0 ( 38965 if you want to backport it manually) if you want to get the "full" version
  • I did not implement the wrapper to support querying date columns with Extbase

What About Date and Time?

Adding support for date and time type is easy now, we should use for instance dbType = "datetime" and extend the conversion methods to test this other dbType, nothing tricky here. In fact, I did it with the Core patch targeted at TYPO3 6.0.

What About DBAL?

You may wonder if this method is compatible with DBAL? Of course not as this! In order to become DBAL-aware we should do it within Core itself, the format ("Y-m-d" or "0000-00-00") could be stored as part of the database "driver" itself, thus within t3lib_DB with some helper method (to be able to override it easily). DBAL would override the helper method, check the underlying DBMS (it would need the table name as argument of course, just as it is for the string quoting method) and return the according date (or date/time) format to be used.

Flattr