首页 文章

仅按月和日比较日期

提问于
浏览 次
3

我的 foos 表中有 date 字段 . 所以现在我需要从日期间隔中选择所有foos，但只比较月和日 .

例如，我需要在 7 Jan 2012 - > 28 Dec - 17 Jan 之前和之后10天选择所有foos . 或者周围 10 Jul 2009 - > 3 Jul - 17 Jul . 它应该只根据所有年份的月份和日期进行选择 .

我原始而天真的解决方案

SELECT b_date FROM foos 
  WHERE DAYOFYEAR(b_date) BETWEEN 
    DAYOFYEAR('2011-01-07' - INTERVAL 10 DAY) AND (DAYOFYEAR('2011-01-07' - INTERVAL 10 DAY) + 20)
  OR DAYOFYEAR(b_date) BETWEEN 
    (DAYOFYEAR('2011-01-07' + INTERVAL 10 DAY) - 20) AND DAYOFYEAR('2011-01-07' + INTERVAL 10 DAY)
  GROUP BY foos.id;
mysql date

2 回答

  • 1

    如果要查找所有日期日期 28 Dec 2011 和 17 Jan 2012 ，可以使用以下命令：

    WHERE DateColumn BETWEEN '2012-01-07' - INTERVAL 10 DAY
                     AND '2012-01-07' + INTERVAL 10 DAY

    对于更普遍的问题，我看到了两种方法 . 一个很容易写，但可能很慢，有一个很大的 foos 表：

    SELECT f.*
FROM 
      foos AS f
  CROSS JOIN
      ( SELECT 32 * MONTH(StartDate) + DAY(StartDate)
                 AS StartCheck
             , 32 * MONTH(EndDate) + DAY(EndDate)
                 AS EndCheck 
        FROM
            ( SELECT ('2012-01-07' - INTERVAL 10 DAY) AS StartDate 
                   , ('2012-01-07' + INTERVAL 10 DAY) AS EndDate 
            ) AS c
      ) AS cc
WHERE 32*MONTH(f.DateColumn)+DAY(f.DateColumn) BETWEEN StartCheck 
                                                   AND EndCheck 
   OR  StartCheck > EndCheck 
   AND 32*MONTH(f.DateColumn)+DAY(f.DateColumn) NOT BETWEEN EndCheck + 1
                                                        AND StartCheck - 1
    回复于
  • 2
    SELECT b_date FROM foos 
  WHERE DAYOFYEAR(b_date) BETWEEN 
    DAYOFYEAR('2011-01-07' - INTERVAL 10 DAY) AND (DAYOFYEAR('2011-01-07' - INTERVAL 10 DAY) + 21)
  OR DAYOFYEAR(b_date) BETWEEN 
    (DAYOFYEAR('2011-01-07' + INTERVAL 10 DAY) - 21) AND DAYOFYEAR('2011-01-07' + INTERVAL 10 DAY)
  GROUP BY foos.id;
    回复于

相关问题