Why doesn't MySQL support millisecond / microsecond precision?

Question

So, I found the most annoying mistake in MySQL .

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

So, I use PHP and Doctrine, and I really need these microseconds (I use the actAs: [Timestampable] property).

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

I also saw something about compiling a UDF extension. This is not acceptable, because I or the future attendant will update and deceive, change.

Has anyone found a suitable solution?

Answer 1

I found a workaround! It is very clean and does not require application code changes. This works for the Doctrine and can be applied to another ORM.

Basically, save the timestamp as a string.

Comparison and sorting works if the date string is formatted correctly. MySQL temporary functions will truncate the microsecond part when transmitting a date string. This is normal if for date_diff etc. No microsecond precision is required.

 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 comment my fields as actAs:MicroTimestampable and actAs:MicroTimestampable , precision micros in 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' => '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(); } } } } 

Answer 2

For information to the following readers, this error was finally fixed in version 5.6.4 :

"MySQL now supports fractional seconds for TIME, DATETIME and TIMESTAMP values ​​accurate to microseconds."

Answer 3

In SQL92-Standard:

• TIMESTAMP - contains the date and time field YEAR, MONTH, DAY, HOUR, MINUTE and SECOND.

A database compatible with SQL92 should not support millions or microseconds from my point of view. Therefore, error # 8523 is correctly marked as "function request".

How will Doctrine handle microseconds, etc.? I just found the following: Doctrine # timestamp:

A timestamp data type is simply a combination of the date and time of daytime data types. representation of time values ​​a stamp type is achieved by appending a date and time value to a single line connected by a space. Therefore, the format template is YYYY-MM-DD HH: MI: SS.

Thus, microseconds are not mentioned as in SQL92-docs. But I should not go deep into the doctrine, but it seems to be ORM, for example, sleep mode in java. Therefore, it can / should be able to define your own models, where you can store time information in BIGINT or STRING, and your model is responsible for reading and writing them to your PHP classes.

BTW: I do not expect MySQL to support TIMESTAMP with milli / microseconds in the near future, for example, the next 5 years.

Answer 4

Since you use Doctrine to store data, and Doctrine also does not support fractional seconds, the bottleneck is not MySQL.

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

I often recommend storing seconds of an era instead of their own types of timestamps, since they are usually easier to manipulate and avoid the whole problem in the time zone that you constantly encounter with native types of timestamps and providing services internationally.

Answer 5

Another way to bypass time in milliseconds. Created function "time_in_msec"

USING:

The 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 ; 

Answer 6

Starting with Mysql 5.6.4, it stores a microsecond in a column.

"MySQL now supports fractional seconds for TIME, DATETIME and TIMESTAMP values ​​accurate to microseconds."

For example:

 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; 

You only need to change the data type from datetime to datetime (6);

For more information, see the following: http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

Answer 7

Now you can use microseconds

 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) 

Answer 8

As mentioned, microsecond support was added in version 5.6.4.

Perhaps for fractional seconds the following is used:

 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 ; 

Test:

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

Another kind:

 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 | +----------------------------+----------------------------+--------+ 

See the MySQL manual page called Fractional seconds in time values.