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 :


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 ?


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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.