DECIMAL length for microtime(true)?
I want to store PHP's microtime as my timestamp in MySQL.
I've been told it's best to store it in DECIMAL, but I can't find an ideal size.
Does anyone know what the maximum size microtime(true) returns, so I can put that as my data type length?
Should I choose variable DECIMAL length?
Answer
tl;dr. Use microtime(false) and store the results in a MySQL bigint as millionths of seconds. Otherwise you have to learn all about floating point arithmetic, which is a big fat hairball.
The PHP microtime function grabs the Unix timestamp (currently about hex 50eb7c00 or decimal 1,357,609,984) from one system call, and the microsecond time from another system call. It then makes them into a character string. Then, if you call it with (true) it converts that number to a 64-bit IEEE 745 floating point number, a thing PHP calls a float.
As of today you need ten decimal digits to the left of the decimal point to store an integer UNIX timestamp. That will remain true until about 2280 CE when your descendants will start needing eleven digits. You'll need six digits to the right of the decimal place to store the microseconds.
You aren't going to get total microsecond accuracy. Most systems keep their sub-second system clock with a resolution of something in the range of 1-33 milliseconds. It's system dependent.
MySQL version 5.6.4 and later allow you to specify DATETIME(6) columns, which will hold dates and times to microsecond resolution. If you're using such a MySQL version that's absolutely the way to go.
Before version 5.6.4, you need to use MySQL DOUBLE (IEEE 754 64-bit floating point) to store these numbers. MySQL FLOAT (IEEE 754 32-bit floating point) doesn't have enough bits in its mantissa to store even the present UNIX time in seconds completely accurately.
Why are you storing these timestamps? Are you hoping to do
WHERE table.timestamp = 1357609984.100000
or similar queries to look up particular items? That is fraught with peril if you use float or double numbers anywhere in your processing chain (that is, even if you use microtime(true) even once). They are notorious for not coming up equal even when you thought they should. Instead you need to use something like this. The 0.001 ìs called "epsilon" in the numerical processing trade.
WHERE table.timestamp BETWEEN 1357609984.100000 - 0.001
AND 1357609984.100000 + 0.001
or something similar. You won't have this problem if you store these timestamps as decimals or in millionths of seconds in a bigint column.
IEEE 64-bit floating point has 53 bits of mantissa -- of precision. The present UNIX Epoch timestamp (seconds since 1-Jan-1970 00:00Z) times one million uses 51 bits. So there isn't much extra precision in the DOUBLE if we care about the low-order bit. On the other hand, the precision won't run out for a couple of centuries.
You are nowhere near running out of precision with int64(BIGINT). If I were actually storing microsecond timestamps just for the ordering of them in MySQL, I'd go with DATETIME(6) because I'd get lots of date arithmetic for free. If I were doing an in-memory high volume app, I'd use int64.