精度为毫秒的时间戳: 如何在 MySQL 中保存它们

我必须使用 MySQL 开发一个应用程序，并且必须保存像“1412792828893”这样的值，这些值表示一个时间戳，但精度为毫秒。也就是说，自1970年1.1以来的毫秒数。我声明该行为 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`)
) ;

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

178199 次浏览
小开
最佳答案

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: 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.

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

小开

You can use BIGINT as follows:

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)
);
小开 
CREATE TABLE fractest( c1 TIME(3), c2 DATETIME(3), c3 TIMESTAMP(3) );


INSERT INTO fractest VALUES
('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777');
小开

please create the table like this by mentioning the length (length can be whatever digit count that you want by milliseconds) of timestamp timestamp(2) or timestamp(3) or timestamp(5) likewise. the Mysql version should be 5.6 or above. https://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

CREATE TABLE IF NOT EXISTS `probability` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`measured_at` timestamp(2) NOT NULL,
PRIMARY KEY (`id`)
) ;

and then pass the timestamp through Java.

statement.setTimestamp(2, new Timestamp(new Date().getTime()))

提交答案