CODEWITHSUNDEEP
mysql
user127
user127

Reputation: 111

Duplicate MySQL UNIX_TIMESTAMP() result on successive 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;

Upvotes: 4

Views: 254

Answers (1)

spencer7593
spencer7593

Reputation: 108370

The FLOAT datatype only has 23-bits for the mantissa, which is essentially equivalent to seven decimal digits of precision. (Yes, technically, the limit is more accurately specified as six digits.) But the point is that a FLOAT represents no more than seven decimal digits of precision.

And right now, UNIX_TIMESTAMP() function is returning an integer value of ten decimal digits. So, a one second change is going to change only the 10th (lowest order) decimal digit (or rollover a 9 to a 0). But that isn't enough difference to result in a different FLOAT value. (Eventually, the value of the FLOAT will change, but any one second difference is very unlikely to result in a different FLOAT value.)

I recommend you try using BIGINT, DECIMAL(10,0) or TIMESTAMP datatype, or use a DOUBLE if you need(?) the scalability of a floating point type.

Excerpted from the MySQL Documentation

<snip>

Functions that return the current date or time each are evaluated only once per query at the start of query execution. This means that multiple references to a function such as NOW() within a single query always produce the same result. (For our purposes, a single query also includes a call to a stored program (stored routine, trigger, or event) and all subprograms called by that 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 stored program, since the DECLARE statement is valid only within a stored program.)

Upvotes: 2

Related Questions