Simple SQL Problem

Twinkie

Banned
Messages
1,389
Reaction score
12
Points
0
Why is SQL saying now() is an invalid default value for a datetime field?
Code:
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?
 
Last edited:

garrensilverwing

New Member
Messages
148
Reaction score
0
Points
0
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:

Code:
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
Code:
Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
 

mrfish

New Member
Messages
5
Reaction score
0
Points
0
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().
 

gomarc

Member
Messages
516
Reaction score
18
Points
18
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
 

batman1

New Member
Messages
92
Reaction score
0
Points
0
default value cannot be a function. put some time. 0000-00-00 00:00:00
 

xav0989

Community Public Relation
Community Support
Messages
4,467
Reaction score
95
Points
0
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.
 
Last edited:

Twinkie

Banned
Messages
1,389
Reaction score
12
Points
0
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.
 

xav0989

Community Public Relation
Community Support
Messages
4,467
Reaction score
95
Points
0
Re: Cms theme

:drool: I guess everyone is welcome here lol. :cool:
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!
 
Last edited:
Top