Bug #41137 TIMESTAMP column with no default value
Submitted: 30 Nov 2008 16:17 Modified: 16 Jan 2014 18:01
Reporter: Viktor Štujber Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[30 Nov 2008 16:17] Viktor Štujber
Description:
For some reason, it seems MySQL won't let you create a TIMESTAMP column with no default value. If you omit the DEFAULT part of the ADD COLUMN query, MySQL automatically fills in "default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP". This is different from other column types where not specifying a default makes the column mandatory in INSERT statements.

If you're using a TIMESTAMP column to just store date/time information (as a timezone-aware alternative to DATETIME), having a forced default value might not be what you want.

How to repeat:
Try
ALTER TABLE `test` ADD `Timestamp` TIMESTAMP NOT NULL;

Then
SHOW CREATE TABLE `test`;

And
see that MySQL added the abovementioned default & on-update attribute.
[13 Jan 2009 16:50] Susanne Ebrecht
Many thanks for writing a bug report.

Which MySQL version did you use?
[13 Jan 2009 22:45] Viktor Štujber
This was last tried on MySQL 5.0.75 (FreeBSD).
I do not have 5.1 or higher available for testing, sorry.
[16 Jan 2009 16:04] Susanne Ebrecht
Many thanks for writing a feature request.

The behaviour of timestamp is documented. Just an example you will find here:

http://dev.mysql.com/doc/refman/5.1/en/timestamp.html

"In a CREATE TABLE  statement, the first TIMESTAMP column can be declared in any of the following ways: "
...
"With neither DEFAULT nor ON UPDATE clauses, it is the same as DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP."
[16 Jan 2009 17:17] Viktor Štujber
Indeed, that's why it is a feature request and not a bugreport :)

Summary: It is impossible to have a TIMESTAMP column without default value. Sometimes, I might not want some of my timestamp columns to have a default value.
Rationale: All other column types have normal consistent behavior w.r.t. default values - if you don't specify it, there isn't any, and the column cannot be omitted in insert queries.
[9 Oct 2012 19:48] Christopher Smith
Any chance we can reopen this? It'd be nice to be able to have a TIMESTAMP field that was required to be provided at insert time.
[24 Nov 2013 11:36] Bernhard Breytenbach
Seems to be a very old bug, not sure why its not fixed.....
Workaround I found was:

ALTER TABLE `table_name` MODIFY COLUMN `column_name` TIMESTAMP NOT NULL DEFAULT 0;
ALTER TABLE `table_name` ALTER  COLUMN `column_name` DROP DEFAULT;
[16 Jan 2014 18:01] Viktor Štujber
It seems this is addressed in MySQL 5.6.6 via the 'explicit_defaults_for_timestamp' option and the deprecation notice on each server startup. Yay, finally. Note that the option only affects newly created columns.