Re: default current_timestamp problem

[SGreen]

Scott Purcell [EMAIL PROTECTED] wrote on 06/07/2005 04:29:56 PM:

 Hello,
 I am trying to create simple reference table for some cookies I am 
 creating. I wanted to put in a current_timestamp each time I do an 
 insert so I can delete this data after 2 or 3 weeks.

 Here is the insert statement:
 CREATE TABLE COOKIE_REF (cookie_ref varchar(50), dat timestamp 
 default current_timestamp)

 I am trying to insert the below statement, but it fails with a 1064
 Error Code : 1064
 You have an error in your SQL syntax.  Check the manual that 
 corresponds to your MySQL server version for the right syntax to use
 near 'current_timestamp)' at line 1
 (0 ms taken)

 
 I am running version 4.0.15-max-debug on windows. and following this
 link but It will not work. What can I be doing wrong?

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

 
 Thanks,
 Scott

You are not reading far enough

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

 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 as of MySQL 4.1.2. See Section 
11.3.1.2, TIMESTAMP Properties as of MySQL 4.1.


You are not running 4.1.2 or newer, that's why you are getting an error 
trying to set the default value for a timestamp column.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

RE: default current_timestamp problem

[J.R. Bullington]

You do not need to set a default value if you want the current_timestamp.
Just leave the default option out when creating your table and the server
will do it for you.

CREATE TABLE COOKIE_REF (
cookie_ref varchar(50),
dat timestamp
);


OR

If you really want to put in a value for the default, use

CREATE TABLE COOKIE_REF (
cookie_ref varchar(50),
dat timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP
);

J.R.



-Original Message-
From: Scott Purcell [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 07, 2005 4:30 PM
To: mysql@lists.mysql.com
Subject: default current_timestamp problem




Hello,
I am trying to create simple reference table for some cookies I am creating.
I wanted to put in a current_timestamp each time I do an insert so I can
delete this data after 2 or 3 weeks.

Here is the insert statement:
CREATE TABLE COOKIE_REF (cookie_ref varchar(50), dat timestamp default
current_timestamp)

I am trying to insert the below statement, but it fails with a 1064 Error
Code : 1064 You have an error in your SQL syntax.  Check the manual that
corresponds to your MySQL server version for the right syntax to use near
'current_timestamp)' at line 1 (0 ms taken)


I am running version 4.0.15-max-debug on windows. and following this link
but It will not work. What can I be doing wrong?

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




Thanks,
Scott

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]