sqlmariadbutconupdate

MariaDB native UTC time on update

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.

Solution

  • 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.