Create table with NULL timestamp columns in MySQL 5.7

Question

`effective_end_utc` timestamp NOT NULL COMMENT 'The UTC timestamp for when the target ceases to be in effect.',

This will end up giving me

ERROR 1067 (42000) at line 27: Invalid default value for 'effective_end_utc'

Base on other reponse, I have even set the mode to following at the begining of schema

SET GLOBAL SQL_MODE='ALLOW_INVALID_DATES'

Any idea whats going wrong?

remove this from the query: COMMENT 'The UTC timestamp for when the target ceases to be in effect.' just use effective_end_utc timestamp NOT NULL — Just_Do_It
– Just_Do_It, Commented Nov 8, 2016 at 16:09
Look at my answer here. And by the way, ALLOW_INVALID_DATES only applies to date(-time), not to timestamp. — Solarflare
– Solarflare, Commented Nov 8, 2016 at 17:35

dinesh707 · Accepted Answer · 2016-11-09 08:24:18Z

3

The schema should be changed with a default value like

 `reported_timestamp_utc` timestamp NULL DEFAULT NULL COMMENT

answered Nov 9, 2016 at 8:24

dinesh707

12.7k22 gold badges88 silver badges138 bronze badges

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

1 Answer 1