PHP/MySQL - storing date and time

Umm...I have no idea on how to store the date/time in MySQL database.

Anyone got a sample code which deal with time/date in PHP/MySQL?

Indeed, I am making a tiny guest book like program. I want to store/retreive time and date in/from MySQL database.

You need a special field in your table from the variable type DATETIME.
You can fill this with an extra MYSQL function named NOW() like this:

mysql_query("insert into table values (..., NOW(''),...");

The function NOW() insert the actual date/time, but you can even save every date/time by entering manually.

The data is stored in the format: YYYY-DD-MM HH:MM:SS

Check any MySQL tutorial for info.

Hope that will help you...

Martin

Thanks Martin.
now() works fine for me.
I just wonder if I can convert the format
of the date/time
i stored my date/time in the format of
YYYY-MM-DD HH:MM:SS

I want to show it in this format:
MM-DD-YYYY HH:MM in PHP

anyone know how to do it?

select date_format(date_column, '%m %d %Y %l:%i %p) as When from table_name;

// %p gives AM/PM if you need it

Check out MySQL's date and time functions on the online manual for more formatting options (military time, ordinal suffixes, a bunch of stuff)

Thanks Kyuzo. (This is 'ccm'. I am using another account in office.)
Indeed, I am a newbie in both PHP and MySQL.
I have already created a table with a column named 'date datetime'.
And I have used NOW() in PHP to store the date/time info in MySQL database.
For now, I retreive the date/time info by '$row->date' in PHP.
I have no idea what to do next.
Do I have to re-create the whole table?
And how do I implement the Date/Time format in my table?

Hi hkccm,

You don't have to recreate the table for changing a field type.

u may try this..

'alter table tblname change old_field new_field TIMESTAMP(-);'

for getting a special date format you can use
TIMESTAMP--with in bracket pass value for a specific format.

See the mysql documentation for the correct syntax.

To change the date/time format you can even use the regular expression stuff, it's quite mighty!

Example:
$date = "2000-03-13";
ereg("([0-9]{4})-([0-9]{1,2})-([0-9]{1,2})",$date, $regs);
echo "$regs[3].$regs[2].$regs[1]";

Output: 13.03.2000

This function takes the date ($date) and splits it into an array ($regs) by odering after YYYY ($regs[1]), MM($regs[2]), DD($regs[3]) - there was a failure in my first posting ;-)).
You can now easily change the order by changing the order of the array.

Martin