The best formats for storing Time & Date in MySQL DB

[newphpbie]

Hey all..

I would like to save the time and date to a table in a MySQL database.
I am curently using
(date("F dS Y&quot)
to display the date but I don't think this is appropiate for storing in the MySQL table. I have also been looking at all the possibilities for recording the current time, although I appear to have got really lost amongst the endless possibilities.

Could someone recommend the best code to collect and record the time and date in a MySQL table please.

Any help/advice/tips would be much appriciated.

Thanks.

Tony

 

[pwesson]

This is what I do...

Write Date/Time to a table:
$thedate = Date("Y-m-d H-i-s"
insert into table (DATETIMEFIELD) values ('$thedate')...

When reading the table:
SELECT *, DATE_FORMAT(DATETIMEFIELD, '%W %M %D, %Y at %l:%i %p') as MyDateTimeField ...

With the mySQL DATE_FORMAT function you can now get your date displayed in any format you want.

Hope this helps.

Paul Wesson, Programmer/Analyst

http://www.ipaw.net

 

[sleipnir214]

Just to flesh out pwesson's answer a little bit:

MySQL will correctly accept a datetime value in one format: "YYYY-MM-DD HH:MM:SS" (actually, that's not true. It'll also accept "YYYY/MM/DD HH:MM:SS&quot

MySQL will pretend to accept other formats, but when you later fetch the value, you'll get garbage back.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[shadedecho]

I believe that MySQL will also accept YYYYMMDDHHMMSS as a 14 char string into "timestamp" fields.