Details
-
Task
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
-
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.