Menu
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; 
323
If the explicit_defaults_for_timestamp system variable is disabled, TIMESTAMP columns by default are NOT NULL , cannot contain NULL values, and assigning NULL assigns the current timestamp. To permit a TIMESTAMP column to contain NULL , explicitly declare it with the NULL attribute.
TIMESTAMP Initialization and the NULL AttributeDEFAULT NULL can be used to explicitly specify NULL as the default value. (For a TIMESTAMP column not declared with the NULL attribute, DEFAULT NULL is invalid.) If a TIMESTAMP column permits NULL values, assigning NULL sets it to NULL , not to the current timestamp.
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'.
In order to allow a TIMESTAMP to be nullable, create it using the NULL attribute, or alter the table and add the NULL attribute. In a create statement, it would resemble. CREATE TABLE t1 (tsvalue TIMESTAMP 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; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
