Which Data type to use for storing date

jamessmb

Hi,

Fairly new to PHP/MySQL. I have a MySQL table with a field called "RecordDate". It's data type is DATETIME.

My PHP code sets a var called $ts to this:

$ts = date('Y-m-d',time());

When I insert it into the table, it shows up as this:

2000-19-93 00:00:00

I just ran this, so I'm expecting the value to be 2003-08-02

If I run this code:
$ts = date('Y-m-d',time());
echo $ts;

I get 2003-08-02 which is correct and what I want.

Using PHP Version 4.3.2 and MySQL 3.23.49

I've tried the other Date/Time datatypes and they all return 0000-00-00

Thanks,
James

barand

Should work :-

$ts = $date('Y-m-d');  // defaults to current date/time
$sql = "INSERT INTO tablename (RecordDate) VALUES ('$ts')";
mysql_query($sql);

jamessmb

But, what datatype should the field be in MySQL? datetime, date, or timestamp?

Thanks,

James

barand

If the time is irrelevant, such as birthdays etc, use DATE.
If the time does matter, eg appointments, use DATETIME
Use timestamp when you want mysql automatically to record when a record was inserted or updated.

hth

jamessmb

Ok, that makes sense. Thank you.