How to convert MySQL TIMESTAMP to date in PHP

Question

I have a MySQL DB table with a column with a name "timestamp", type timestampand attribute on update CURRENT_TIMESTAMPand a default value CURRENT_TIMESTAMP.

If I add an entry to the table, specifying other values, but not a timestamp, then the timestamp will automatically be added as 2016-12-28 17:02:26.

In PHP, I query the table using the following query

SELECT * FROM history WHERE user_id = 9 ORDER BY timestamp ASC

The result of the query is stored in $rows, and I use foreachto create an array with some other formatted values. I'm trying to format a timestamp in the date of the 24-hour time in the UK dd/mm/yy, HH:MM:SS.

I tried the functions date()and strftime()as follows:

$formatted_datetime = strftime("%d %m %y  %H %M %S", $row["timestamp"]); 
$formatted_datetime = date("d/m/y, H:i:s", $row["timestamp"]);

Both of them lead to the following notification, and the time of the date is displayed incorrectly, like 01 01 70 00 33 36:

Note. Invalid numeric value found in / home / ubuntu / workspace / pset 7 / public / history.php on line 20

I am new to PHP and MySQL, and so far, none of the other issues or documentation that I have seen successfully handled this conversion. I do not understand why it strftime()does not work, and how to do it correctly?

Answer 1

To do this, the OO (and most flexible) method uses the class DateTimeand uses the static method createFromFormatto create a new object DateTime:

$new_datetime = DateTime::createFromFormat ( "Y-m-d H:i:s", $row["timestamp"] );

$new_datetime , , object format:

echo $new_datetime->format('d/m/y, H:i:s');

, DateTime, (, ), ( DateTime) ( // .. DateTime).

DateTime: http://php.net/manual/en/class.datetime.php . . .

Answer 2

MySQL date timestamp YYYY-MM-DD HH:MM:SS (2016-12-20 18:36:14) formate, , .

$formatted_datetime = date("d/m/y, H:i:s", strtotime($row["timestamp"]));

Answer 3

MySQL ? mm/dd/yy, dd/mm/yy?

SELECT *, DATE_FORMAT(timestamp, '%m/%d/%y, %T') formatted_date FROM ....

$row ['formatted_date'].

. https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Answer 4

Carbon

$carbon = Carbon::instance('2016-12-28 17:02:26');

, .