7

I'm storing process time in a MySQL database as a float(4,4).

$start_time = microtime( TRUE );

// things happen in my script

$end_time = microtime( TRUE );
$process_time = $end_time - $start_time;

// insert $process time into mysql table

$process_time always displays correctly when outputted to the command line, but if it's value is greater than 1, it stores into mysql as .9999.

What gives?

T. Brian Jones
  • 11,630
  • 19
  • 67
  • 109
  • I don't quite understand: Do you store start and end time or the difference of both? And how do you compute the difference: In PHP or Mysql? – soulmerge Nov 11 '11 at 09:34
  • 2
    A note on comparing float values: http://stackoverflow.com/questions/3148937/compare-floats-in-php – Treffynnon Nov 11 '11 at 09:34

4 Answers4

16

float(4,4) means total 4 digits, 4 of them are after the decimal point. So you have to change to 10,4 for example

MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)” means than values can be stored with up to M digits in total, of which D digits may be after the decimal point.

ypercubeᵀᴹ
  • 105,605
  • 14
  • 160
  • 222
Maxim Krizhanovsky
  • 24,757
  • 5
  • 49
  • 85
7

From the MySQL Numeric Types page:

MySQL permits a nonstandard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)” means than values can be stored with up to M digits in total, of which D digits may be after the decimal point. For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

float(4,4) means a 4 digit number, with all 4 digits to the right of the decimal point; 0.9999 is the biggest number it can hold.

Phil Lello
  • 7,569
  • 2
  • 21
  • 32
2

This isn't directly an answer to your question, but you shouldn't use floats for that. Rounding issues are well known for floats. Use a decimal if you want precision.

CodeCaster
  • 131,656
  • 19
  • 190
  • 236
  • 1
    Floats are quite perfect for storing times, as they have an accuracy, depending on their precision, of 1E-5 (single) or 1E-10 (double) (Values are all IIRC, but you should get the idea). So it doesn't hurt to store values with a precision of 1E-3 in a float. It just should be possible to store values greater or equal to 1 in such a variable. – glglgl Nov 11 '11 at 09:47
2

It's because of the values you're passing in. You're allowing 4 digits after the decimal point, but only 4 digits in total so the maximum it can store is .9999. Change it to float(5,4) to save it correctly, or increase the 5 if you think you'll need an even greater number.

Dave Starling
  • 66
  • 4