Storing Date as a string in MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
packito
Forum Newbie
Posts: 20
Joined: Mon May 19, 2003 1:53 pm

Storing Date as a string in MySQL

Post 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!
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
packito
Forum Newbie
Posts: 20
Joined: Mon May 19, 2003 1:53 pm

Problem solved

Post by packito »

Ok, problem solved. Thanks for the help! :D
ckuipers
Forum Commoner
Posts: 61
Joined: Mon Mar 24, 2003 6:10 am

Post by ckuipers »

I usually convert the dates to the UNIX timestamps and store them like that in the database.
Post Reply