Timestamp : from UNIX to MySQL

Question

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 ?

Answer 1

There is a built-in function FROM_UNIXTIME() in MySQL, use it:

INSERT INTO yourtable (date_heure, ...) values (FROM_UNIXTIME(1470762668), ...)