Converting unix timestamp to datetime while retaining milliseconds.
I am receiving unix timestamp in the following format:
Then I am trying to send it by means of PHP to a column in MySQL that is
TO_TIMESTAMP(). I have also tried
FROM_UNIXTIME(), but with the same results.
$sql = " INSERT INTO assoc_table (timestart, timeend) VALUES (TO_TIMESTAMP(:timestart), TO_TIMESTAMP(:timeend)) ";
- 2020-03-12 22:42:23.000
For some reason it does not register the milliseconds.
- To get the milliseconds out of the unix timestamp and into the datetime column.
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.
Given an epoch timestamp with a fractional part,
from_unixtime() works as expected:
datetime(3) is the relevant format to store such value.
Creating a datetime object with php usign the epoch and then formatting it should work:
$epoch = 1584049707; $dt = new DateTime("@$epoch"); echo $dt->format('Y-m-d H:i:s.u');
if you need to do the conversion on MySQL check this documentation:
should look like:
DATE_ADD(FROM_UNIXTIME(0), interval -315619200 second),'%Y-%m-%d-%f');
where -315619200 is your epoch