Format Date retrieved from db

Anon

Hello,

How do I format a date value that is retrieved from a datetime field?

Ex. from 2001-02-23 15:30:32 to February 23, 2001 3:30:32 PM

Anon

Hi,

I'm assuming you're using MySQL.

SELECT DATE_FORMAT(column, "%M %e, %Y %r") AS column FROM table;

This is in the manual under "DATE_FORMAT."

Anon

Thanks alot, works great.

allending

Hi guys, which do you think is faster and better for a message board , where more people VIEW than POST??

Formatting a date in a format that i want and storing it in a VARCHAR in mySQl, and just retrieving it whenever a user views a page with no need for formatting since it is already in the form I want.

....

or, put it into mySQL , and each time I retrieve it from mySQL , i use the DATE_Format funtion.

It would seem that for a large set of rows, putting the time/date in a preformatted way would be more efficient wouldn't it? (Since the other way would invlove mySQL formatting values for each row)

Thanks for any comments.

Allen

allending

I mean preformatting the date in in PHP of course 🙂

sepodati

I think it'd be better to store the date as a timestamp and use the MySQL functions to format it. You might come across a reason later to select posts within a certain amount of days, or time period or something. you will be able to do it easily with a timestamp...

---John Holmes...

allending

Thnaks!
I decided on that too 😉
I wouldnt be able to do a simple ORDER BY without a timestamp.

allending

Do you think it would hog a lot of resources to maybe format 20 dates per request? I dont think a date format would cost much, but i would love some input.

sepodati

Maybe you could have a timestamp column and a preformatted column.

INSERT INTO table (time_column, formatted_column) VALUES (NOW(),DATE_FORMAT(NOW(),'%Y-%m-%d'));

Something like that when you insert it. I know you want a different format, but I don't remember the symbols right now...

---John Holmes...

allending

Thanks, ill try that .. best of both worlds 😉 .. Trying to make this message board as fast as possible.