毫秒精度的时间戳：如何将它们保存在 MySQL 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26299149/
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
Timestamp with a millisecond precision: How to save them in MySQL
提问by Luixv
I have to develop a application using MySQL and I have to save values like "1412792828893" which represent a timestamp but with a precision of a millisecond. That is, the amount of milliseconds since 1.1.1970. I declare the row as timestampbut unfortunately this didn't work. All values are set to 0000-00-00 00:00:00
我必须使用 MySQL 开发一个应用程序，我必须保存诸如“1412792828893”之类的值，这些值表示时间戳但精度为毫秒。即，自 1.1.1970 以来的毫秒数。我将该行声明为，timestamp但不幸的是这不起作用。所有值都设置为0000-00-00 00:00:00
CREATE TABLE IF NOT EXISTS `probability` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`segment_id` int(11) NOT NULL,
`probability` float NOT NULL,
`measured_at` timestamp NOT NULL,
`provider_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ;
How should be the declaration in order to be able to save timestamp values with this precision?
为了能够以这种精度保存时间戳值，声明应该如何？
回答by O. Jones
You need to be at MySQL version 5.6.4 or later to declare columns with fractional-second time datatypes. Not sure you have the right version? Try SELECT NOW(3). If you get an error, you don't have the right version.
您需要使用 MySQL 5.6.4 或更高版本才能声明具有小数秒时间数据类型的列。不确定您的版本是否正确？试试SELECT NOW(3)。如果出现错误，则说明您没有正确的版本。
For example, DATETIME(3)will give you millisecond resolution in your timestamps, and TIMESTAMP(6)will give you microsecond resolution on a *nix-style timestamp.
例如，DATETIME(3)将在时间戳中为您提供毫秒分辨率，并TIMESTAMP(6)在 *nix 样式时间戳上为您提供微秒分辨率。
Read this: https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html
阅读：https: //dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html
NOW(3)will give you the present time from your MySQL server's operating system with millisecond precision.
NOW(3)将以毫秒精度为您提供 MySQL 服务器操作系统的当前时间。
If you have a number of milliseconds since the Unix epoch, try this to get a DATETIME(3) value
如果自Unix 纪元以来您有许多毫秒，请尝试此操作以获取 DATETIME(3) 值
FROM_UNIXTIME(ms * 0.001)
Javascript timestamps, for example, are represented in milliseconds since the Unix epoch.
例如，Javascript 时间戳以Unix 纪元以来的毫秒数表示。
(Notice that MySQL internal fractional arithmetic, like * 0.001, is always handled as IEEE754 double precision floating point, so it's unlikely you'll lose precision before the Sun becomes a white dwarf star.)
（请注意，MySQL 内部小数运算，例如* 0.001，始终作为 IEEE754 双精度浮点处理，因此在太阳成为白矮星之前您不太可能失去精度。）
If you're using an older version of MySQL and you need subsecond time precision, your best path is to upgrade. Anything else will force you into doing messy workarounds.
如果您使用的是旧版本的 MySQL 并且需要亚秒级的时间精度，那么最好的方法是升级。其他任何事情都会迫使你做一些凌乱的变通方法。
If, for some reason you can't upgrade, you could consider using BIGINTor DOUBLEcolumns to store Javascript timestamps as if they were numbers. FROM_UNIXTIME(col * 0.001)will still work OK. If you need the current time to store in such a column, you could use UNIX_TIMESTAMP() * 1000
如果由于某种原因您无法升级，您可以考虑使用BIGINT或DOUBLE列来存储 Javascript 时间戳，就好像它们是数字一样。 FROM_UNIXTIME(col * 0.001)仍然可以正常工作。如果您需要将当前时间存储在这样的列中，您可以使用UNIX_TIMESTAMP() * 1000
回答by M Shafaei N
You can use BIGINT as follows:
您可以按如下方式使用 BIGINT：
CREATE TABLE user_reg (
user_id INT NOT NULL AUTO_INCREMENT,
identifier INT,
phone_number CHAR(11) NOT NULL,
verified TINYINT UNSIGNED NOT NULL,
reg_time BIGINT,
last_active_time BIGINT,
PRIMARY KEY (user_id),
INDEX (phone_number, user_id, identifier)
);
