Formating multiple mySQL date fields at the same time

Question

I have a table with 12 timestamp columns and 3 other fields. I can't change the field type on the timestamp columns.

I need to display all 15 fields to the user as m/d/y. Is there a way to format them all at the same time without having to apply DATE_FORMAT to each one? I'd rather not have to do

SELECT DATE_FORMAT(field, '%c/%e/%y') AS field

for each one if possible.

I'm using mySQL & PHP and I don't care where the conversion happens.

Answer 1

I dont think it is possible, the query doesnt know what columns are until it returns the results, you have to tell it to change the formatting for each datetime.

Matt

Answer 2

Have you considered using a database layer like MDB2 or PDO, which can convert the types for you? MDB2 has a date type, that displays YYYY-MM-DD format but could easily be turned into m/d/y with strftime and strtotime.

An example of using MDB2:

$dsn = "mysql://user@pass/db"; (not sure about DSN format, you might have to poke the MDB2 documentation)
$connection = MDB2::connect ($dsn);
$connection->loadModule ("Extended");
$rows = $connection->getAll ("your query", array ('date', ...), $parameters, array (paramater-types), MDB2_FETCHMODE_OBJECT);

if (MDB2_Driver_Common::isError ($rows)) throw new Exception ("Error!");
else { foreach ($rows as $row) { ...Do something... } }

You can find more documentation about using MDB in this way at MDB2 on InstallationWiki. Also there you'll find a list of MDB2 datatypes and what they are mapped to for display values. Also, Docs for MDB2_Extended will be a good source of info.

Edit: Obviously you'll have to use strftime and strtotime on each row separately. If you switch MDB2_FETCHMODE_OBJECT to MDB2_FETCHMODE_ORDERED or MDB2_FETCHMODE_ASSOC above, you can probably use a foreach loop to run strftime and strtotime on all of the rows that need to be reformatted.

Answer 3

You can write a simple date conversion routine in php such as this:

function mysql2table($date) {
    $new = explode("-",$date);
    $a=array ($new[2], $new[1], $new[0]);
return $n_date=implode("-", $a);
}

Which I stole from here: http://www.daniweb.com/code/snippet736.html#

Then simply loop through your SQL column results checking if the value is a date and converting it.