Why Doesn't MySQL Support Millisecond/Microsecond Precision

Why doesn't MySQL support millisecond / microsecond precision?

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();
            }
        }
    }
}

MySQL 5.6 DATETIME doesn't accept milliseconds/microseconds

Found the answer. Data type should be DATETIME(6) for microseconds and DATETIME(3) for milliseconds.

TIME and TIMESTAMP column types also support fractional seconds with the same syntax.

For more information, consult the MySQL Reference on Fractional Seconds.

Timestamp with a millisecond precision: How to save them in MySQL

You need to be at MySQL version 5.6.4 or later to declare columns with fractional-second time datatypes. Not sure you have the right version? Try SELECT NOW(3). If you get an error, you don't have the right version.

For example, DATETIME(3) will give you millisecond resolution in your timestamps, and TIMESTAMP(6) will give you microsecond resolution on a *nix-style timestamp.

Read this: https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html

NOW(3) will give you the present time from your MySQL server's operating system with millisecond precision.

If you have a number of milliseconds since the Unix epoch, try this to get a DATETIME(3) value

FROM_UNIXTIME(ms * 0.001)

Javascript timestamps, for example, are represented in milliseconds since the Unix epoch.

(Notice that MySQL internal fractional arithmetic, like * 0.001, is always handled as IEEE754 double precision floating point, so it's unlikely you'll lose precision before the Sun becomes a white dwarf star.)

If you're using an older version of MySQL and you need subsecond time precision, your best path is to upgrade. Anything else will force you into doing messy workarounds.

If, for some reason you can't upgrade, you could consider using BIGINT or DOUBLE columns to store Javascript timestamps as if they were numbers. FROM_UNIXTIME(col * 0.001) will still work OK. If you need the current time to store in such a column, you could use UNIX_TIMESTAMP() * 1000

Getting MySQL 5.7 to show milliseconds, not microseconds

This was a side effect of using the mycli MySQL command line tool. It does not occur when using standard mysql.

How to insert a microsecond-precision datetime into mysql？

MySql 5.6+ supports fractional seconds in Time Values, while previous versions don't.

A standard datetime column will not hold microsecond values, while a datetime(6) will. You can test it in MySql 5.6:

CREATE TABLE your_table (
  d1 datetime,
  d2 datetime(6)
);

INSERT INTO your_table VALUES
('2011-11-11 11:11:11.111111', '2011-11-11 11:11:11.111111');

SELECT MICROSECOND(d1) as m1, MICROSECOND(d2) as m2
FROM your_table;

m1 | m2
-----------
0  | 111111

If you are not using MySql 5.6+ I would suggest you to use two columns, one for the datetime part, and one for the microseconds:

CREATE TABLE your_table (
  dt datetime,
  us int
);

INSERT INTO your_table VALUES
('2011-11-11 11:11:11.111111', MICROSECOND('2011-11-11 11:11:11.111111'));

SqlAlchemy mysql millisecond or microsecond precision

The problem I was having is that the stock SqlAlchemy DATETIME class does not work with the mysql requirement of passing a (6) value into the constructor in order to represent fractional time values. Instead, one needs to use the sqlalchemy.dialects.mysql.DATETIME class. This class allows the use of the fsp parameter (fractional seconds parameter.) So, a column declaration should actually look like:

dateCreated = Column(DATETIME(fsp=6)) 

Thanks to those others who replied. It helped guide my investigation to ultimately stumbling over this esoteric and very confusing distinction.

Rounding Microseconds to Milliseconds MySQL

Try this query:

SELECT FROM_UNIXTIME(TO_SECONDS(dt) -
                     TO_SECONDS('1970-01-01 00:00:00') +
                     (ROUND(MICROSECOND(dt) / 1000, 0) / 1000)) AS new_dt
FROM yourTable

Here dt is your datetime column. For the sake of explanation, I will break down the above query using 2016-12-01 12:30:01.122456 as an example.

MICROSECOND(dt) = 122456
ROUND(MICROSECOND(dt) / 1000, 0) = 122

then

(ROUND(MICROSECOND(dt) / 1000, 0) / 1000)) = 0.122

So 0.122 is the number of fractional seconds in your datetime, which doesn't contain any precision in the microsecond range after rounding.

TO_SECONDS(dt) = # seconds EXCLUDING the fractional part

Finally, we take the number of seconds in dt since year 0 and subtract off the number of seconds from year 0 to the start of the epoch. This leaves us with number of seconds in dt since the start of the epoch. Then, we add the number of seconds excluding the fractional part to 0.122 to obtain the total number of seconds of the desired new datetime. At last, we use FROM_UNIXTIME() to convert this number of seconds back to a bona fide datetime.

datetime doesn't save decisecond

That's because T is not part of a valid mysql timestring. MySQL's date/time string format has always been

yyyy-mm-dd hh:mm:ss

and now that fractional seconds are supported

yyyy-mm-dd hh:mm:ss.sssss

Nowhere in that format is there ANY support for T. You'll have to use a STR_TO_DATE() to parse your "non-standard" format into something MySQL will accept. Or even simply just use a string replacement and do REPLACE('yourstring', 'T', ' ')

---

Related Topics