Data Mysql Operations

Question

Data Mysql Operations

Navigation

#1 by (2 votes)

1

I would like to know how to do calculations with dates in Mysql.

Table:

ID | DataValidade (DATETIME)
1  | 2017-01-01 00:00:00
2  | 2017-05-01 00:00:00
3  | 2016-06-01 00:00:00

I need to return records whose expiration date is 30 days from today's date. I need to return records whose expiration date is between periods.

I did

SELECT * FROM tabela where DATE_SUB(DataValidade, 30)

but is returning error #1064

Can anyone help me or send links where I have good examples that I can study regarding operations with mysql dates?

php mysql

asked by anonymous 10.05.2016 / 17:19

1 answer

Imprecision of the column sum result Variable does not work in pagination

score 2 · Accepted Answer

1064 is the sqlstate for syntax error, in your case the key word INTERVAL was missing followed by the unit (seconds, minutes, days etc), a functional example would be this:

SELECT date_sub(now(), INTERVAL 1 DAY ) #2016-05-09 12:32:23

All other drive options are at documentation

This query displays the 30-day records that have expired:

SELECT * FROM tabela WHERE DataValidade <= DATE_SUB(now(), INTERVAL 30 DAY)

For records that will expire, change data_sub() to date_add() that adds days (minutes seconds etc) to a date

SELECT * FROM tabela WHERE DataValidade <= DATE_add(now(), INTERVAL 30 DAY)