MySQL CURRENT_TIMESTAMP 以毫秒为单位
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9624284/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CURRENT_TIMESTAMP in milliseconds
提问by Marsellus Wallace
Is there any way to get milliseconds out of a timestamp in MySqlor PostgreSql(or others just out of curiosity)?
有没有办法从MySql或PostgreSql（或其他人出于好奇）的时间戳中获得毫秒数？
SELECT CURRENT_TIMESTAMP
--> 2012-03-08 20:12:06.032572
Is there anything like this:
有没有这样的：
SELECT CURRENT_MILLISEC
--> 1331255526000
or the only alternative is to use the DATEDIFFfrom the era?
或者唯一的选择是使用DATEDIFFfrom era？
采纳答案by Billy Moon
To get the Unix timestamp in secondsin MySQL:
在 MySQL 中以秒为单位获取 Unix 时间戳：
select UNIX_TIMESTAMP();
Details: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp
详情：http: //dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp
Not tested PostgreSQL, but according to this site it should work: http://www.raditha.com/postgres/timestamp.php
没有测试过 PostgreSQL，但根据这个网站它应该可以工作：http: //www.raditha.com/postgres/timestamp.php
select round( date_part( 'epoch', now() ) );
回答by Claudio Holanda
For MySQL (5.6+) you can do this:
对于 MySQL (5.6+)，您可以这样做：
SELECT ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000)
Which will return (e.g.):
哪个将返回（例如）：
1420998416685 --milliseconds
回答by jbaylina
In mysql, it is possible to use the uuid function to extract milliseconds.
在mysql中，可以使用uuid函数来提取毫秒。
select conv(
concat(
substring(uid,16,3),
substring(uid,10,4),
substring(uid,1,8))
,16,10)
div 10000
- (141427 * 24 * 60 * 60 * 1000) as current_mills
from (select uuid() uid) as alias;
Result:
结果：
+---------------+
| current_mills |
+---------------+
| 1410954031133 |
+---------------+
It also works in older mysql versions!
它也适用于较旧的 mysql 版本！
Thank you to this page: http://rpbouman.blogspot.com.es/2014/06/mysql-extracting-timstamp-and-mac.html
感谢您访问此页面：http: //rpbouman.blogspot.com.es/2014/06/mysql-extracting-timstamp-and-mac.html
回答by Ahmed
In Mysql 5.7+ you can execute
在 Mysql 5.7+ 中你可以执行
select current_timestamp(6)
for more details
更多细节
https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html
https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html
回答by Igor
The main misunderstanding in MySQL with timestamps is that MySQL by default both returns and stores timestamps without a fractional part.
在 MySQL 中使用时间戳的主要误解是MySQL 默认返回和存储没有小数部分的时间戳。
SELECT current_timestamp() => 2018-01-18 12:05:34
which can be converted to seconds timestamp as
可以转换为秒时间戳
SELECT UNIX_TIMESTAMP(current_timestamp()) => 1516272429
To add fractional part:
添加小数部分：
SELECT current_timestamp(3) => 2018-01-18 12:05:58.983
which can be converted to microseconds timestamp as
可以转换为微秒时间戳
SELECT CAST( 1000*UNIX_TIMESTAMP(current_timestamp(3)) AS UNSIGNED INTEGER) ts => 1516272274786
There are few tricks with storing in tables. If your table was created like
存储在表格中的技巧很少。如果你的表是这样创建的
CREATE TABLE `ts_test_table` (
`id` int(1) NOT NULL,
`not_fractional_timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
than MySQL will NOT store fractional part within it:
比 MySQL 不会在其中存储小数部分：
id, not_fractional_timestamp
1, 2018-01-18 11:35:12
If you want to add fractional part into your table, you need to create your table in another way:
如果要将小数部分添加到表中，则需要以另一种方式创建表：
CREATE TABLE `ts_test_table2` (
`id` int(1) NOT NULL,
`some_data` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
`fractional_timestamp` timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
that leads to required result:
这导致所需的结果：
id, some_data, fractional_timestamp
1, 8, 2018-01-18 11:45:40.811
current_timestamp() function is allowed to receive value up to 6, but I've found out (at least in my installed MySQL 5.7.11 version on Windows) that fraction precision 6 leads to the same constant value of 3 digits at the tail, in my case 688
current_timestamp() 函数允许接收最多 6 的值，但我发现（至少在我在 Windows 上安装的 MySQL 5.7.11 版本中）分数精度 6 导致尾部相同的 3 位数常量值，在我的情况下 688
id, some_data, fractional_timestamp
1, 2, 2018-01-18 12:01:54.167688
2, 4, 2018-01-18 12:01:58.893688
That means that really usable timestamp precision of MySQL is platform-dependent:
这意味着 MySQL 真正可用的时间戳精度是平台相关的：
- on Windows: 3
- on Linux: 6
- 在 Windows 上：3
- 在 Linux 上：6
回答by Falco
The correct way of extracting miliseconds from a timestamp value on PostgreSQL accordingly to current documentationis:
根据当前文档，从 PostgreSQL 上的时间戳值中提取毫秒的正确方法是：
SELECT date_part('milliseconds', current_timestamp);
--OR
SELECT EXTRACT(MILLISECONDS FROM current_timestamp);
with returns: The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.
返回：秒字段，包括小数部分，乘以 1000。请注意，这包括完整的秒数。
回答by TanuAD
Use:
用：
Select curtime(4);
This will give you milliseconds.
这会给你几毫秒。
回答by aleroot
In PostgreSQL you can use :
在 PostgreSQL 中，您可以使用：
SELECT extract(epoch from now());
on MySQL :
在 MySQL 上：
SELECT unix_timestamp(now());
回答by Rémi Becheras
None of these responses really solve the problem in postgreSQL, i.e :
这些响应都没有真正解决postgreSQL 中的问题，即：
getting the unix timestampof a date field in milliseconds
以毫秒为单位获取日期字段的unix时间戳
I had the same issue and tested the different previous responses without satisfying result.
我遇到了同样的问题并测试了不同的先前响应但没有令人满意的结果。
Finally, I found a really simple way, probably the simplest :
最后，我找到了一个非常简单的方法，可能是最简单的：
SELECT (EXTRACT (EPOCH FROM <date_column>::timestamp)::float*1000 as unix_tms
FROM <table>
namely :
即：
- We extract the pgSQL EPOCH, i.e. unix timestampin floatting secondsfrom our column casted in timestamp prudence (in some complexe queries, pgSQL could trow an error if this cast isn't explicit. See )
- then we cast it in float and multiply it by 1000 to get the value in milliseconds
- 我们从我们的列中提取 pgSQL EPOCH，即以浮动秒为单位的unix 时间戳（在一些复杂的查询中，如果此转换不明确，pgSQL 可能会引发错误。参见 ）
- 然后我们将其转换为浮点数并将其乘以 1000 以获得以毫秒为单位的值
回答by Yuval
Easiest way I found to receive current time in milliseconds in MySql:
我发现在 MySql 中以毫秒为单位接收当前时间的最简单方法：
SELECT (UNIX_TIMESTAMP(NOW(3)) * 1000)
Since MySql 5.6.
从 MySQL 5.6 开始。
