Having problems with dates

[jiveturkey420]

I was creating a news section to a website, and ran into a problem.

I am using a MySQL database to store the news entrys, and this particular table only has 2 columns. I created the table like so....

 

CREATE TABLE news (time timestamp NOT NULL default now(), news varchar(10000));

 

The news is inserted into the table using like so......

 

INSERT INTO news VALUES (' ', '$newnews');

 

...through a php script.

 

The value of the first column of my table always winds up being  0000-00-00  00:00:00

 

Why isn't it recording the proper time?

I am using a LAMP stack on Fedora 17

 

 

[xyph]

You don't want a space in there.

 

INSERT INTO news VALUES ('', '$newnews');

 

You can't use NOW() for a default value either (this may have changed with the latest version though). From what I know about MySQL5, default must be a constant value. Leave default blank, and use this instead.

 

INSERT INTO news VALUES (NOW(), '$newnews');

[Barand]

When I run your INSERT I get "incorrect datetime value" error.

 

Use

 

INSERT INTO news VALUES (null, '$newnews');

 

I also ran your create statement as I too expected it to fail. It accepted it but changed the default to "CURRENT_TIMESTAMP" (MySQL Workbench 5.2.38)

[jiveturkey420]

Righteous. It's working now. Thanks y'all.

[Psycho]

You can't use "NOW()" as the default, but you can use "CURRENT_TIMESTAMP"

 

CREATE TABLE news (time timestamp NOT NULL default CURRENT_TIMESTAMP, news varchar(10000));

 

But, if you are going to have a field with a default, what's the point of having to include an empty value when creating the record? Just don't include the field in the insert. Include the field list that you are inserting for and the associated values.

INSERT INTO news (news) VALUES ('$newnews');

 

And, for what it's worth, I would suggest not having a field name and a table name with the same value. Only creates confusion in my opinion.