Use UNIX_TIMESTAMP on upgrade instead of timestamp
I want to add a unix timestamped column to see when the last row was changed. Until now, 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 unix timestamp - something like this:
... INT ON UPDATE UNIX_TIMESTAMP() NOT NULL DEFAULT UNIX_TIMESTAMP();
UPDATE:
As I understand it, this thread only shows how to add a unix timestamp manually to each line. I was wondering if it is possible to do this automatically as well.
source to share
The data type TIMESTAMP
is the only one that supports the CURRENT_TIMESTAMP
default in MySQL. Internally, the timestamp is stored as int, but the interface uses the date format.
You have the ability to accomplish what you want:
Save it as a timestamp and apply the function UNIX_TIMESTAMP()
when you select it
select unix_timestamp(insert_time)
from xyz.test;
Store it as 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);
source to share