Can't make datetime field's default value NOW(), HELP.

[SauloA]

I have a MySQL database and I'm trying to set my datetime fields have a default value of NOW() or CURDATE or something that records the current date/time.  I'm using phpmyadmin and input NOW() and a number of other default values in the default value text box, but I keep getting an error saying that my default value is invalid.  Is there any way to set a default value that records the current date/time on phpMyAdmin or MySQL?  I'd appreciate the help.

Here's my table structure.  The [color=red]red[/color] is the datetime field I'm trying to change.

CREATE TABLE `user_review_tbl` (
 `urev_id` int(11) NOT NULL auto_increment,
 `user_id` int(11) NOT NULL,
 `urev_review` text NOT NULL,
 [color=red]`urev_date` datetime NOT NULL,[/color]
 `urev_approved` tinyint(1) default NULL,
 `shop_id` int(11) NOT NULL,
 PRIMARY KEY  (`urev_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

[dgiberson]

change to:
CREATE TABLE `user_review_tbl` (
  `urev_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL,
  `urev_review` text NOT NULL,
  `urev_date` datetime NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `urev_approved` tinyint(1) default NULL,
  `shop_id` int(11) NOT NULL,
  PRIMARY KEY  (`urev_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

[SauloA]

I've already created the table so how would the code look when using ALTER?

[SauloA]

Changing my code to:

`urev_date` datetime NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

isn't working.  I get an error in phpMyAdmin saying that the code isn't invalid.

Any other ideas?

[dgiberson]

try taking the NOT NULL option off.... that might work, i know it does in Query Browser

[SauloA]

I removed NOT NULL but I still get an error that the code is invalid.  Any other ideas?

[dgiberson]

If there's not a whole lot of data in the table, I would suggest scrapping the table and start from the beginning, if there is a bunch of data, I can check phpMyAdmin once I get home...

[SauloA]

I've been creating and recreating through the process.  But I may be putting the wrong attributes.

I've written the code exactly like this:

'urev_date` datetime NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

and without the NOT NULL like this:

'urev_date` datetime default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

And I also tried making the default NOW() along with other default values without NOT NULL.

Any ideas?

[Daney11]

This may be of some help.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

[SauloA]

The website you provided may be good for formatting time but doesn't seem to be good for creating default values.  Unless you know how to manipulate the data on that website to get me the current date and time displayed then I'm out of luck.

[Daney11]

Hmm this one?

http://dev.mysql.com/doc/refman/5.0/en/timestamp-4-1.html

[SauloA]

I don't want to use a TIMESTAMP field.  I've looked at that MySQL documentation you have been reading many times when I was learining MySQL.  I don't think that's the best resource.

[dgiberson]

SauloA, I think for what you are trying to accomplish, a timestamp field is the way you want to go if you want the date/time updated every time the record is touched.... otherwise use an integer(11) field to store unix timestamp, and update that from the php code. i cant find any documentation on setting a default time value without using a timestamp data type...