Page 1 of 1

Storing Date as a string in MySQL

Posted: Mon May 19, 2003 1:53 pm
by packito
Hi!

I need help with the following issue: i'm trying to store the computer's actual date in the 'date_created' colum of a MySQL table.

The code i first tried to do it was:

//first determine actual date
$today=date("d/m/Y",time());

//then i use this query to insert all itens into the table
$insertSQL=sprintf("insert into mytable(col1, col2,...,date_created) VALUES(%s,%d,...,&s)", function1, function2,...,$today);

where col1,col2... are the names of other columns in the table and function1,function2... are functions i use to obtain some other values.
There is no problem with any of these functions, since all fields are correctly inserted in the table, except for the date!

Instead of writing the actual date, the program writes a number, which is the result of this numerical division: d/m/Y. For example, if the date is 19/05/2003 it writes 0.0019 whic is 19 divide by 5 divide by 2003. That is even more strange to me since print of gettype($today) returns string, which is exactly the type of variable i want and the colum (date_created)is suppose to accept.

What must i do to correctly insert dates (like "19/05/2003") in mytable, storing them as a string??

Thanks everybody!

Posted: Mon May 19, 2003 2:22 pm
by twigletmac
In MySQL store dates either in DATE, DATETIME or TIMESTAMP fields - not as a VARCHAR, this will make your life a lot easier when you need to manipulate them later. You can easily use DATE_FORMAT() to format the date before outputting it:
http://www.mysql.com/doc/en/DATETIME.html
http://www.mysql.com/doc/en/Date_and_ti ... tions.html

Mac

Problem solved

Posted: Tue May 20, 2003 11:44 am
by packito
Ok, problem solved. Thanks for the help! :D

Posted: Wed May 21, 2003 1:59 am
by ckuipers
I usually convert the dates to the UNIX timestamps and store them like that in the database.