Why doesn't MySQL support millisecond / microsecond precision?

Question

So I just found the most frustrating bug ever in MySQL.

Apparently the TIMESTAMP field, and supporting functions do not support any greater precision than seconds!?

So I am using PHP and Doctrine, and I really need those microseconds (I am using the actAs: [Timestampable] property).

I found a that I can use a BIGINT field to store the values. But will doctrine add the milliseconds? I think it just assigns NOW() to the field. I am also worried the date manipulation functions (in SQL) sprinkled through the code will break.

I also saw something about compiling a UDF extension. This is not an acceptable because I or a future maintainer will upgrade and poof, change gone.

Has anyone found a suitable workaround?

Answer 1

From the SQL92-Standard:

• TIMESTAMP - contains the datetime field's YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.

A SQL92 compliant database does not need to support milli- or microseconds from my point of view. Therefore the Bug #8523 is correctly marked as "feature request".

How does Doctrine will handle microseconds et al? I just found the following: Doctrine#Timestamp:

The timestamp data type is a mere combination of the date and the time of the day data types. The representation of values of the time stamp type is accomplished by joining the date and time string values in a single string joined by a space. Therefore, the format template is YYYY-MM-DD HH:MI:SS.

So there are no microseconds mentioned either as in the SQL92-docs. But I am not to deep into doctrine, but it seems to be an ORM like hibernate in java for example. Therefore it could/should be possible to define your own models, where you can store the timeinformation in a BIGINT or STRING and your model is responsible to read/write it accordingly into your PHP-classes.

BTW: I don't expect MySQL to support TIMESTAMP with milli/microseconds in the near future eg the next 5 years.

HTH & Best Regards

Michael

Answer 2

As you're using Doctrine to store data, and Doctrine does not support fractional seconds either, then the bottleneck is not MySQL.

I suggest you define additional fields in your objects where you need the extra precision, and store in them the output of microtime(). You probably want to store it in two different fields - one for the epoch seconds timestamp and the other for the microseconds part. That way you can store standard 32bit integers and easily sort and filter on them using SQL.

I often recommend storing epoch seconds instead of native timestamp types as they are usually easier to manipulate and avoid the whole time zone issue you keep getting into with native time types and providing service internationally.

Answer 3

I found a workaround! It is very clean and doesn't require any application code changes. This works for Doctrine, and can be applied to other ORM's as well.

Basically, store the timestamp as a string.

Comparisons and sorting works if the date string is formatted correctly. MySQL time functions will truncate the microsecond portion when passed a date string. This is okay if microsecond precision isn't needed for date_diff etc.

SELECT DATEDIFF('2010-04-04 17:24:42.000000','2010-04-04 17:24:42.999999');
> 0

SELECT microsecond('2010-04-04 17:24:42.021343');
> 21343 

I ended up writing a MicroTimestampable class that will implement this. I just annotate my fields as actAs:MicroTimestampable and voila, microtime precision with MySQL and Doctrine.

Doctrine_Template_MicroTimestampable

class Doctrine_Template_MicroTimestampable extends Doctrine_Template_Timestampable
{
    /**
     * Array of Timestampable options
     *
     * @var string
     */
    protected $_options = array('created' =>  array('name'          =>  'created_at',
                                                    'alias'         =>  null,
                                                    'type'          =>  'string(30)',
                                                    'format'        =>  'Y-m-d H:i:s',
                                                    'disabled'      =>  false,
                                                    'expression'    =>  false,
                                                    'options'       =>  array('notnull' => true)),
                                'updated' =>  array('name'          =>  'updated_at',
                                                    'alias'         =>  null,
                                                    'type'          =>  'string(30)',
                                                    'format'        =>  'Y-m-d H:i:s',
                                                    'disabled'      =>  false,
                                                    'expression'    =>  false,
                                                    'onInsert'      =>  true,
                                                    'options'       =>  array('notnull' => true)));

    /**
     * Set table definition for Timestampable behavior
     *
     * @return void
     */
    public function setTableDefinition()
    {
        if ( ! $this->_options['created']['disabled']) {
            $name = $this->_options['created']['name'];
            if ($this->_options['created']['alias']) {
                $name .= ' as ' . $this->_options['created']['alias'];
            }
            $this->hasColumn($name, $this->_options['created']['type'], null, $this->_options['created']['options']);
        }

        if ( ! $this->_options['updated']['disabled']) {
            $name = $this->_options['updated']['name'];
            if ($this->_options['updated']['alias']) {
                $name .= ' as ' . $this->_options['updated']['alias'];
            }
            $this->hasColumn($name, $this->_options['updated']['type'], null, $this->_options['updated']['options']);
        }

        $this->addListener(new Doctrine_Template_Listener_MicroTimestampable($this->_options));
    }
}

Doctrine_Template_Listener_MicroTimestampable

class Doctrine_Template_Listener_MicroTimestampable extends Doctrine_Template_Listener_Timestampable
{
    protected $_options = array();

    /**
     * __construct
     *
     * @param string $options 
     * @return void
     */
    public function __construct(array $options)
    {
        $this->_options = $options;
    }

    /**
     * Gets the timestamp in the correct format based on the way the behavior is configured
     *
     * @param string $type 
     * @return void
     */
    public function getTimestamp($type, $conn = null)
    {
        $options = $this->_options[$type];

        if ($options['expression'] !== false && is_string($options['expression'])) {
            return new Doctrine_Expression($options['expression'], $conn);
        } else {
            if ($options['type'] == 'date') {
                return date($options['format'], time().".".microtime());
            } else if ($options['type'] == 'timestamp') {
                return date($options['format'], time().".".microtime());
            } else {
                return time().".".microtime();
            }
        }
    }
}

Answer 4

Another workaround for Time in milliseconds. Created function "time_in_msec"

USAGE :

Difference between two dates in milliseconds.

mysql> SELECT time_in_msec('2010-07-12 23:14:36.233','2010-07-11 23:04:00.000') AS miliseconds;
+-------------+
| miliseconds |
+-------------+
| 87036233    |
+-------------+
1 row in set, 2 warnings (0.00 sec)



DELIMITER $$

DROP FUNCTION IF EXISTS `time_in_msec`$$

CREATE FUNCTION `time_in_msec`(ftime VARCHAR(23),stime VARCHAR(23)) RETURNS VARCHAR(30) CHARSET latin1
BEGIN
    DECLARE msec INT DEFAULT 0;
    DECLARE sftime,sstime VARCHAR(27);
    SET ftime=CONCAT(ftime,'000');
    SET stime=CONCAT(stime,'000');
    SET  msec=TIME_TO_SEC(TIMEDIFF(ftime,stime))*1000+TRUNCATE(MICROSECOND(TIMEDIFF(ftime,stime))/1000,0);
    RETURN msec;
END$$

DELIMITER ;