caiocpricci2 caiocpricci2 - 1 year ago 59
MySQL Question

How to set a default constant value for timestamp columns?

I have a table with a nullable timestamp field (enddate)

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`type` varchar(50) NOT NULL DEFAULT 'FIXED',
`name` varchar(100) NOT NULL,
`description` varchar(255) DEFAULT NULL,
`enddate` datetime NOT NULL DEFAULT '2038-01-18 22:00:00',

I run an insert, passing a null to startdate and enddate, expecting that it would use my default values. So running:

INSERT INTO `fee` (`type`, `name`, `description`, `startdate`, `enddate`)
('FIXED', 'Delivery fee', NULL, NULL, NULL);

Would insert:

27, FIXED, Delivery fee, NULL, NOW(), '2038-01-18 22:00:00'

But it inserts:

27, FIXED, Delivery fee, NULL, NOW(), NOW()

I tried this in mysql 5.5, 5.6 and 5.7, based on their documentation:

Use of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP is specific to TIMESTAMP. The DEFAULT clause also can be used to specify a constant (nonautomatic) default value; for example, DEFAULT 0 or DEFAULT '2000-01-01 00:00:00'.

Question is, what am I doing wrong?

Answer Source

When you set default value for any field in table, you only should pass the value when you actually wants to insert something else rather then default value. You were setting it to null with your query but IN your CREATE query

`enddate` datetime NOT NULL DEFAULT '2038-01-18 22:00:00'

you have set it as it should be 'NOT NULL'.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download