SET A default value for a MySQL Datetime column

If you want a table which populates automatically a DATETIME column with the current date/time value, We may do something INCORRECT like this:

CREATE TABLE example( 
  name VARCHAR(15), 
  current_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

Why incorrect? Actually is correct, but you must be aware that the range of TIMESTAMP is only 1970-2038. So we could get unexpected errors when working with higher ranges.

The faster and easier approach I found is the use of triggers:

CREATE TRIGGER example_on_insert BEFORE INSERT ON example 
    FOR EACH ROW SET NEW.current_date = IFNULL(NEW.dateAdded, NOW());

The IFNULL is to add the automatic DATETIME value just in case the current_date hasn’t been defined in the INSERT query.
Otherwise we only be able to set the current_date manually using the UPDATE query.

Leave a Reply

Your email address will not be published. Required fields are marked *