How to periodically archive data in MySQL

Question

I got this scenario :

I got 2 specific columns which are called cs_start_time(Type:Datetime) and cs_time_length(Type:int (seconds)) in my table.

Now I want to delete these rows automatically when cs_start_time + cs_time_length < NOW() (Current Datetime) and Insert them into another table (of course it should be before deleting these rows).

Or I handle it when someone do a select query on these rows (Trigger alike)

Is this even possible with mysql ? (Im a newbie in mysql)

Answer 1

You can use MySQL event scheduler for this purpose:

I've simulated your scenario creating the main table where data initially stay. Later the expired data are archived in a table named archiveTable through an event scheduler.

• Main table structure and data:

---

-- ----------------------------
-- Table structure for `maintable`
-- ----------------------------
DROP TABLE IF EXISTS `maintable`;
CREATE TABLE `maintable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cs_start_time` datetime NOT NULL,
  `cs_time_length` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

-- ----------------------------
-- Records of maintable
-- ----------------------------
INSERT INTO `maintable` VALUES ('1', '2016-05-01 12:00:00', '10');
INSERT INTO `maintable` VALUES ('2', '2016-05-02 12:00:00', '5');
INSERT INTO `maintable` VALUES ('3', '2016-05-03 12:00:00', '15');

---

• Archive table structure and data:

---

DROP TABLE IF EXISTS `archivetable`;
CREATE TABLE `archivetable` (
  `id` int(11) NOT NULL,
  `cs_start_time` datetime NOT NULL,
  `cs_time_length` int(11) NOT NULL
);

---

• Set event scheduler ON first

SET GLOBAL event_scheduler = ON;

• Create the event:

---

    DROP EVENT IF EXISTS `archiveEvent`;
    delimiter $$
    CREATE EVENT `archiveEvent` ON SCHEDULE EVERY 1 MINUTE STARTS '2016-05-02 00:00:00' ON COMPLETION PRESERVE ENABLE DO

    BEGIN
        INSERT INTO archivetable (
            id,
            cs_start_time,
            cs_time_length
        ) SELECT
            MT.id,
            MT.cs_start_time,
            MT.cs_time_length
        FROM
            maintable MT
        WHERE
            MT.cs_start_time + INTERVAL MT.cs_time_length SECOND < NOW() ; 

        DELETE
        FROM
            maintable
        WHERE
            cs_start_time + INTERVAL cs_time_length SECOND < NOW() ;
        END$$

    delimiter ;

Note: Look the event start time is set to 2016-05-02 00:00:00. After that the event will be scheduled in every one minute interval. You can change the schedule time in any interval unit as you like.

Suggestion:

Quoting from my comment:

You can use mysql event scheduler for this purpose. But I would suggest you can filter out these entries in your select query. What's the big deal of deleting those entries whereas you can easily bypass these in your select query? You have to check every second through the scheduler whether they meet the condition to be deleted or not;