日期范围查询忽略月份年份结束和年份开始

问题描述：

我需要构建日期范围查询来查找事件。一些事件每年都会重复一次（annual_natural = true），并且这会在年终/开始时导致问题。我需要知道如何存储这些日期（我年度使用什么值？）和/或改进查询。我目前正在将重复日期存储为2017年的日期类型（如果他们跨越新的一年，则为2017年和2018年）这是我尝试过的。它的作用不同于新的一年中发生的事件。只有相关的字段包含在这个问题中。我很高兴重新设计数据库，我只有测试数据量小日期范围查询忽略月份年份结束和年份开始

"SELECT event.event_name, event.start_date, event.end_date, event.all_year, event.yearly_natural 
     FROM event 
     INNER JOIN event_location ON event_location.event_location_id = event.event_location_id 
     WHERE 

     (event.yearly_natural = false 

     AND 
      (event.start_date BETWEEN '$testFrom' AND '$testTo') 
      OR (event.end_date BETWEEN '$testFrom' AND '$testTo') 
      OR (event.start_date <= '$testFrom' AND event.end_date >= '$testTo') 
      )  
     OR (event.yearly_natural = true 
     AND 
      (DATE_FORMAT(event.start_date, '%m%d') BETWEEN '$testFrom' AND '$testTo') 
      OR (DATE_FORMAT(event.end_date, '%m%d') BETWEEN '$testFrom' AND '$testTo') 
      OR (DATE_FORMAT(event.start_date, '%m%d') <= '$testFrom' AND event.end_date >= '$testTo') 
     ) 

     OR (event.all_year = true) 

      ";

奇怪。我认为每年一次活动开始和结束的可能性不大，并且发生在同一地点。我会创建两个表格，一个用于事件属性，例如名称，描述等，另一个用于事件日期和位置。因此，每次事件发生时，您都会在最后一个表中创建一个新行。这样你可以继续使用'date'类型。 –

@KIKOSoftware嗨。这些事件是野生动物事件，例如季节性开花或鸟类栖息因此相同的日期和地点。已经有两个表格（event和event_location）因此加入，但为了清晰我已经忽略了位置细节。 – silverbadger

所以我的问题是：如果这些事件之一每年从十一月运行到一月份，怎么办？如何存储日期或查询它们？ – silverbadger

答

我与KIKO软件的评论表示赞同。每事件发生一次可能会更有意义。然而，如果你想保持它的方式，你可以做到这一点。其概念是，您修改年份输入日期以匹配存储日期的年份，然后将它们作为完整日期而不是仅仅是月份和日期进行比较。不知道它是否能够按原样工作100％，或者是否需要调整，但是您应该能够从这里获得一些工作。

 (event.yearly_natural = false 

    AND 
     (event.start_date BETWEEN '$testFrom' AND '$testTo') 
     OR (event.end_date BETWEEN '$testFrom' AND '$testTo') 
     OR (event.start_date <= '$testFrom' AND event.end_date >= '$testTo') 
     )  
    OR (event.yearly_natural = true 
    AND 
     (event.start_date BETWEEN CONCAT(DATE_FORMAT(event.start_date, '%Y'), '-', DATE_FORMAT('$testFrom', '%m-%d')) AND CONCAT(DATE_FORMAT(event.start_date, '%Y'), '-', DATE_FORMAT('$testTo', '%m-%d'))) 
    OR (event.end_date BETWEEN CONCAT(DATE_FORMAT(event.end_date, '%Y'), '-', DATE_FORMAT('$testFrom', '%m-%d')) AND CONCAT(DATE_FORMAT(event.end_date, '%Y'), '-', DATE_FORMAT('$testTo', '%m-%d'))) 
    OR (event.start_date <= CONCAT(DATE_FORMAT(event.start_date, '%Y'), '-', DATE_FORMAT('$testFrom', '%m-%d')) AND event.end_date >= CONCAT(DATE_FORMAT(event.end_date, '%Y'), '-', DATE_FORMAT('$testTo', '%m-%d')) 
) 

    OR (event.all_year = true)

谢谢。给我一段时间来尝试一下，我会让你知道我怎么走。 – silverbadger

先生们，谢谢你们两位。经过一段时间的考虑后，我认为你们建议我将每个事件存储为单独的条目都是正确的，我会继续这样做。不过，我非常感谢你们提供的'日戳'和串联的知识，这些都增加了我的知识。这是我第一个成功发布的SO问题，那么我该如何给你所有的赞誉呢？ – silverbadger

@silverbadger你可以通过点击旁边的向上箭头来获得答案（如果你觉得它们都很有价值）。如果您最终选择了一个作为您的解决方案，那么您应该将其标记为已接受的答案（单击复选标记）。如果你最终没有使用任何一个答案，你可以标记你认为最好或最接近的人，或者你甚至可以提供你自己的答案，详细描述你做了什么。 –

答

你可以简单地选择一个arbitary日期，说1月1日，并指定其值为0。一月的第5将4和明年一月的3TH将是367，依此类推。与Linux时间戳的功能类似，我们称之为“日戳”。请注意，这些日戳是相对的，不像时间戳那样绝对。您需要做的唯一事情就是从真实日期到这些日期的转换例程。我会这样做在PHP：

function daystamp2timestamp($daystamp) 
// returns a timestamp of the daystamp 
{ 
    $startThisYear = strtotime(date('Y').'-01-01 12:00'); 
    return strtotime("+$daystamp day",$startThisYear); 
} 

function timestamp2daystamp($timestamp) 
// returns a daystamp of the timestamp 
{ 
    $startThisYear = strtotime(date('Y').'-01-01 12:00'); 
    return floor(($timestamp-$startThisYear)/(24*60*60)); 
}

此代码未经测试，所以在使用它之前进行测试。它只是为了给你一个想法。正如你可能知道timestamp可以很容易地转换为date()函数的日期。

您只能在数据库中存储日戳，并且可以很容易地查看事件是否在日期范围内。首先将日期转换为日期并将其与数据库中的值进行比较。

日期范围查询忽略月份年份结束和年份开始

相关推荐