Why doesn't MySQL support millisecond / microsecond precision?
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.
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');> 0SELECT 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(); } } }}