MySQL: Calculate width date in column

15
February 11, 2019, at 08:50 AM

I want to define a cron that freqently delete specific data from a MySQL column if date in date_added column is older than 60 days, but don't find any infomation if MySQL supports calculation width date. Is that possible and if yes how to do?

DELETE FROM 
vcount 
WHERE date_added - 60 days

date_added == datetime == 0000-00-00 00:00:00

Answer 1

assuming your date_added is a valid date column you can use date_sub

DELETE FROM 
vcount 
WHERE date_sub(date_added, INTERVAL 60 DAY);
Answer 2

use DATE_SUB(date, INTERVAL value interval)

date : Required. The date to be modified

value : Required. The value of the time/date interval to subtract. Both positive and negative values are allowed

interval: Required. The type of interval to subtract. Can be one of the following

values:

MICROSECOND

SECOND

MINUTE

HOUR

DAY

WEEK

MONTH

QUARTER

YEAR

SECOND_MICROSECOND

MINUTE_MICROSECOND

MINUTE_SECOND

HOUR_MICROSECOND

HOUR_SECOND

HOUR_MINUTE

DAY_MICROSECOND

DAY_SECOND

DAY_MINUTE

DAY_HOUR

YEAR_MONTH

DELETE FROM 
vcount 
WHERE date_added  < (DATE_SUB(NOW(), INTERVAL 60 DAY))
Answer 3

You can use either date_add() or date_sub() functions:

where date_added < date_add(curdate(),INTERVAL -60 DAY)

or

where date_added < date_sub(curdate(),INTERVAL 60 DAY)

(many prefer to always use date_add() using a negative interval for subtraction)

READ ALSO
column count doesn&#39;t match the value count at row 1_database insert error

column count doesn't match the value count at row 1_database insert error

Been working on this for a whileFor some reason even though I have enough values and columns, I get "column count doesn't match value count at row 1" error

41
cannot get id from ajax

cannot get id from ajax

I can't get the id from the js file, I don't know if the js code or the php code is wrongCan someone help? problem seems to be from index

11
Inserting 2 rows instead of one: angular and php

Inserting 2 rows instead of one: angular and php

I am working with angular 6 and phpWhen i add an entity whith my application, i find that the backend is inserting 2 rows in the table instead of one

34
Merge dataframes by left join SQL &amp; Pandas

Merge dataframes by left join SQL & Pandas

I made two tables into a MySQL database using PythonThe following SQL code is to perform join on two tables in the database

43