0

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.

NS - Not the train
  • 302
  • 3
  • 10

1 Answers1

0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • current_timestamp works indeed, but that does not give the UTC time which is why I’m still searching. – NS - Not the train Jan 23 '21 at 23:23
  • @NoahSnoeks . . . The value is stored internally as UTC. You can use `convert_tz()` to convert *back* to UTC when you need to. The "visible" timestamp is just a convenience. – Gordon Linoff Jan 23 '21 at 23:29