具有毫秒精度的时间戳记：如何将它们保存在MySQL中 [英] Timestamp with a millisecond precision: How to save them in MySQL

问题描述

我必须使用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`）
）; 
  

为了能够以这种精度保存时间戳值，应该如何声明？

解决方案

您需要在MySQL版本5.6.4或更高版本中声明具有小数秒数据类型的列。不确定你有正确的版本？尝试 SELECT NOW（3）。如果您收到错误，则您没有正确的版本。

例如， DATETIME（3）将在你的时间戳中给你毫秒的分辨率，而 TIMESTAMP（6）将给你一个* nix风格时间戳的微秒分辨率。

阅读： http：//dev.mysql .com / doc / refman / 5.7 / en / fractional-seconds.html

NOW（3）将从您的MySQL服务器的操作系统以毫秒的精度为您提供现在的时间。

如果您有一个毫秒数，因为 Unix纪元，尝试获取DATETIME（3）值

  FROM_UNIXTIME（ms * 0.001）
  

Javascript时间戳例如，从 Unix纪元开始，以毫秒表示。

（请注意，MySQL内部小数算术，如 * 0.001 始终处理为IEEE754双精度浮点数，因此不太可能在太阳成为白矮星之前，会失去精度。）

如果您使用的是旧版本的MySQL，而且您需要次时间精度，则最佳路径为升级。还有一件事会强迫你做错乱的解决方法。

如果由于某种原因你无法升级，你可以考虑使用 BIGINT 或 DOUBLE 列来存储Javascript时间戳，就像它们是数字一样。 FROM_UNIXTIME（col * 0.001）仍然可以正常工作。如果您需要当前时间存储在这样的列中，可以使用 UNIX_TIMESTAMP（）* 1000

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?

解决方案

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.

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.

Read this: http://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html

NOW(3) will give you the present time from your MySQL server's operating system with millisecond precision.

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 timestamps, for example, are represented in milliseconds since the Unix epoch.

(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.)

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.

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

这篇关于具有毫秒精度的时间戳记：如何将它们保存在MySQL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！