How to retrieve microseconds or milliseconds from MySQL current time?

Question

I am trying to create my first stored function on MySQL. In this function I want to return the timestamp of the current date and time with 3 microsecond digits like this: YYYYMMDDHHMMSSZZZ

I use this number in my database to create unique keys for my records offline so they don't crash when my systems merge databases from different offline servers.

So my first try for that was SELECT CAST(MICROSECOND(NOW()) AS CHAR(3));

But it returns 0.

If I try SELECT CAST(MICROSECOND('2009-12-31 23:59:59.001210') AS CHAR(3));

It returns 121, as I need.

So, How to tell MySQL that I want to know the microseconds of the current time?

---

EDIT:

Consider this:

CREATE FUNCTION CHAVE (pTable VARCHAR(32)) RETURNS CHAR(20)
BEGIN
    DECLARE vSigla CHAR(3);
    DECLARE vDateTime CHAR(14);
    DECLARE vMilli CHAR(3);
    DECLARE vKey CHAR(20);
    SET vSigla = (SELECT SIGLA FROM TABLELIST WHERE NOME = pTable);
    SET vDateTime = (SELECT CAST(LEFT(UTC_TIMESTAMP()+0, 14) AS CHAR(14)));
    SET vMilli = LPAD(FLOOR(RAND() * 1000), 3, '0');
    SET vKey = CONCAT(vSigla, vDateTime, vMilli);
    RETURN vKey;
END;

The result of:

INSERT INTO TABLEX (dateID, name) VALUES (CHAVE('TABLEX'), 'EASI');

Will be, from CHAVE('TABLEX'):

KEY20130320151159666

Where 666 will be a random number, but I wish it was the real milliseconds count of the current time, so I have no possible duplicated key.

---

If only I could use SHOW COLUMNS FROM @TableName WHERE FIELD_NAME LIKE '%_ID' LIMIT 1 and insert that in a non-dynamic SELECT to get the millisecond of the last record of that table...

Answer 1

MySQL 5.6 supports the millisecond precision in the sysdate function.

try

select sysdate(6) will return 2013-04-16 13:47:56.273434

and

select sysdate(3) will return 2013-04-16 13:47:56.273

Answer 2

Take a look at what MySQL says(http://dev.mysql.com/doc/refman/5.1/en/fractional-seconds.html):

However, when MySQL stores a value into a column of any temporal data type, it discards any fractional part and does not store it.

So you need to store it not as a date value, but as a simple floating point value.

Answer 3

For mysql 5.6

round(unix_timestamp() * 1000  + MICROSECOND(sysdate(6)) / 1000)