Convert unix timestamp to datetime with milliseconds in MySQL

Ask Time：2020-03-13T05:54:15 Author：kexxcream

Problem

Converting unix timestamp to datetime while retaining milliseconds.

Background

I am receiving unix timestamp in the following format:

1584049707

Then I am trying to send it by means of PHP to a column in MySQL that is datetime(3) using TO_TIMESTAMP(). I have also tried FROM_UNIXTIME(), but with the same results.

SQL

$sql = "
  INSERT INTO assoc_table (timestart, timeend) 
  VALUES (TO_TIMESTAMP(:timestart), TO_TIMESTAMP(:timeend))
";

Result

2020-03-12 22:42:23.000

For some reason it does not register the milliseconds.

Desired outcome

To get the milliseconds out of the unix timestamp and into the datetime column.

Author:kexxcream，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/60662325/convert-unix-timestamp-to-datetime-with-milliseconds-in-mysql

GMB :

Epoch timestamps represent then number of seconds elapsed since January 1st, 1970; if you want fractional secons, it needs to have a fractional part... which is not the case with the input that you are given to MySQL; this is then reflected in the results that you are getting. \n\nGiven an epoch timestamp with a fractional part, from_unixtime() works as expected:\n\nselect from_unixtime(1584049707.123)\n\n\nReturns:\n\n2020-03-12 21:48:27.123\n\n\nNote: datetime(3) is the relevant format to store such value.",

2020-03-12T22:04:32

Convert unix timestamp to datetime with milliseconds in MySQL