DECIMAL length for microtime(true)?

Question

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?

Solution 1

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.

Solution 2

In MySQL 5.6.4 and above, the native DATETIME and TIMESTAMP types can support fractional seconds. Thus, you can store a timestamp with microsecond resolution in a DATETIME(6) or a TIMESTAMP(6) column.

To convert PHP microtime() return values to MySQL datetime format, you can use the MySQL FROM_UNIXTIME() function or, if you're using the PHP DateTime class, DateTime::format(). Note that the PHP date() function does not currently support microsecond timestamps. (It does have a format code for microseconds, u, but it always maps it to 000000.)

For older MySQL versions, which cannot store microseconds in their native datetime types, you should use either DOUBLE, BIGINT (with the values expressed in microseconds, i.e. multiplied by 1,000,000) or DECIMAL(16,6) (which should be enough for a few hundred years yet).

Solution 3

That depends on the precision you need. If milliseconds are enough for you, and you don't expect any runtimes larger than 999999 seconds you could use DECIMAL(10,4). You can fit it to your needs this way.

About the theoretical maximum, it is limited by the size of a float on your system (32bit, 64bit). See PHP Float for details. But as I said, that is theoretical. No timer will give you the time in that precision.

Comments

[Admin]

I know, but the link in my q says that float approximates where decimal seems to only truncate. I'd rather store the whole thing. Should it be a double?

[Admin]

Is that the max length that microtime(true) will output?

[Nic]

I updated my answer. PHP Manual says: The size of a float is platform-dependent, although a maximum of ~1.8e308 with a precision of roughly 14 decimal digits is a common value (the 64 bit IEEE format).

[Admin]

Heh, bear with me because this isn't my area of expertise. Could you post the lengths for both 32 and 64 bit?

[Nic]

This is a) not that easy because there are different representations of a float and b) its a really large number. As I said it's theoretical, and no timer will give you the time in that precision. You should go with what @SamuelCook suggested or add a few more digits to be on the save side` if you feel like you have to.

[Tom van der Woerdt]

There's no rounding issue with floats if the source is already a float. Storing it as a decimal number will, in fact, only cause more rounding trouble.

[O. Jones]

PHP float's precision is considered to be platform-dependent, but it is usually 64-bit IEEE 754. MySQL's FLOAT data type is 32-bit IEEE 754. If you convert a 64-bit float to a 32-bit float, you will lose precision. UNIX timestamps as of now use 31 bits to represent integral seconds. So, to represent a seconds/microseconds time stamp in MySQL IEEE 754 floating point requires DOUBLE.. FLOAT will lose precision.

[O. Jones]

IEEE 754 32-bit float (MySQL FLOAT) doesn't have enough precision to handle μsec timestamps. MySQL double has plenty of precision. It'll keep ya until the sun turns into a red giant. The MySQL spec says "For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of digits." In other words, the numbers in parentheses after FLOAT or DOUBLE declarations don't do much of anything except lull the programmer into thinking they understand something they don't.

[O. Jones]

Sorry, gotta downvote this, because MySQL FLOAT is 32-bit, where PHP float is system dependent but often is 64-bit.

[Admin]

I'm using PHP to do all sorting re highscalability.com/blog/2010/3/23/…. Please don't try to tell me how bad this idea is. I've reduced my local wampserver wait time from ~40ms to now consistently <10ms by using PHP only for calculation. In this case, I'm handling time differences with PHP's microtime. Thank you for the precision notes! Should I store in varchar since PHP seems to convert everything to string when querying?

[Admin]

also, is there a size difference between varchar and decimal? is varchar 15 = decimal 14,4? Thanks again for your extensive knowledge!

[O. Jones]

Timestamps stored in 64-bit float data items (PHP float, MySQL double)will sort into order correctly. It's numerical equality that gets squirrely with floating point.

[Aditya M P]

I like the way you talk, Sir.

[user151841]

If I'm using a micro-second timestamp purely for sorting (ORDER BY) log transactions where events happen in sub-second time, does it matter if I use DOUBLE or BIGINT?