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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to datetime

Examples related to timestamp

Examples related to milliseconds