0

I have a table named tab1. I want add a new column to the table. I tried 

alter table tab1 add column mod_at TIMESTAMP default CURRENT_TIMESTAMP

I succeeded in adding the column. But the default timestamp value added will be local timestamp. How to add the UTC timestamp as default value, instead of local timestamp?

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Virat
  • 551
  • 4
  • 9
  • 23

2 Answers2

1

I suspect you misunderstand how TIMESTAMP column type works. This column always uses UTC internally, but it converts from/to server/session time zone when you read or write. You can run this query to see your session's time zone:

SELECT @@session.time_zone

Now, if you change the time zone, e.g.:

SET @@session.time_zone = '+00:00'

... you'll see that the values already stored change to match the new time zone. That's the expected behaviour.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
-1

you could use UTC_TIMESTAMP() instead

LONG
  • 4,490
  • 2
  • 17
  • 35