homepage Welcome to WebmasterWorld Guest from 54.234.147.84
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Convert mysql datetime into something more friendly
Client hates current display
neophyte




msg:1305641
 11:31 am on Mar 2, 2006 (gmt 0)

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




msg:1305642
 11:52 am on Mar 2, 2006 (gmt 0)

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




msg:1305643
 1:17 pm on Mar 2, 2006 (gmt 0)

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




msg:1305644
 1:27 pm on Mar 2, 2006 (gmt 0)

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




msg:1305645
 1:43 pm on Mar 2, 2006 (gmt 0)

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




msg:1305646
 3:00 pm on Mar 2, 2006 (gmt 0)

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




msg:1305647
 3:16 pm on Mar 2, 2006 (gmt 0)

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




msg:1305648
 5:51 pm on Mar 2, 2006 (gmt 0)

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




msg:1305649
 11:22 pm on Mar 2, 2006 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved