Timestamp with a millisecond precision: How to save them in MySQL

Question

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?

Affecting

What version of MySQL? – Faceplate

mysql Ver 14.14 Distrib 5.6.11, for Win32 (x86) – Affecting

Looks like you're in luck. – Faceplate

Do you have the values in hand as milliseconds prior to insertion? If so, why not store directly as BIGINT? – Bobsledding

Answer 1

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

Uncertain

I've changed my definition to timestamp(6) but when trying to add values using this syntax INSERT INTO probability (measured_at, probability, provider_id, segment_id) VALUES( 1412877161519,0.7418073347680607,1,211623); I still get 0000-00-00 00:00:00.00000 at the "measured_at" column. How should I insert values to this table? – Affecting

@Affecting you would need to convert the value on the way in: INSERT ... VALUES(FROM_UNIXTIME(0.001 * 1412877161519), 0.7418 ... ); – Emeliaemelin

Multiplying by 0.001 is a lifesaver! I could never guess it would work without losing second fraction. Thank you. – Unreason

In my case, since I'm testing with a raspberry pi (and it is really cumbersome to install mysql 5.6 in the RPI, I'm going with saving the information as unix time. – Bacchic

Solved by * 0.001 – Slashing

Answer 2

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');

Cuff

Answer 3

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

Boron

Answer 4

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)
   );

Beneficiary

A BIGINT is 8 bytes, though. Is there nothing more efficient? That's like two more bytes per record than you need – Wiliness