SQL：两个日期之间没有年份？

I'm trying to query through historical data and I need to return data just from a 1 month period: 2 weeks back and 2 weeks forward,but I need the year to not matter.

我正在尝试查询历史数据,我需要在1个月内返回数据:2周后和2周前进,但我需要一年无所谓。

So, if I was to make the query today I would want all rows with date between xxxx-06-31 and xxxx-07-27

所以,如果我今天要进行查询,我希望所有行的日期都在xxxx-06-31和xxxx-07-27之间

Thanks in advance for the help!

先谢谢您的帮助!

EDIT: I've tried two ways. both of which I believe will not work around the new year. One is to use datepart(day) and the other would be to simply take the year off of date and compare.

编辑:我尝试了两种方法。我认为这两者都不会在新的一年里发挥作用。一种是使用datepart(day),另一种是简单地将年份与日期进行比较并进行比较。

3 个解决方案

#1

The best way to think of this problem is to convert your dates to a number between 0 and 365 corresponding to the day in the year. Then simply choosing dates where this difference is less than 14 gives you your two week window.

想到这个问题的最佳方法是将日期转换为0到365之间的数字,对应于一年中的某一天。然后,只需选择此差异小于14的日期,即可获得两周的窗口。

That will break down at the beginning or end of the year. But simple modular arithmetic gives you the answer.

这将在年初或年底崩溃。但简单的模运算可以为您提供答案。

Fortunately, MySQL has DAYOFYEAR(date), so it's not so complicated:

幸运的是,MySQL有DAYOFYEAR(日期),所以它不是那么复杂:

SELECT * FROM tbl t
WHERE 
  MOD(DAYOFYEAR(currdate) - DAYOFYEAR(t.the_date) + 365, 365) <= 14
  OR MOD(DAYOFYEAR(t.the_date) - DAYOFYEAR(currdate) + 365, 365) <= 14

That extra + 365 is needed since MySQL's MOD will return negative numbers.

由于MySQL的MOD将返回负数,因此需要额外的+ 365。

This answer doesn't account for leap years correctly. If the current year is not a leap year and the currdate is within 14 days of the end of the year, then you'll miss one day in Jan that you should have included. If you care about that, then you should replace 365 with [the number of days in the year - 1].

这个答案没有正确解释闰年。如果当前年份不是闰年而且该年度是在年底的14天之内,那么您将错过1月份的一天,您应该包括在内。如果您关心这一点,那么您应该用[一年中的天数 - 1]替换365。

#2

Supposed you have a date like this,

假设你有这样的日期,

create table datelist
(
  d date
);

insert into datelist values
('2012-07-01'),
('2011-06-29'),
('2012-07-02'),
('2010-07-05'),
('2012-05-31'),
('2010-06-30');

Try this query below,

试试下面的查询,

SELECT d, date_format(d,'%Y-%b-%d')
FROM datelist
WHERE (MONTH(d) = 6 AND DAYOFMONTH(d) >= 30)
   OR (MONTH(d) = 7 AND DAYOFMONTH(d) <= 27)

SQLFiddle Demo

#3

Is it OK if the solution is terribly slow?

如果解决方案非常慢,可以吗?

    SELECT tbl.*
      FROM tbl
INNER JOIN (SELECT COALESCE(DATE(CONCAT(yyyy, '-', MONTH(CURRENT_DATE), '-', DAYOFMONTH(CURRENT_DATE)),
                            DATE(CONCAT(yyyy, '-02-28'))) AS midpoint
              FROM (SELECT DISTINCT(YEAR(d)) AS yyyy
                     FROM tbl) all_years) adjusted
           ON tbl.datecol BETWEEN adjusted.midpoint - INTERVAL 2 WEEK
                                  AND
                                  adjusted.midpoint + INTERVAL 2 WEEK;

That computes all midpoints for all years in the data set, and then pulls records +- 2 weeks from any such midpoint, which handles end-of-year wrapping.

这将计算数据集中所有年份的所有中点,然后从处理年终包装的任何此类中点开始记录+ - 2周。

The COALESCE handles 02-29 on years without leapday (which MySQL will NULL-ify), forcing it down to 02-28.

COALESCE在没有leapday的情况下处理02-29(MySQL将为NULL-ify),将其强制降至02-28。