Using MySQL, I'm trying to make a timestamp column from a date column and a time column. If the date or time column contains a NULL value, MySQL automatically sets the corresponding value in the TIMESTAMP column to the CURRENT_TIMESTAMP.
Is there a way to make it default to a NULL, instead of the CURRENT_TIMESTAMP?
Something like:
ALTER TABLE customers ADD s_timestamp TIMESTAMP;
UPDATE customers
SET s_timestamp = timestamp(s_date,s_time) DEFAULT NULL;
Use this query to add your column
ALTER TABLE `customers`
ADD COLUMN `s_timestamp` TIMESTAMP NULL DEFAULT NULL;
And, if you want to get current timestamp on update only :
ALTER TABLE `customers`
ADD COLUMN `s_timestamp` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP;
To add to accepted answer in case someone is looking to modify an existing column that was created defaulting to current_timestamp:
alter table `customers` modify `s_timestamp` timestamp null
The column will now not default to current_timestamp anymore.
Whilst this seems to have been answered, we have a createdAt and updatedAt TIMESTAMP(6) columns.
`createdAt` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
`updatedAt` timestamp(6) NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6),
In our application code, we never set these values (unless fixing something and want to preserve the any original updatedAt or cloning/importing data and these values are known).
If you have null date/time when building your timestamp, then testing them first is the one viable option.
UNTESTED
UPDATE table SET column = CASE
WHEN s_date IS NULL OR s_time IS NULL THEN null
ELSE TIMESTAMP(s_date, s_time)
END
From memory, the default behaviour takes place if the column is not mentioned in the query.
I thought this should work:
ALTER TABLE customers ADD COLUMN s_timestamp TIMESTAMP DEFAULT NULL;
TIMESTAMP columns have special automatic behavior when NULL is inserted, so if you really want to be able to set a null marker, you have to explicitly make the column nullable.