MariaDB native UTC time on update

Question

I’m searching for a value that changes the value of a column to the current UTC time when the row is updated, but UTC_TIMESTAMP and GETUTCDATE only works as default value, not when used in ON UPDATE.

Is there a solution or do I have to give the database the UTC time manually via the application.

P.S. CURRENT_TIMESTAMP works, but does not give UTC time.

Answer 1

You can set a default value to automatically increment on an update:

This column updates only on insert:

inserted_at DATETIME DEFAULT CURRENT_TIMESTAMP

This column updates on insert or update:

updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Here is a db<>fiddle.

Note: These work with timestamp as well as datetime, if that is your preference.