How to retrieve microseconds or milliseconds from MySQL current time?
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...
