Why Doesn’t MySQL support Millisecond DATETIME Resolution?

Apparently, there’s been an outstanding bug for nearly two years for MySQL to add support for millisecond storage in DATETIME and TIME data types.

A microseconds part is allowable in temporal values in some contexts, such as in literal values, and in the arguments to or return values from some temporal functions. Microseconds are specified as a trailing .uuuuuu part in the value. Example:

However, microseconds cannot be stored into a column of any temporal data type. Any microseconds part is discarded.

What’s this about? I have to admit that I’ve known about this problem for about the same amount of time (probably three years).

At Rojo we used BIGINTs as timestamps which provided millisecond resolution since they were 64bits. The problem is that it made date manipulation harder since MySQL didn’t realize they were timestamps. Having MySQL pretty print the values on the console made my job a lot easier now that we’re using DATETIME and TIMESTAMP at Tailrank.

Why are these values truncated at milliseconds?

It seems that in DATETIME they’re using 8 bytes for storage but not using the milliseconds value to extend the range:

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

You can store 73k years in 64bit space (before and after an epoch of Jan 1, 1970) so this can’t be the problem. The issue seems to be that internally MySQL is storing the value as it would be represented in the gregorian calendar (for example an application that stored martian lunar data).

Of course this is clearly non-optimal because this isn’t a very efficient storage mechanism. For example the month value can only go up to 12 and not 99 so you’re missing out on a huge address space range.

Is there an inherit optimization I’m missing out on here? It seems one major reason that this bug hasn’t been fixed could be that internally MySQL date/time functions use this time string format.

One thing I don’t understand is that DATETIME uses 8 bytes of storage but TIMESTAMP only uses 4 bytes? The TIMESTAMP documentation also does not state the range of the TIMESTAMP type. Can it store values up to ‘9999-12-31 23:59:59’ ? It wouldn’t be able to if stored in a string format.

Unfortunately this exercise leaves me with more questions than answers.

  1. Hi!

    We suck :)

    Seriously, its a can of worms, which is why we have not added milliseconds. Milliseconds either means we have to come up with a new field type, which is dumb, or find a way to upgrade the current one (which is a pain to most of the engine writers and probably will require dumping/loading which we hate (and you would hate)).

    I am against new field types just because old field types hit limits. Unless there is an object reason (and we don’t support objects in the database) I am for less, and I mean far less, types. Frankly I think we have too many as is.

    We have to do it though, its just becoming to painful to not do it. It means fixing a lot of code, and frankly it would be nicer if we had better concepts of versioning internally (which we will have to do long term to be an “online” database).

    If you want to know more about fields, study sql/field.h.


  2. Thanks Brian.

    To be honest this is exactly what I figured……

    I think we’re just going to have to suck it up and represent our timestamps as 64bit values since epoch.

    There’s also a secondary problem I want to solve which wouldn’t be fixed by just using milliseconds.


  3. The previous section of the manual (relative to the one you quoted) states:

    “Remember that although DATETIME, DATE, and TIMESTAMP values all can be specified using the same set of formats, the types do not all have the same range of values. For example, TIMESTAMP values cannot be earlier than 1970 or later than 2038.”

    So the MySQL TIMESTAMP is basically Unix time, which explains its storage size.

  4. Yeah, TIMESTAMP is just the number of seconds since the epoch. DATETIME is an integer AFAIK, but it looks like 20070530113245 for example — in other words, take ISO-8601 time and strip out all non-digits, interpret the result as an integer, and I think you have DATETIME. DATE works the same way I think — today is the unsigned 3-byte value 20070530.

    This does give some nice properties, like easy comparisons, but as I’m sure we all know it is a pain in other ways. Years ago I was doing some date/time modules of my own, and thought I’d take a look at the MySQL source code to see how the calculations are done. Wow, did that confuse me.

    Caveat — I haven’t read the relevant source in years, so my memory could be totally whacked :)

  5. atfrase

    So, it’s clear that TIMESTAMP cannot be upgraded since it is already at its limit with a densely-packed storage scheme. A TIMESTAMP supporting fractional seconds would either require more disk/memory space (and thus a dump/reload of affected tables) or would support a smaller overall range, neither of which is really acceptable.

    However, if DATETIME is 64 bits and currently stored in this gregorian-int scheme (9999-12-31 23:59:59 being decimal 99,991,231,235,959 for example), then it has plenty of room to add fractional seconds without requiring any additional space and without even colliding with any existing data.

    Off the top of my head I can think of three different ways to do it that would offer at least 10 microsecond resolution, wouldn’t require a global conversion or dump/reload of any tables on upgrade, and would simultaneously support (not collide with) the existing seconds-resolution format, so that the server could recognize old-format DATETIME data on-the-fly and interpret it correctly.

    I assume there must be some really dreadful gotcha in the actual code, because this seems readily doable, for DATETIMEs at least. Unless there’s a burning need for DATETIME and TIMESTAMP to store the same resolution, but then again they have different supported ranges and storage sizes as it is, so what’s one more little difference?

%d bloggers like this: