从数据库日期中选择日期为今天和多个

There are a method with SQL to SELECT from one database the date records where the date is today and the date is multiple of two years.

有一种方法可以使用SQL从一个数据库中选择日期记录,其中日期是今天,日期是两年的倍数。

For example, i have a table call "list". That table have two column, 'ID' and 'last_date'. One of this record is: ID = '1' and date = '17-03-2015'

例如,我有一个表调用“列表”。该表有两列,'ID'和'last_date'。其中一条记录是:ID ='1'和date = '17 -03-2015'

I need to select all record where the date is the date on the table + 2 year. For example today the query will return the ID 1.

我需要选择所有记录,其中日期是表格上的日期+ 2年。例如,今天查询将返回ID 1。

Thanks to all.

谢谢大家。

2 个解决方案

#1

Use DATE_SUB() to subtract 2 years from today's date, and compare that to the column.

使用DATE_SUB()从今天的日期减去2年,并将其与列进行比较。

SELECT id
FROM list
WHERE last_date = DATE_SUB(CURDATE(), INTERVAL 2 YEAR);

This is a little better than @Teja's solution because it only has to do the date arithmetic once, rather than for every row in the table. And if there's an index on the last_date column, it will be able to use it to find the rows quickly.

这比@Teja的解决方案好一点,因为它只需要进行一次日期算术,而不是表格中的每一行。如果last_date列上有索引,它将能够使用它快速查找行。

#2

We can write an expression that returns a date value that is exactly two days before today's date:

我们可以编写一个返回日期值的表达式,该日期值恰好是今天日期的前两天:

 SELECT DATE(NOW()) + INTERVAL -2 YEAR

We can use an expression in the WHERE clause of a query. If the column in the table we want to check is defined as DATE datatype:

我们可以在查询的WHERE子句中使用表达式。如果我们要检查的表中的列被定义为DATE数据类型:

 SELECT t.id
   FROM mytable t
  WHERE t.mydatecol = DATE(NOW()) + INTERVAL -2 YEAR

If it's defined as a DATETIME, the normal pattern would be range check

如果它被定义为DATETIME,则正常模式将是范围检查

 SELECT t.id
   FROM mytable t
  WHERE t.mydatecol >= DATE(NOW()) + INTERVAL -2 YEAR 
    AND t.mydatecol  < DATE(NOW()) + INTERVAL -2 YEAR + INTERVAL 1 DAY

If the column is stored as a VARCHAR in a non-canonical format e.g. DD-MM-YYYY then we could either attempt to convert that to a DATE using STR_TO_DATE (which we don't like to do because the query can't make effective use of a index), or we could convert our generated date value into the required string format for an equality comparison:

如果列以非规范格式存储为VARCHAR,例如DD-MM-YYYY然后我们可以尝试使用STR_TO_DATE将其转换为DATE(我们不喜欢这样做,因为查询无法有效地使用索引),或者我们可以将生成的日期值转换为相等比较所需的字符串格式:

 SELECT t.id
   FROM mytable t
  WHERE t.ddmmyyyy = DATE_FORMAT(DATE(NOW()) + INTERVAL -2 YEAR,'%d-%m-%Y')

That would get us exact match to '17-03-2015', but not to '17-3-2015'. And we have to do equality test or IN list, we can't do range check, because the value stored in the column isn't canonical.

这将使我们完全匹配'17 -03-2015',但不会到'17 -3-2015'。而且我们要做相等测试或IN列表,我们不能做范围检查,因为存储在列中的值不是规范的。

If we need to look for multiple dates... today, two years ago, four years ago, six years ago, ... we can generate a list of dates and perform a join operation. (Assuming that mydatecol is defined as DATETIME...)

如果我们需要寻找多个日期......今天,两年前,四年前,六年前......我们可以生成一个日期列表并执行连接操作。 (假设mydatecol被定义为DATETIME ...)

 SELECT t.id
   FROM ( SELECT DATE(NOW()) + INTERVAL 0 YEAR AS dt
          UNION ALL SELECT DATE(NOW()) + INTERVAL -2 YEAR
          UNION ALL SELECT DATE(NOW()) + INTERVAL -4 YEAR
          UNION ALL SELECT DATE(NOW()) + INTERVAL -6 YEAR
          UNION ALL SELECT DATE(NOW()) + INTERVAL -8 YEAR
          UNION ALL SELECT DATE(NOW()) + INTERVAL -10 YEAR
          UNION ALL SELECT DATE(NOW()) + INTERVAL -12 YEAR
          UNION ALL SELECT DATE(NOW()) + INTERVAL -14 YEAR
          UNION ALL SELECT DATE(NOW()) + INTERVAL -16 YEAR
          UNION ALL SELECT DATE(NOW()) + INTERVAL -18 YEAR
          UNION ALL SELECT DATE(NOW()) + INTERVAL -20 YEAR
        ) l
   JOIN mytable t
  WHERE t.mydatecol >= l.dt + INTERVAL 0 DAY
    AND t.mydatecol <  l.dt + INTERVAL 1 DAY

#1