具有毫秒精度的时间戳:如何将它们保存在 MySQL 中
我必须使用 MySQL 开发应用程序，并且必须保存诸如1412792828893"之类的值，这些值表示时间戳，但精度为毫秒.也就是说，自 1.1.1970 以来的毫秒数.我将该行声明为 timestamp 但不幸的是这不起作用.所有值都设置为 0000-00-00 00:00:00
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 timestamp but unfortunately this didn't work. All values are set to 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?
推荐答案
您需要使用 MySQL 5.6.4 或更高版本才能声明具有小数秒时间数据类型的列.不确定您的版本是否正确?试试 SELECT NOW(3).如果出现错误，则说明版本不正确.
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.
例如，DATETIME(3) 将为您提供毫秒分辨率的时间戳，而 TIMESTAMP(6) 将为您提供 *nix 样式时间戳的微秒分辨率.
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.
阅读:https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html
NOW(3) 将为您提供 MySQL 服务器操作系统的当前时间，精度为毫秒.
NOW(3) will give you the present time from your MySQL server's operating system with millisecond precision.
如果您距离 Unix 纪元 有几毫秒，请尝试使用此方法获取 DATETIME(3) 价值
If you have a number of milliseconds since the Unix epoch, try this to get a DATETIME(3) value
FROM_UNIXTIME(ms * 0.001)
Javascript 时间戳，用于例如，从 Unix 纪元开始以毫秒为单位.
Javascript timestamps, for example, are represented in milliseconds since the Unix epoch.
(请注意，MySQL 内部小数运算，如 * 0.001，始终作为 IEEE754 双精度浮点数处理，因此在太阳变成白矮星之前您不太可能失去精度.)
(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，并且需要亚秒级时间精度，那么最好的方法是升级.其他任何事情都会迫使您做出混乱的解决方法.
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.
如果由于某种原因无法升级，您可以考虑使用 BIGINT 或 DOUBLE 列来存储 Javascript 时间戳，就好像它们是数字一样.FROM_UNIXTIME(col * 0.001) 仍然可以正常工作.如果您需要将当前时间存储在这样的列中，您可以使用 UNIX_TIMESTAMP() * 1000
If, for some reason you can't upgrade, you could consider using BIGINT or DOUBLE columns 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
相关文章