How do I change the appearance of time/date?

[jdlev]

So mysql will only take the date/time fields at 2012-11-12 and 8:00

 

What if I wanted to save/display the date at 11/12/12 instead and the time at 8:00am?

[Barand]

You can save your dates like that (11/12/12) but it will be as much use as a chocolate teapot. You cannot do sorts or comparisons on dates in that format, nor can you use the dozens of extremely useful dattime functions without converting it first.

 

Store dates for functionality, not for their appearance.

Edited February 13, 2013 by Barand

[shlumph]

Just as Barand said. Format your dates when you are ready to display them. Store them as Y-m-d H:i:s or as a Unix Timestamp.

[Barand]

Store them as Y-m-d H:i:s or as a Unix Timestamp.

 

I disagree with using unix timestamps - you still have to convert those to use datetime functions. USE DATE, DATETIME or TIMESTAMP types (whereTIMESTAMP is the MySQL type)

 

edit:

Not only that, if you are browsing the data in your table unix time values are impossible to interpret

Edited February 13, 2013 by Barand

[kicken]

Once you've saved the dates in a DATE/DATETIME/TIMESTAMP column as recommended, when you SELECT them back out you can format them for display purposes however you want using the DATE_FORMAT function.

 

[jdlev]

Thanks guys!

[fenway]

And TIMESTAMPs have time zone fun built-in.