Geek Answers Handbook

Duplicate MySQL UNIX_TIMESTAMP () result on consecutive calls

Why does this code return the same UNIX_TIMESTAMP() value for each call?

 DECLARE myStamp1 FLOAT; DECLARE myStamp2 FLOAT; DECLARE n INT; SET myStamp1 = UNIX_TIMESTAMP(); SELECT SLEEP(1) INTO n; SET myStamp2 = UNIX_TIMESTAMP(); SELECT myStamp1, myStamp2;
+4
source share
1 answer

The FLOAT data type has only 23 bits for the mantissa, which is essentially equivalent to seven decimal places of precision. (Yes, technically, the limit is more accurately defined as six digits.) But the fact is that FLOAT represents no more than seven decimal digits of accuracy.

And right now, the UNIX_TIMESTAMP() function returns an integer value of ten decimal digits. Thus, a single change will only change the decimal digit of the 10th (lower order) (or upsets 9 to 0). But this is not enough to get a different FLOAT value. (In the end, the FLOAT value will change, but a difference of one second is unlikely to lead to a different FLOAT value.)

I recommend that you try using the BIGINT , DECIMAL(10,0) or TIMESTAMP data type, or use DOUBLE if you need (?) Floating point type scalability.

Excerpt from MySQL documentation

<snip>

Functions that return the current date or time, each of them is evaluated only once for each request at the beginning of the query. This means that multiple references to a function such as NOW () in a single query always give the same result. (For our purposes, a single request also includes a call to a stored program (stored procedure, trigger, or event) and all subroutines called by this program.) This principle also applies to CURDATE (), CURTIME (), UTC_DATE (), UTC_TIME (), UTC_TIMESTAMP () and to any of their synonyms.

</snip>

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

(The code you show must be in a saved program, because the DECLARE statement is valid only in a saved program.)

+2
source

More articles:


All Articles