SQL:没有年份的两个日期之间的哪个位置? [英] SQL: Where between two dates without year?

问题描述

我正在尝试查询历史数据，我只需要返回1个月内的数据:后2周和前2周，但是我需要年份没关系.

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.

因此，如果今天要进行查询，我希望所有在xxxx-06-31和xxxx-07-27之间带有date的行

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

提前感谢您的帮助！

我尝试了两种方法.我相信这两种方法都不会在新的一年左右生效.一种是使用datepart(day)，另一种是简单地将年份取下日期并进行比较.

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.

推荐答案

解决此问题的最佳方法是将日期转换为0到365之间的数字，该数字与一年中的某天相对应.然后，只需选择差异小于14的日期即可为您提供两周的时间窗口.

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.

这将在年初或年底分解.但是简单的模块化算法可以为您提供答案.

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

幸运的是，MySQL具有DAYOFYEAR(date)，所以它并不那么复杂:

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

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

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

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

此答案不能正确说明leap年.如果当前年份不是a年，并且日期在该年末之后的14天内，那么您会错过1月中应该包含的一天.如果您对此感到担心，则应将365替换为[the number of days in the year - 1].

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].

这篇关于SQL:没有年份的两个日期之间的哪个位置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！