MySQL TIMESTAMP to default NULL, not CURRENT_TIMESTAMP

Question

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;

Norvell

Answer 1

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;

Illsorted

This worked for me. I created the table and while doing so I directly gave my timestamp column NULL DEFAULT NULL and it worked! – Teishateixeira

Earlier I was trying to create the timestamp column as COLUMN_NAME DEFAULT NULL which didn't work at all. – Teishateixeira

Answer 2

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.

Lynnettelynnworth

Answer 3

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.

Jorie

Answer 4

I thought this should work:

ALTER TABLE customers ADD COLUMN s_timestamp TIMESTAMP DEFAULT NULL;

Ligurian

It doesn't. 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. – Velleman

Duly noted, but the question is about MySQL behavior. – Velleman