43

So I just found the most frustrating bug ever in MySQL.

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

So I am using PHP and Doctrine, and I really need those microseconds (I am using the actAs: [Timestampable] property).

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

I also saw something about compiling a UDF extension. This is not an acceptable because I or a future maintainer will upgrade and poof, change gone.

Has anyone found a suitable workaround?

Byron Whitlock
  • 52,691
  • 28
  • 123
  • 168

8 Answers8

38

For information for the next readers, this bug has finally be corrected in version 5.6.4:

"MySQL now supports fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microsecond precision."

Xavier Portebois
  • 3,354
  • 6
  • 33
  • 53
  • 3
    Read up everything that is new in 5.6.4 using the link above ... but if you are looking for just how to do fractional seconds, read: http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html. Basically instead of DATETIME (or TIMESTAMP), do DATETIME(6) (or TIMESTAMP(6)) or another precision. – Ephraim Apr 30 '13 at 06:54
  • The really stupid mistake what the mysql guys made, is that they made things backwards incompatible. Or at least when accessing it through Java. Feeding it a timestamp that has fractions of a second, causes the datetime field to come out with all 0s. They should have made millisecond precision an optional add on that you don't see unless you ask for it. Currently this is costing companies hugely because of the compatibility they causes. A very poor and irresponsible reckless mistake on their part. A good plan is for future systems to not use mysql. Probably PostgreSQL might be a better choice. – Mike Jan 29 '16 at 18:18
  • @Mike sounds like a problem with the Java access. The default behavior is to discard milliseconds (by default, columns don't store milliseconds, unless you change their specification to do so). Its possible you've encountered a bug rather than a fundamental mistake in their upgrade design. Maybe there's some way to work around it? Are there any Q & A's here that refer to such problems? – ToolmakerSteve Apr 13 '17 at 13:45
  • @ToolmakerSteve The problem happens when all you change, is the mysql version. The Java connector stayed the same. The work around is to clear out the millisecond part of any date object you feed it. Like date.setMilliseconds(date.getMilliseconds() / 1000 * 1000). – Mike Apr 25 '17 at 02:09
29

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.

Michael Konietzka
  • 5,419
  • 2
  • 28
  • 29
  • 5
    But every other RDBMS supports millisecond or higher precision in their timestamp fields and time functions (GETDATE() NOW() etc). +1 for being technically correct about SQL-92 – Byron Whitlock Apr 04 '10 at 00:42
9

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();
            }
        }
    }
}
Byron Whitlock
  • 52,691
  • 28
  • 123
  • 168
  • You are right about this solution to be working. However, the lookup and comparison times (for strings) will be much longer than comparing integers. – Etamar Laron Apr 08 '10 at 19:32
  • How so if the field is indexed? – Byron Whitlock Apr 08 '10 at 23:08
  • 4
    An integer is simple, much less data to deal with in every case, "Hi There" is much more expensive to process than 1871239471294871290843712974129043192741890274311234 is. Integers are much more scalable, especially when being used in comparisons, more CPU cycles per comparison for anything but the shortest string. Indexing helps, but it doesn't change that you're dealing with more data on the comparisons, at least at some point. – Nick Craver Apr 09 '10 at 10:49
  • 6
    -1 Storing dozens of bytes rather than 4 is going to bloat your indexes, murdering insert and lookup speed for anything but toy projects. Also, all of those coercions are expensive, CPU-wise. Finally, string coercion does *not* provide one-to-one equivalence with native time types (for example, I can subtract one timestamp from another, but if I do this for their string representations, I always get zero. Given all of these caveats, it's simpler, safer, and faster to just call a spade a spade and store millisecond-precision timestamps in an appropriately named BIGINT column. – user359996 Nov 08 '11 at 17:07
  • Makes me wonder if this code could be made to work with float values rather than strings.... You'd probably need to use the `DateTime` class when formatting the values, as some native datetime functions are not microsecond-aware (`strtotime()` comes to mind). –  May 07 '12 at 18:36
4

From Mysql version 5.6.4 onward ,It stores microsecond in a column.

"MySQL now supports fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microsecond precision."

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;

Only needs to change datatype to datetime to datetime(6);

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

Voitcus
  • 4,463
  • 4
  • 24
  • 40
Rahul More
  • 41
  • 2
  • And are you saying you don't need any changes to Doctrine? – malhal Jul 06 '13 at 20:01
  • 1
    Downside is that you can not use ALTER TABLE table CHANGE COLUMN timestamp_column TIMESTAMP(6) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP You will have to change it to be CURRENT_TIMESTAMP(6) – Will B. Jan 19 '14 at 05:04
4

As you're using Doctrine to store data, and Doctrine does not support fractional seconds either, then the bottleneck is not MySQL.

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

I often recommend storing epoch seconds instead of native timestamp types as they are usually easier to manipulate and avoid the whole time zone issue you keep getting into with native time types and providing service internationally.

Guss
  • 30,470
  • 17
  • 104
  • 128
3

Another workaround for Time in milliseconds. Created function "time_in_msec"

USAGE :

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 ;
Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
Bijumon K N
  • 31
  • 1
2

Now you can use micro seconds 

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)
m00am
  • 5,910
  • 11
  • 53
  • 69
DehuaYang
  • 21
  • 1
  • 4
1

As mentioned microsecond support was added in version 5.6.4

Perhaps the following is of use for fractional seconds:

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 view of it:

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 entitled Fractional Seconds in Time Values

Drew
  • 24,851
  • 10
  • 43
  • 78
  • I don't know if I messed up the Test above with base 60 versus base 100, something else, or if they are spot on. Perhaps some peer could gander at that. Thx. Anyway, I just think this dupe target is missing some modern fractional second work. – Drew Oct 04 '16 at 19:53