CODEWITHSUNDEEP
phpmysqlmysqli
Cheong BC
Cheong BC

Reputation: 316

How to create Mysql unix_timestamp with millisecond

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

Upvotes: 0

Views: 1101

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

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.

Upvotes: 1

Related Questions