MySQL query datetime default

Question

I've got the SQL query below that seems to be throwing an error but I', really stuck in where the syntax error is. I think it's on the field5 but I'm not 100% if I'm using default datetime correctly.

CREATE TABLE mytable (field0 int(10) unsigned NOT NULL auto_increment primary key,
field2 DATETIME NOT NULL,
field3 int(1) unsigned default 0,
field4 int(10) NOT NULL,
field5 DATETIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL)

Can anyone whose better at SQL see where the syntax error is? :)

Thanks

Jaydee · Accepted Answer · 2011-04-05 13:42:46Z

3

Field 5 - TIMESTAMP is a type and so is DATETIME, use one or the other not both.

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

answered Apr 5, 2011 at 13:42

Jaydee

4,1481 gold badge21 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jaime Bellmyer Over a year ago

this explains the solution better than the chosen answer. I know Tommy posted first, but I voted for this one because it already had a vote and it needs to be near the top.

Devart · Accepted Answer · 2011-04-05 13:38:08Z

2

CREATE TABLE mytable30(
  `key` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  field2 DATETIME NOT NULL,
  field3 INT(1) UNSIGNED DEFAULT 0,
  field4 INT(10) NOT NULL,
  field5 TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
)

It is better to rename key field. INT(1) - should it be like this INT(11)?

answered Apr 5, 2011 at 13:38

Devart

120k23 gold badges172 silver badges189 bronze badges

Comments

Tommy · Accepted Answer · 2011-04-05 13:37:21Z

0

You cannot use Datetime and Timestamp, you have to choose one.

answered Apr 5, 2011 at 13:37

Tommy

1,2771 gold badge12 silver badges22 bronze badges

Collectives™ on Stack Overflow

MySQL query datetime default

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related