How to create Mysql unix_timestamp with millisecond

Question

I have a php function using mysql query UNIX_TIMESTAMP() to create a unique transaction number, but I found that if the function loop in same time, it will generate the same transaction number for Case 1, using Case 2 is not a good way for huge loop.

1.I am using transaction number is for sorting same transaction date in table list.

2.My transaction table is using UUID as PK, because data cross DB.

Desire answer is like below and unique.

TransNo = 1397533879.20290

PHP Version 5.3

Mysql Version 5.5

E.g. Function Loop 3 times

Case 1 :

SELECT UNIX_TIMESTAMP() as TransNo;

Output : 
1397533879
1397533879
1397533879

Case 2 :

SELECT UNIX_TIMESTAMP() as TransNo, SLEEP(1);

Output :
1397533879
1397533880
1397533881

Ohya, thank @Joshua Bixler, I forgot php have this functions. But any way, did the mysql able to do that? — Cheong BC, Apr 15 '14 at 04:30
http://stackoverflow.com/questions/8979558/mysql-now-function-with-high-precision Have to jump up version 5.6 to use it looks like. — Joshua Bixler, Apr 15 '14 at 04:35

Ravinder Reddy · Accepted Answer · 2014-04-15T05:18:37.270

Desire answer is like below and unique.

TransNo = 1397533879.20290

If you call current_timestamp or now functions with a precision for microseconds part, then they return a timestamp that includes current micro seconds of time. Please note that Maximum precision allowed is for 6 digits only.

See Documentation on Fractional Seconds in Time Values

Example 1:

select 
    @ts:=current_timestamp( 3 ) cts_with_milli_seconds
  , unix_timestamp( @ts ) uts_with_ts_milli_seconds;

Result 1:

+-------------------------+---------------------------+
| cts_with_milli_seconds  | uts_with_ts_milli_seconds |
+-------------------------+---------------------------+
| 2014-04-15 10:22:17.764 |         1397537537.764000 |
+-------------------------+---------------------------+

Example 2:

select 
    @nw:=now( 6 ) now_with_milli_seconds
  , unix_timestamp( @nw ) uts_with_nw_milli_seconds;

Result 2:

+----------------------------+---------------------------+
| now_with_milli_seconds     | uts_with_nw_milli_seconds |
+----------------------------+---------------------------+
| 2014-04-15 10:22:17.789248 |         1397537537.789248 |
+----------------------------+---------------------------+

Side note:
If still microseconds part too matches, then you can append RAND() to your values.

Usage of RAND() will not guarantee in sorting order. Hence, unless you need just a unique output, don't use it.

Thanks @Ravinder, I am using php microtime() provide by Joshua Bixler, it result as 1397537683.3391, 1397537683.3429, 1397537683.3456, it can solve my sorting problem, if using RAND() cannot guarantee in order insert. — Cheong BC, Apr 15 '14 at 05:06
`RAND()` suggestion was in case of micro part matching. And yes, on usage, sorting may fail. Otherwise my answer is as helpful as what is in referred posting. — Ravinder Reddy, Apr 15 '14 at 05:17

How to create Mysql unix_timestamp with millisecond

1 Answers1