[MDEV-23715] UTC_TIMESTAMP() should work with ON UPDATE for DATETIME type Created: 2020-09-11  Updated: 2020-09-11

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Create Table
Fix Version/s: None

Type: Task Priority: Minor
Reporter: Jeff Groves Assignee: Unassigned
Resolution: Unresolved Votes: 2
Labels: None


 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.
Generated at Thu Feb 08 09:24:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.