Simple SQL Problem

[Twinkie]

Why is SQL saying now() is an invalid default value for a datetime field?

CREATE TABLE Catagories (
ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(100) NOT NULL UNIQUE,
Created DATETIME DEFAULT NOW(),
Modified DATETIME DEFAULT NOW(),
Notes VARCHAR(255)
)

~Error: Invalid default value for 'Created'

Looks fine to me?

 

[garrensilverwing]

im not sure but i dont think you can store functions inside of a sql database and have it run the function, however sql has a built in way to put timestamps in, try this:

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL auto_increment,
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `created` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

you will need to add a way to put the timestamp for the created, such as the php function date()
also, you can only have one field time be timestamped otherwise you will get this error

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

 

[mrfish]

Yah, I just got back into MySQL and I'm pretty sure your cannot store NOW() as a property. Correct me if I'm wrong though. The only way you should use the now() function is in your php script when the 'whatever' if created and/or modified. Then you can add/rewrite the current value to now().

 

[Twinkie]

Ok, thanks for your help

 

[gomarc]

The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. See Section 10.3.1.1, “TIMESTAMP Properties”.

Source: http://dev.mysql.com/doc/refman/5.0/en/create-table.html

 

[vol7ron]

MySQL over PostgreSQL? tsk tsk

 

[batman1]

default value cannot be a function. put some time. 0000-00-00 00:00:00

 

[xav0989]

I usually allow null values in my timestamp fields since some programs and scripts have trouble processing 0000-00-00 00:00:00.

Or you could setup the database field to hold an integer and directly store the number of seconds since the epoch, as a lot of php scripts are doing.

 

[Twinkie]

I understand the reasoning for using a timestamp rather than a datetime field, but for the sake of maintainability for a small project, I chose the field that would be easier to read. If this was a bigger project I would be concerned of the speed of turning datetime into a timespamp value for processing.

I am making a little Ebay for me and my mom to sell some of our old junk.

 

[cmstheme]

Cms theme

:drool: I guess everyone is welcome here lol.

 

[xav0989]

Re: Cms theme

:drool: I guess everyone is welcome here lol.

cmstheme, unless you are willing to add some valuable information, or actually give your opinion, could you please refrain from posting just to post. Oh and by the way, there is an introduction section it's just around the corner:

Ok I admit it, I was pretty harsh, so, yes everyone is welcome, but have a little bit of retenue, please!