Use UNIX_TIMESTAMP on update instead of timestamp

I want to add a column with a unix timestamp to see when the row was last altered. So far i could only figure out how to add a column with timestamp format.

ALTER TABLE xyz.test ADD `insert_time` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

Is there any solution to use a unix timestamp – something like this:

... INT ON UPDATE UNIX_TIMESTAMP() NOT NULL DEFAULT UNIX_TIMESTAMP();

UPDATE:

similar question

As far as i understand this thread only shows how to add a unix timestamp manually to each row. I was wondering if it is also possible to do this automatically.

Answer

The TIMESTAMP data type is the only one that supports the CURRENT_TIMESTAMP default in MySQL. Internally the timestamp is stored as an int, but the interface uses a date time format.

You have some options to accomplish what you want:

Store it as a timestamp, and apply the UNIX_TIMESTAMP() function when you select from it

select unix_timestamp(insert_time)
from xyz.test;

Store it as an int, and use a trigger to populate the value

ALTER TABLE xyz.test 
  ADD `insert_time_unix` INT NULL;

create trigger tr_b_ins_test before insert on xyz.test 
  for each row 
  set new.insert_time_unix = unix_timestamp(insert_time);

create trigger tr_b_upd_test before update on xyz.test 
  for each row 
  set new.insert_time_unix = unix_timestamp(insert_time);