Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that is as follows:

CREATE TABLE SomeTable (  
    id int unsigned NOT NULL AUTO_INCREMENT,  
    status enum('broken','repaired') NOT NULL,  
    from datetime NOT NULL,  
    until datetime NOT NULL,  
    brought_in timestamp,  
    brought_by varchar(255) NOT NULL,  
    repair_status enum('unpayed', 'payed') DEFAULT NULL,  
    payed_when timestamp,    
    delivered_when timestamp,  
    primary key(id)    
);  

When I create the table if I do a SHOW CREATE TABLE then I see that the column brought_in is defined to have the current timestamp ON INSERT and ON UPDATE.
But I did not add such an instruction in my create table.
Why doesn't it let it be empty like the other columns?
Now on every insert or update of another column it adds the current timestamp. I don't want that.
It does not do that with the other columns. What am I doing wrong?

share|improve this question
2  
That's the default behaviour of the (first) timestamp column. Read the documentation: Automatic Initialization and Updating for TIMESTAMP. If you don't want this, you have to explicitly tell MySQL. –  ypercube Oct 18 '13 at 12:11
    
Use brought_in timestamp NULL DEFAULT NULL, or some other default value and you'll have no automatic properties. –  ypercube Oct 18 '13 at 12:17
    
@ypercube:Really?Why does it do that?Any idea on the logic behind? –  Jim Oct 18 '13 at 12:27
    
I don't know why the original developers made that choice. They also made the inconsistent choice that TIMESTAMP columns are by default NOT NULL while all other columns are by default NULL ... –  ypercube Oct 18 '13 at 12:33
    
@ypercube:Why is this a comment and not an answer? –  Jim Oct 18 '13 at 12:46

1 Answer 1

up vote 1 down vote accepted

This is the default behaviour of the (first) timestamp column. Many more details can be read in the documentation: Automatic Initialization and Updating for TIMESTAMP.

If you don't want this, you have to explicitly tell MySQL when you create the table. You can declare a default value (or default null) and the automatic properties will be supressed. Any of the following will do (with differences in behaviour explained):


Option 1: not null and default 0

brought_in timestamp NOT NULL DEFAULT 0,             -- option 1a

brought_in timestamp DEFAULT 0,                      -- option 1b

These two options are identical. The column is defined as NOT NULL and has a default value of 0. Note that TIMESTAMP is the only datatype that has a default property of NOT NULL, in contrast with all other datatypes and with the ISO/ANSI standard.

If you try to insert NULL in the column, the CURRENT_TIMESTAMP is inserted (surprise, another MySQL feature.)


Option 2: null and default null

brought_in timestamp NULL DEFAULT NULL,              -- option 2a

brought_in timestamp NULL,                           -- option 2b

These are identical, they allow NULL (and both result in default being NULL as well.)

If you try to insert NULL in the column, it is allowed and inserted fine (no surprises here.)


Option 3: null and default 0 or some other constant

brought_in timestamp NULL DEFAULT 0,                 -- option 3a

brought_in timestamp NULL DEFAULT '2013-01-01',      -- option 3b

This is similar to option 2 but has a different default value.

If you try to insert NULL in the column, it is allowed and inserted fine, too (no surprises here either.)

share|improve this answer
    
I was wondering is this behavior outside of the SQL standard?I mean how these data types behave aren't they part of the SQL standard? –  Jim Oct 18 '13 at 14:26
    
Datetimes have been implemented with quite many differences in the various DBMS. –  ypercube Oct 18 '13 at 14:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.