melbourne chapter

Here’s some quick examples of a few different ways you can work with  date/ or datetime fields rom a MySQL database. Hope you find them helpful
Formatting date with MySQL for output:

$query = mysql_query("SELECT DATE_FORMAT(event_date, '%d/%m/%y') AS formatted_date FROM events");
 
$row = mysql_fetch_array($query);
 
echo $row['formatted_date'];
// output: 20/11/06

Retrieving a date from MySQL with the intention of using it for calculation (timestamp conversion):

$query = mysql_query("SELECT UNIX_TIMESTAMP(event_date) AS timestamp FROM events");
 
$timestamp = mysql_fetch_array($query);
 
echo $row['timestamp'];
// output: 20061120091528 i.e. 2006-11-20 09:15:28

Formatting a date string with PHP for output:

$query = mysql_query("SELECT event_date FROM events");
 
$row = mysql_fetch_array($query);
 
$formatted_date = date('d m y', strtotime($row['event_date']));
 
echo $formatted_date;
 
// output: 20/11/06

Converting formatted date strting for calculation in PHP (timestamp conversion):

$query = mysql_query("SELECT DATE_FORMAT(event_date, '%d/%m/%y') AS formatted_date FROM events");
 
$row = mysql_fetch_array($query);
 
echo 'Formatted date: '.$row['formatted_date'];
 
//output : 20/11/06
 
$timestamp = strtotime($row['formatted_date']));
 
echo 'Timestamp '.$timestamp;
 
// output: 20061120091528 (i.e. 2006-11-20 09:15:28)