0

I have a timestamp given by a JSON. I want to push this into my database (MySQL). There is a row called "epoch" which is a date time type.

Printing the timestamp by the following code results in the correct result.

$epoch = date('d.m.Y H:i:s', $this->date);

echo "Timestamp: " . $this->date . " - Date: " . $epoch . "\n";

Timestamp: 1446746400 - Date: 05.11.2015 19:00:00 

$sql = "REPLACE INTO Forecast (epoch) VALUES ('" . $this->date . "')";

Result is a 0000-00-00 00:00:00 Date Value in my DB...

Can anybody explain me, how to pass this timestamp correctly into MySQL?

Improve this question
2
  • start by using a mysql date/time format: dev.mysql.com/doc/refman/5.7/en/datetime.html. there's no such thing as a "json timestamp". json has no real data types beyond primitive numerica/string/array/objects. Commented Nov 2, 2015 at 20:08
  • there is a TIMESTAMP data type in mySQL, what is the data type of your epoch column? SELECT column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Forecast' Commented Nov 2, 2015 at 20:21

3 Answers 3

Reset to default
1

Try with this:

$epoch_date = $epoch->format('Y-m-d H:i:s');
$query = "update forecast set epoch = '$epoch_date'";
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

The datetime format in mysql is not a timestamp like in php. You need to store a string that looks similar to your$epoch string:

date('Y-m-d H:i:s', $this->date)

would work fine.

Improve this answer

Comments

0

MySQL only accepts date in YYYY-MM-DD format. Anything other than this format will simply be discarded and the default value of the field is inserted as a result. So in order to store the correct date you need to convert it native datetime format of Mysql. You can use Mysql date_format() or php date function for this.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.