inserting DATE into mysql

Anon

Hello,

I am experiencing difficulties in inserting a DATE into a mysql table, using PHP.

I have created a table with a 'date' variable in it, but can't seem to be able to insert into it.

I am currently trying using the command:
$query= mysql_query("insert into table (datevalue) values ('20010112');");

What should it be?

thanks,
jim

Anon

Dates are not stored in mysql as 2001-01-12 like you have. They instead use the basic unix timestamp and base caluclations off of that.

So what to do:
use PHP's mktime() function

Usage: mktime(hour, minute, second, month, day, year)

So in your case:
$datevalue = mktime(0, 0, 0, 1, 12, 2001);

and then modify your SQL to read:
values ('$datevalue')

That's if you want to set your timestamp to some specified, fixed date.

If, however, you just want to automatically include a timestamp for the current date and time, use this method instead:

$datevalue = date("Y-m-d");

and that will set your datevalue to right now.

[deleted]

Ok brian, time to read the manual again! ;-)

You can simply insert "2000-01-01" as a date, or
"2001-01-01 01:01:01" as a datetime.

http://www.mysql.com/doc/D/A/DATETIME.html

--quote --
The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD
HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. (``Supported'' means that although earlier values might work,
there is no guarantee that they will.)

The DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported
range is '1000-01-01' to '9999-12-31'.

The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have
multiple TIMESTAMP columns, only the first one is updated automatically.
-- end quote --

Anon

What is the code for getting the current date and current time using the date and time datatype.