On one side, I have a MySQL DB where there is table that has a timestamp field. According to MySQL doc, this MySQL TIMESTAMP is similar to DATETIME (with different bounds). As far as I know, the required form is : "Y-m-d H:i:s".
An extract from the DB :
date_heure TIMESTAMP NOT NULL
On the other side, I have a mobile app which sends data to the DB. The time is very important, so among this data (sent using JSON) there is a timestamp field. This timestamp is UNIX-like, in milliseconds.
As you guessed, my purpose is to insert the received timestamp in the database.
To do so, on server-side, I wrote the following PHP code :
$timestamp = $json_decode['t']; //get the UNIX timestamp from the JSON data
$sec_timestamp = $timestamp/1000; //in order to have seconds, not milliseconds
$mysql_timestamp = date("Y-m-d H:i:s", $sec_timestamp); //converting UNIX timestamp into MySQL timestamp.
However, in the DB, the timestamp field still has the following form, for every row : 0000-00-00 00:00:00
There is no problem on the app side, the UNIX timestamp (in milliseconds) is properly sent via JSON.
Hence, what can I do to insert this into the DB ?
Best Answer
There is a built-in function
FROM_UNIXTIME()in MySQL, use it: