osoda osoda - 8 days ago 5
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;
Comments