Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23715

UTC_TIMESTAMP() should work with ON UPDATE for DATETIME type

    XMLWordPrintable

Details

    Description

      It is now possible to create DATETIME columns with a DEFAULT of UTC_TIMESTAMP(), but it is not possible to use ON UPDATE UTC_TIMESTAMP(). Only ON UPDATE CURRENT_TIMESTAMP() will work.

      For example, this will NOT work:

      CREATE TABLE test_table (
          id INT NOT NULL AUTO_INCREMENT,
          created_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP(),
          updated_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP() ON UPDATE UTC_TIMESTAMP(),
          data VARCHAR(100) NOT NULL DEFAULT '',
          PRIMARY KEY(id)
      );
      

      But this will work:

      CREATE TABLE test_table (
          id INT NOT NULL AUTO_INCREMENT,
          created_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP(),
          updated_at DATETIME NOT NULL DEFAULT UTC_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP(),
          data VARCHAR(100) NOT NULL DEFAULT '',
          PRIMARY KEY(id)
      );
      

      Instead of using TIMESTAMP fields (which stop working for dates past the year 2038),
      it would be nice if you could use DATETIME fields and make them automatically set to UTC when an update occurs.

      Attachments

        Activity

          People

            Unassigned Unassigned
            avrjeff Jeff Groves
            Votes:
            2 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.