osoda osoda - 6 months ago 38
MySQL Question

Converting retrieved datetime from database

I'm trying to send the date to my Android device in JSON format. Also, the time is in 24 hours format. How can I change this into 12 hours format?

PHP

$sql = "SELECT * from news Order by n_date Desc";
$con = mysqli_connect($server_name,$mysql_user,$mysql_pass,$db_name);


$result = mysqli_query($con,$sql);


$response = array();
while($row=mysqli_fetch_array($result))
{
array_push($response, array("title"=>$row[2],"content"=>$row[3], "n_date"=>$row[4]));
}



echo json_encode (array("news_response"=>$response));


mysqli_close($con);

?>


The JSON response:

{"news_response":[{"title":"news","content":"content","n_date":"2016-11-12 10:47:23"}]}


I am trying to convert the 2016-11-12 to something like November 12, 2016 10:47:23. How do I do this?

I couldn't find any example online.

Answer

in PHP:

echo (new DateTime('2016-11-12 10:47:23'))->format('F d, y GA:i:s');

in MySQL:

SELECT concat(MONTHNAME(date),' ', DAY(date), ' ', TIME(date)) from news;