MYSQL query to alter timestamp column

Question

MYSQL VERSION - 5.7.14

In mysql version I am not able to insert timestamp value with null in insert query.

I tried to alter the timestamp coulmn in mysql with the below query:

ALTER TABLE `business` MODIFY `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;
ALTER TABLE `business` MODIFY `modified` TIMESTAMP on update CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL;

But I am getting the error #1067 - Invalid default value for 'modified'

remove `NOT NULL` from the end of each line you want to set null — Mojtaba, Mar 19 '17 at 21:55
If you try `NOT NULL DEFAULT 'CURRENT_TIMESTAMP' ` ? http://stackoverflow.com/a/15012579/1363190 — Michael Krikorev, Mar 19 '17 at 22:01
I run the first query removing the `not null`. still getting the same error. — Joshi, Mar 19 '17 at 22:01
`ALTER TABLE business MODIFY created TIMESTAMP NOT NULL DEFAULT 'CURRENT_TIMESTAMP'` either? — Michael Krikorev, Mar 19 '17 at 22:06
@MichaelK - `CURRENT_TIMESTAMP` should not be in quotes. Even so keeping the quotes or removing the quotes. the query suggested generates error in sql syntax — Joshi, Mar 19 '17 at 22:12
Does this give an error to? `ALTER TABLE business MODIFY created TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP;` (If you want to allow null, else remove the `NULL`) — Michael Krikorev, Mar 19 '17 at 22:23

Michael Krikorev · Answer 1 · 2017-03-19T22:31:59.560

This should work:

// TO ALLOW NULL AND SET CURRENT_TIMESTAMP AS DEFAULT:
ALTER TABLE `business` MODIFY `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP;
// TO ALLOW NULL AND SET CURRENT_TIMESTAMP AS DEFAULT AND ON UPDATE:  
ALTER TABLE `business` MODIFY `modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

If you do not want to allow null then just remove the NULL:

// DO NOT ALLOW NULL, SET CURRENT_TIMESTAMP AS DEFAULT:
ALTER TABLE `business` MODIFY `created` TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
// DO NOT ALLOW NULL, SET CURRENT_TIMESTAMP AS DEFAULT AND ON UPDATE:  
ALTER TABLE `business` MODIFY `modified` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

MYSQL query to alter timestamp column

1 Answers1