MySQL formatting dates & times

[Guest]

I have a couple questions that I hope you guys can help me out with...

I have a MySQL DB which includes the columns "date", "start_time", and "end_time".  Date is stored as "date", and start_time & end_time are stored as "time".

When formatting a date in PHP, I've found this page very useful: http://php.net/manual/en/function.date.php

Does anyone know of a similar page showing the attributes that are available with the MySQL DATE_FORMAT.

Also, I currently have the query written like this:

SELECT id, title, description, DATE_FORMAT(`date`, '%m/%d') AS formatted_date, start_time, end_time, location

FROM events

Does anyone know how I can format the TIME columns similar to how I've done the DATE columns?  I have times stored as "08:00:00" and "21:30:00" and I want them to display as "8:00am" and "9:00pm".

Any help?? THANKS!

[David_Powers · Accepted solution]

The full list of formatting specifiers is in the MySQL documentation at http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format.

Use DATE_FORMAT() for your time columns.

DATE_FORMAT(theTime, '%l:%i%p') AS formatted_time

I think that MySQL uses AM and PM. If you want lowercase, just pass the database result to strtolower():

<?php echo strtolower($row_recordsetName['formatted_time']); ?>

[Guest]

As always, David - thank you very much. I Googled that several times and never came to the page you sent me. I appreciate it!!