Can't default date to CURRENT_TIMESTAMP in MySQL 5.5

  • I am not able to set Current_timestamp as default value. My Mysql version is 5.5.47.

    Query is

    ALTER TABLE `downloads` ADD `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ;
    

    enter image description here

    While it is working fine on my local DB with mysql V5.6.56.

    The problem is due to microseconds added in default value in mysql new versions. See http://tekina.info/default-datetime-timestamp-issue-mysql-upgrading-5-6/ for solutions.

  • Philᵀᴹ

    Philᵀᴹ Correct answer

    4 years ago

    From the MySQL 5.5 manual:

    You cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

    Therefore, what you want to achieve will work in MySQL 5.5 if you add a TIMESTAMP column instead of a DATE column.

    The changes in 5.6.x that allow the functionality are documented here, and I'll quote the relevant summary for completeness:

    As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table.

  • Check this answer.

    Your options are:

    • Upgrade to MySQL 5.6.5
    • Change the column type to TIMESTAMP, as in:

      ALTER TABLE `downloads` ADD `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
      
    • Create a TRIGGER THAT updates the column automatically:

      ALTER TABLE `downloads` ADD `date` DATETIME NULL; -- date must allow
                                                        -- NULLs or default
                                                        -- to a special value
      DROP TRIGGER IF EXISTS downloads_BI;
      DELIMITER //
      CREATE TRIGGER downloads_BI
      BEFORE INSERT ON downloads FOR EACH ROW
      BEGIN
          IF (NEW.date IS NULL) THEN -- change the isnull check for the default used
              SET NEW.date = now();
          END IF;
      END//
      DELIMITER ;
      

      You may want to create an update value, too, if it must be automatically updated on update or want to prevent null values.

      mysql> INSERT INTO downloads (i) VALUES (1); -- I do not set date
      Query OK, 1 row affected (0.00 sec)
      
      mysql> SELECT * FROM downloads;
      +------+---------------------+
      | i    | date                |
      +------+---------------------+
      |    1 | 2016-03-22 09:27:52 |
      +------+---------------------+
      1 row in set (0.00 sec)
      

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM