CODEWITHSUNDEEP
mysqlsql
RandomDude
RandomDude

Reputation: 1141

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.

Upvotes: 7

Views: 4042

Answers (2)

Maxim Rysevets
Maxim Rysevets

Reputation: 156

I was looking for a solution for my CMS EFFCORE and I found it. Here is a 100% working example:

CREATE TABLE `demo` (
    `id` int NOT NULL AUTO_INCREMENT,
    `text` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
    `updated_at` int NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
);

DELIMITER ;;
CREATE TRIGGER `updated_at__on_insert`
BEFORE INSERT
ON `demo`
FOR EACH ROW
BEGIN
   SET new.updated_at = UNIX_TIMESTAMP();
END;;
DELIMITER ;

DELIMITER ;;
CREATE TRIGGER `updated_at__on_update`
BEFORE UPDATE
ON `demo`
FOR EACH ROW
BEGIN
   SET new.updated_at = UNIX_TIMESTAMP();
END;;
DELIMITER ;

p.s. None of the other solutions presented here describe everything accurately and clearly.

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65567

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);

Upvotes: 5

Related Questions