mysql - 如何获取用户在SQL中输入的一个月前的数据

Question

当前查询

SELECT col1 FROM table1 where 
id=1234 and (date(sys_time) 
between "2015-07-01" AND "2015-07-10")
;

I want to get the data prior to one month from the dates mentioned here. Is there any SQL query to do it?

Answer 1

只需使用date_sub():

SELECT col1
FROM table1 
WHERE id = 1234 AND 
      sys_time >= date_sub('2015-07-01', interval 1 month) and
      sys_time < date_add(date_sub('2015-07-01', interval 1 month), interval 1 day) 

请注意，我从 sys_time 中删除了 date() 函数。这有助于 MySQL 使用表达式的索引(如果可用):