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

Ответ 1

Я нашел обходное решение! Он очень чистый и не требует изменений кода приложения. Это работает для Доктрины и может быть применено и к другому 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, который будет реализовывать это. Я просто комментирую свои поля как 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'        =>  '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();
            }
        }
    }
}

Ответ 2

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

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

Ответ 3

В SQL92-Standard:

• TIMESTAMP - содержит поле даты и времени YEAR, MONTH, DAY,         HOUR, MINUTE и SECOND.

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

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

Тип данных временной метки - просто сочетание даты и времени дневных типов данных. представление значений времени тип штампа достигается путем присоединения значения даты и времени в единственная строка, соединенная пробелом. Поэтому шаблон формата ГГГГ-ММ-ДД ЧЧ: МИ: СС.

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

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

Ответ 4

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

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

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

Ответ 5

Другой способ обхода времени в миллисекундах. Созданная функция "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 ;

Ответ 6

Начиная с версии 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

Ответ 7

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

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)

Ответ 8

Как упоминалось, микросекундная поддержка была добавлена ​​в версии 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 под названием Дробные секунды во временных значениях