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.