MySQL Timestamp Question

Question

i am looking at some practice questions

Assume that you've just created this table:


CREATE TABLE timestamptest (
ts1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
i INT
);


When you look at its structure, you will notice that the TIMESTAMP column is declared NOT NULL. What happens if you insert these records:

mysql> INSERT INTO timestamptest SET ts1=NULL, i=10; 
mysql> INSERT INTO timestamptest SET ts1=0, i=11; 
mysql> INSERT INTO timestamptest SET ts1='', i=12;

the ans is

Only the first statement succeeds, and the TIMESTAMP column is set to the current date and time. The other two statements give an error:

mysql> INSERT INTO timestamptest SET ts1=NULL, i=10;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO timestamptest SET ts1=0, i=11;
ERROR 1292 (22007): Incorrect datetime value: '0' for column 'ts1' at row 1
mysql> INSERT INTO timestamptest SET ts1='', i=12;
ERROR 1292 (22007): Incorrect datetime value: '' for column 'ts1' at row 1

but when i tried, inserting ts1=0 works, it inserts a zero value timestamp ... is the answer wrong?

laalto · Accepted Answer · 2009-08-24 11:17:04Z

4

It depends whether the NO_ZERO_DATE option is set:

mysql> create table foo(x timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo values(0);
Query OK, 1 row affected (0.00 sec)

mysql> set sql_mode='strict_all_tables,no_zero_date';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into foo values(0);
ERROR 1292 (22007): Incorrect datetime value: '0' for column 'x' at row 1

answered Aug 24, 2009 at 11:17

laalto

153k68 gold badges293 silver badges306 bronze badges

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

Collectives™ on Stack Overflow

MySQL Timestamp Question

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related