Time limit for each post

Question

I need a solution to delete a post or a record from the database in 3 days, in case the post will be available 3 days and then will be deleted. I think to use the following line:

DELETE FROM posts WHERE data_postagem (aqui que complica, penso em + 3 dias, porém como digitaria isso?) );

Answer 1

You can use two functions for this: ADDDATE or DATE_ADD to add dates or DATEDIFF to calculate the difference between dates.

Structure and Data of Examples:

CREATE TEMPORARY TABLE `postagem` (
    `id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `titulo` VARCHAR(60) NOT NULL,
    `data_postagem` DATETIME DEFAULT "2018-10-23 00:00:00"
);

INSERT INTO `postagem` VALUES 
(null, 'Título 1', '2018-10-01 00:00:00'),
(null, 'Título 2', '2018-10-02 00:00:00'),
(null, 'Título 3', '2018-10-03 00:00:00'),
(null, 'Título 4', '2018-10-04 00:00:00'),
(null, 'Título 5', '2018-10-05 00:00:00'),
(null, 'Título 6', '2018-10-06 00:00:00'),
(null, 'Título 7', '2018-10-07 00:00:00'),
(null, 'Título 8', '2018-10-08 00:00:00'),
(null, 'Título 9', '2018-10-09 00:00:00'),
(null, 'Título 10', '2018-10-10 00:00:00'),
(null, 'Título 11', '2018-10-11 00:00:00'),
(null, 'Título 12', '2018-10-12 00:00:00'),
(null, 'Título 13', '2018-10-13 00:00:00'),
(null, 'Título 14', '2018-10-14 00:00:00'),
(null, 'Título 15', '2018-10-15 00:00:00'),
(null, 'Título 16', '2018-10-16 00:00:00'),
(null, 'Título 17', '2018-10-17 00:00:00'),
(null, 'Título 18', '2018-10-18 00:00:00'),
(null, 'Título 19', '2018-10-19 00:00:00'),
(null, 'Título 20', '2018-10-20 00:00:00'),
(null, 'Título 21', '2018-10-21 00:00:00'),
(null, 'Título 22', '2018-10-22 00:00:00'),
(null, 'Título 23', '2018-10-23 00:00:00'),
(null, 'Título 24', '2018-10-24 00:00:00'),
(null, 'Título 25', '2018-10-25 00:00:00'),
(null, 'Título 26', '2018-10-26 00:00:00'),
(null, 'Título 27', '2018-10-27 00:00:00'),
(null, 'Título 28', '2018-10-28 00:00:00'),
(null, 'Título 29', '2018-10-29 00:00:00'),
(null, 'Título 30', '2018-10-30 00:00:00'),
(null, 'Título 31', '2018-10-31 00:00:00');

Example with ADDDATE:

As already mentioned, the function adddate to add dates. This way we can add the date of posting + 3 (days). If the date is less than or equal to the current date, then it means that the term has expired. The function expression is: ADDDATE('data', 'número de dias a serem somados');, for example:

SELECT * FROM postagem WHERE ADDDATE(data_postagem, 3) <= CURDATE();

The above code will print every day that summed with 3, is equal to or less than the current date.

Demonstration

---

Example with DATEDIFF:

Here we can calculate how many days there are of difference between the data1 and the data2. It is a simpler function and, in your case, I believe it will be better used. The expression of the function is: DATEDIFF('data 1', 'data 2');. Return will be a positive or negative integer value.

SELECT * FROM postagem WHERE DATEDIFF(data_postagem, CURDATE()) <= -3;

The above code will also print every day that the day difference is equal to or greater than 3.

Demonstration