Convert mysql datetime into something more friendly

Client hates current display

[neophyte]

In an effort to do things "right" I'm now using a datatype of "datetime" in my mysql tables when they contain dates and times - I use to use varchar.

Problem is, when I pull this information out from the db for screen display, my current client freaks.

Instead of the current display of 2006-03-02 19:13:37, he wants it to show March 2, 2006, 7:28pm.

Easy, of course, when I was just using string data... but now, oh, no.

How does one go about converting an accurate datetime type to something like the above? Can it be done?

Neophyte

[omoutop]

check the mktime() function. It will convert your date-time into an interget (unix time), which u store in your db.

When u retrieve the interget, use mktime again to format it into the deseired format

[Robber]

You can try something like this which I've just grabbed out of a script:

list($yr,$mon,$day) = split('-',$row->fixturedate);

$display_date = date('D j M Y', mktime(0,0,0,$mon,$day,$yr));

This looks like my DB field is just a date rather than datetime but this can be tweaked for that no problem.

Hope that's helpful

[dmmh]

check the mktime() function. It will convert your date-time into an interget (unix time), which u store in your db.

why store the time twice?
Kinda redundant imo, storing either datetime or timestamp is fine, you can convert all ways from there

[jamesa]

MySQL's DATE_FORMAT is the easiest and best way IMO. You don't need to mess with the date in PHP, just do this with the select statement. Assuming your datetime field is called 'thedate':

SELECT DATE_FORMAT(thedate, '%M %e, %Y, %l:%i%p') as newdate FROM your_table;

or even this if you like...

SELECT *, DATE_FORMAT(thedate, '%M %e, %Y, %l:%i%p') as newdate FROM your_table;

It will return a field called 'newdate' with the formatting you want.

See this page [dev.mysql.com] at mysql.com/doc for all the formatting options.

[Robber]

Jamesa's solution is a good one which we also use a lot, one thing to bare in mind though I don't think its standard SQL, not a problem if you're not switching but I just had to shift a MySQL db to MSSQL and was looking high and low for DATE_FORMAT

[JollyK]

Robber's SO right -- although most SQL implementations do have a way to format the date either in a session or in the select statement (like MySQL), they don't all do it the same way.

Nevertheless, we've always tried to do the date formatting in SQL rather than in PHP/Perl just because it seems to be a bit faster and more elegant, but I think it's a matter of personal preference.

If you think you might have to switch to MSSQL, Oracle, etc at a later time, I would suggest maybe sticking all your date formatting selects into a separate function, and use *that* to call the "select DATE_FORMAT(blah, 'blah')". That way, if you do switch, there's only one place to change it.

We switched from Oracle to MySQL at one point, and OY! The amount of code that had to be fixed due to the differences between Oracle and Mysql date stuff was painful. If we'd been smart enough to put a "get_date" function or something, then we would have just had to change that.

FWIW. :-)

JK

[aeve]

I don't know if this is a good solution or not, but I've been pulling datetime out of MySQL using the function

UNIX_TIMESTAMP(time) AS time

and then formatting the date in php with

date()

.

I've also found

strtotime()

very useful on some occasions--it can figure out a lot of different formats.

[neophyte]

Cool! Thank you all for weighing in on this! My client will be most pleased that this issue is now solved as per his requirment.

Thanks again!

Neophyte