如何从 MySQL 当前时间检索微秒或毫秒？

How to retrieve microseconds or milliseconds from MySQL current time?

提问by Please_Dont_Bully_Me_SO_Lords

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

我正在尝试在 MySQL 上创建我的第一个存储函数。在这个函数中，我想用 3 微秒数字返回当前日期和时间的时间戳，如下所示：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));

所以我第一次尝试是 SELECT CAST(MICROSECOND(NOW()) AS CHAR(3));

But it returns 0.

但它返回0。

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

如果我尝试 SELECT CAST(MICROSECOND('2009-12-31 23:59:59.001210') AS CHAR(3));

It returns 121, as I need.

根据我的需要，它返回 121。

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

那么，如何告诉 MySQL 我想知道当前时间的微秒呢？

---

---

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'):

将来自 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.

其中 666 将是一个随机数，但我希望它是当前时间的真实毫秒数，所以我没有可能的重复密钥。

---

---

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

如果我能SHOW COLUMNS FROM @TableName WHERE FIELD_NAME LIKE '%_ID' LIMIT 1在非动态 SELECT 中使用并插入它来获取该表的最后一条记录的毫秒数就好了……

回答by ali

MySQL 5.6 supports the millisecond precision in the sysdatefunction.

MySQL 5.6 支持sysdate函数中的毫秒精度。

try

尝试

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

select sysdate(6)将返回 2013-04-16 13:47:56.273434

and

和

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

select sysdate(3)将返回 2013-04-16 13:47:56.273

回答by artahian

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

看看 MySQL 怎么说（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.

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.

因此，您不需要将其存储为日期值，而是将其存储为简单的浮点值。

回答by javamonk

For mysql 5.6

对于 mysql 5.6

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

回答by workplaylifecycle

Also you can

你也可以

mysql> select now(3) as millisecond, now(6) as microsecond, round(1000 * unix_timestamp(now(3))) elapsed_millisecond, round(unix_timestamp() * 1000  + MICROSECOND(now(6)) / 1000) elapsed_microsecond;
+-------------------------+----------------------------+---------------------+---------------------+
| millisecond             | microsecond                | elapsed_millisecond | elapsed_microsecond |
+-------------------------+----------------------------+---------------------+---------------------+
| 2019-12-10 11:49:43.568 | 2019-12-10 11:49:43.568767 |       1575949783568 |       1575949783569 |
+-------------------------+----------------------------+---------------------+---------------------+
1 row in set (0.01 sec)

回答by Sueb Tarasiri

This is my Solution for Millisecond management.

这是我的毫秒管理解决方案。

I just use "text" data type as data store, of cause you have to control data validation by yourself.

我只是使用“文本”数据类型作为数据存储，因为您必须自己控制数据验证。

CREATE TABLE time_test( idINT NOT NULL AUTO_INCREMENT , start_timeTEXT NULL , stop_timeTEXT NULL , differenceTEXT NULL , rankingINT NULL , PRIMARY KEY ( id) )

创建表 time_test（ idINT NOT NULL AUTO_INCREMENT、 start_timeTEXT NULL、 stop_timeTEXT NULL、 differenceTEXT NULL、 rankingINT NULL、PRIMARY KEY ( id)）

INSERT INTO time_testVALUES (NULL, '10:10:10.111111', '10:10:10.456789',NULL,NULL) INSERT INTO time_testVALUES (NULL, '10:10:10.111111', '10:10:20.777777',NULL,NULL) INSERT INTO time_testVALUES (NULL, '10:10:10.111111', '10:10:01.999999',NULL,NULL)

INSERT INTO time_testVALUES (NULL, '10:10:10.111111', '10:10:10.456789',NULL,NULL) INSERT INTO time_testVALUES (NULL, '10:10:10.111111', '10:10:10.456789',NULL) NULL) 插入time_test值 (NULL, '10:10:10.111111', '10:10:01.999999',NULL,NULL)

Now you can calculate like this Now you can calculate like this sorting SELECT * FROM time_testORDER BY TIME( stop_time )

现在你可以这样计算现在你可以这样计算 SELECT * FROM time_testORDER BY TIME( stop_time )

Calculate time diff, very good for me. SELECT *, TIMEDIFF(stop_time,start_time) from time_test
Calculate time diff, very good for me.

计算时间差异，对我来说非常好。SELECT *, TIMEDIFF(stop_time,start_time) from time_test
Calculate time diff，对我来说非常好。

also can calcuate and store back you can cut some string by yourself. update time_testset difference = concat(TIMEDIFF(stop_time,start_time), MICROSECOND(TIMEDIFF(stop_time,start_time) )) also can calcuate and store back you can cut some string by yourself.

也可以计算和存储你可以自己剪一些字符串。update time_testset difference = concat(TIMEDIFF(stop_time,start_time), MICROSECOND(TIMEDIFF(stop_time,start_time))) 也可以计算和存储回来你可以自己剪一些字符串。

you also doing ranking by this command: SET @r:=0;
UPDATE time_testSET ranking= (@r:= (@r+1)) ORDER BY differenceASC; you also doing ranking by this command:

您还可以通过此命令进行排名：SET @r:=0;
UPDATE time_testSET 排名= (@r:= (@r+1)) ORDER BY differenceASC; 您还通过此命令进行排名：