11

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?

Community
  • 1
  • 1

4 Answers4

16

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • I'm using PHP to do all sorting re http://highscalability.com/blog/2010/3/23/digg-4000-performance-increase-by-sorting-in-php-rather-than.html. 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? –  Jan 08 '13 at 01:55
  • also, is there a size difference between varchar and decimal? is varchar 15 = decimal 14,4? Thanks again for your extensive knowledge! –  Jan 08 '13 at 02:04
  • 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. – O. Jones Jan 08 '13 at 12:48
  • I like the way you talk, Sir. – Aditya M P Oct 02 '14 at 14:51
  • 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`? – user151841 Aug 21 '15 at 14:49
1

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.

Nic
  • 581
  • 2
  • 11
  • Is that the max length that `microtime(true)` will output? –  Jan 08 '13 at 00:06
  • 1
    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).` – Nic Jan 08 '13 at 00:09
  • Heh, bear with me because this isn't my area of expertise. Could you post the lengths for both 32 and 64 bit? –  Jan 08 '13 at 00:15
  • 1
    This is a) not that easy because there are different representations of a float and b) it`s 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. – Nic Jan 08 '13 at 00:24
  • 1
    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 Jan 08 '13 at 00:53
1

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).

Ilmari Karonen
  • 49,047
  • 9
  • 93
  • 153
-1

Why don't you just store it as a float? That's what microtime(true) returns, so it's the best candidate.

Tom van der Woerdt
  • 29,532
  • 7
  • 72
  • 105
  • 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? –  Jan 08 '13 at 00:00
  • 2
    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. – Tom van der Woerdt Jan 08 '13 at 00:37
  • 2
    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 Jan 08 '13 at 00:55
  • 2
    Sorry, gotta downvote this, because MySQL `FLOAT` is 32-bit, where PHP `float` is system dependent but often is 64-bit. – O. Jones Jan 08 '13 at 01:36