Почему MySQL не поддерживает миллисекундную / микросекундную точность?

Для информации для следующих читателей эта ошибка, наконец, была исправлена ​​в версии 5.6.4 :

«MySQL теперь поддерживает дробные секунды для значений TIME, DATETIME и TIMESTAMP с точностью до микросекунды».

Из SQL92-Standard:

• TIMESTAMP – содержит поле даты, времени, дня, часа, минуты и второго дня.

База данных, совместимая с SQL92, не должна поддерживать миллионы или микросекунды с моей точки зрения. Поэтому ошибка # 8523 правильно помечена как «запрос функции».

Как Doctrine будет обрабатывать микросекунды и т. Д.? Я только что нашел следующее: Doctrine # Timestamp :

Тип данных временной метки – это просто комбинация даты и времени данных данных дня. Представление значений типа временной метки выполняется путем объединения значений даты и времени в одной строке, соединенной пробелом. Поэтому шаблон формата – YYYY-MM-DD HH: MI: SS.

Таким образом, микросекунды не упоминаются ни как в SQL92-документах. Но я не должен углубляться в доктрину, но, похоже, это ORM, например, спящий режим в java. Поэтому он может / должен быть способен определить ваши собственные модели, где вы можете хранить информацию о времени в BIGINT или STRING, и ваша модель отвечает за их чтение и запись в ваши PHP-классы.

BTW: Я не ожидаю, что MySQL будет поддерживать TIMESTAMP с миллисекундами в ближайшем будущем, например, следующие 5 лет.

Я нашел обходное решение! Он очень чистый и не требует изменений кода приложения. Это работает для Доктрины и может применяться и к другим ORM.

В принципе, сохраните временную метку в виде строки.

Сравнение и сортировка выполняются, если строка даты отформатирована правильно. Временные функции MySQL будут обрезать микросекундную часть при передаче строки даты. Это нормально, если для date_diff нет необходимости в микросекундной точности

 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 

Я закончил тем, что MicroTimestampable класс MicroTimestampable который будет реализовывать это. Я просто комментирую свои поля как actAs:MicroTimestampable и voila, точность микросов с MySQL и 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' => 'Ymd H:i:s', 'disabled' => false, 'expression' => false, 'options' => array('notnull' => true)), 'updated' => array('name' => 'updated_at', 'alias' => null, 'type' => 'string(30)', 'format' => 'Ymd 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(); } } } } 

Поскольку вы используете Doctrine для хранения данных, а Doctrine не поддерживает дробные секунды, то узким местом является не MySQL.

Я предлагаю вам определить дополнительные поля в ваших объектах, где вам нужна дополнительная точность, и сохранить в них вывод microtime() . Вероятно, вы захотите сохранить его в двух разных полях: один для временной метки секунд эпохи, а другой для части микросекунд. Таким образом, вы можете хранить стандартные 32-битные целые числа и легко сортировать и фильтровать их с помощью SQL.

Я часто рекомендую хранить периоды эпохи вместо собственных типов временных меток, поскольку их обычно легче манипулировать и избегать всей проблемы в часовом поясе, с которой вы постоянно сталкиваетесь с родными типами времени и предоставляя услуги на международном уровне.

Другой способ обхода времени в миллисекундах. Созданная функция "time_in_msec"

ПРИМЕНЕНИЕ :

Разница между двумя датами в миллисекундах.

 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 ; 

Начиная с версии Mysql 5.6.4, она хранит микросекунду в столбце.

«MySQL теперь поддерживает дробные секунды для значений TIME, DATETIME и TIMESTAMP с точностью до микросекунды».

Например:

 CREATE TABLE `test_table` ( `name` VARCHAR(1000) , `orderdate` DATETIME(6) ); INSERT INTO test_table VALUES('A','2010-12-10 14:12:09.019473'); SELECT * FROM test_table; 

Нужно только изменить тип данных на datetime на datetime (6);

Для получения дополнительной информации см. Следующие: http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

Теперь вы можете использовать микросекунды

 mysql> select @@version; +-----------+ | @@version | +-----------+ | 5.6.26 | +-----------+ 1 row in set (0.00 sec) mysql> select now(6); +----------------------------+ | now(6) | +----------------------------+ | 2016-01-16 21:18:35.496021 | +----------------------------+ 1 row in set (0.00 sec) 

Как упоминалось, микросекундная поддержка была добавлена ​​в версии 5.6.4

Возможно, для дробных секунд используется следующее:

 drop procedure if exists doSomething123; delimiter $$ create procedure doSomething123() begin DECLARE dtBEGIN,dtEnd DATETIME(6); DECLARE theCount,slp INT; set dtBegin=now(6); -- see http://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html -- now do something to profile select count(*) into theCount from questions_java where closeDate is null; select sleep(2) into slp; -- not the above but "something" set dtEnd=now(6); -- see http://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html select timediff(dtEnd,dtBegin) as timeDiff,timediff(dtEnd,dtBegin)+MICROSECOND(timediff(dtEnd,dtBegin))/1000000 seconds; -- select dtEnd,dtBegin; end$$ delimiter ; 

Контрольная работа:

 call doSomething123(); +-----------------+----------+ | timeDiff | seconds | +-----------------+----------+ | 00:00:02.008378 | 2.016756 | +-----------------+----------+ 

Другой взгляд на это:

 set @dt1=cast('2016-01-01 01:00:00.1111' as datetime(6)); set @dt2=cast('2016-01-01 01:00:00.8888' as datetime(6)); select @dt1,@dt2,MICROSECOND(timediff(@dt2,@dt1))/1000000 micros; +----------------------------+----------------------------+--------+ | @dt1 | @dt2 | micros | +----------------------------+----------------------------+--------+ | 2016-01-01 01:00:00.111100 | 2016-01-01 01:00:00.888800 | 0.7777 | +----------------------------+----------------------------+--------+ 

См. Страницу руководства MySQL под названием « Дробные секунды во временных значениях»