SQL日期间隔查询 [英] SQL date intervals query

问题描述

我正在管理一个事件数据库。每个事件都有一个开始和结束时间戳（ INT，unix timestamp ）。

目前我可以做使用单个SQL查询的以下内容：

• 建立日历，并标记发生事件的日期


• 在给定日期列出发生的事件（YYYY / MM / DD，YYYY / MM）

问题是事件跨越几天，我无法在开始和结束时间戳之间的日期列出它。

例如：

事件从2011/05/25开始，到2011/05/27结束我不能在页面上列出2011/05/26。

我的实际的SQL查询是

  SELECT * FROM`event` 
 WHERE（`start` BETWEEN？AND？）OR `end` BETWEEN？AND？）
 ORDER BY start ASC 
  

两个绑定的参数unix时间戳）根据给定的参数类型（整个月或特定日期）自动计算。

在两个端点扩展我的查询之前的一天中可以得到这些事件（跨越几天）？

请让我知道，如果我可以澄清我的问题

更新

示例：

 事件开始：1309125660（2011-06-27 00:01:00）
 end end：1314050340（2011-08-22 23:59:59 ）
 
选择开始：1312408860（2011-08-04 00:01:00）
选择结束：1312495199（2011-08-04 23:59:59）
  

当我尝试列出发生的事件时，这个事件不会出现2011/08/4

解决方案

如果我得到正确的问题，使用 [：start，... end] 是您的日期范围兴趣，你正在寻找：

  select * 
 from event 
其中 - 事件开始更早，结束以后
 start< =：start和：start< = end 
或 - 事件在[：start，... end]期间启动
：start< = start并且开始< =：end 
或 - 事件在[：start，：end]期间结束
：start< = end和end< =：end; 
  

如果您正在寻找一个特定的：day ，使用：day as ：start 和：day + 1 day as ：end 。

I am managing an event database. Every event has a start and end timestamp (INT, unix timestamp).

Currently i'm able to do the following things with a single SQL query:

• build up a calendar, and mark days when an event occurring
• list occurring events on a given date (YYYY/MM/DD, YYYY/MM)

The problem is when an event spans several days i can't list it on a date between it's start and end timestamps.

For example:

Event starts on 2011/05/25 and ends on 2011/05/27 i can't list it on the page 2011/05/26.

My actual SQL query is

SELECT * FROM `event` 
WHERE (`start` BETWEEN ? AND ?) OR (`end` BETWEEN ? AND ?) 
ORDER BY start ASC

The two bound parameters (unix timestamps) are automatically calculated depending on what kind of parameter given (a whole month, or a specific day)

Is it possible to get these events (that spans several days) on a day between it's two endpoints extending my query above?

Please let me know if i can clarify my question.

Update

Example:

event start: 1309125660 (2011-06-27 00:01:00)
end end: 1314050340 (2011-08-22 23:59:59)

select start: 1312408860 (2011-08-04 00:01:00)
select end: 1312495199 (2011-08-04 23:59:59)

This event won't appear when i trying to list events occurring 2011/08/4

解决方案

If I get the question right, with [:start, :end] being your date range of interest, you're looking for:

select *
 from event
where -- event started earlier, ends later
      start <= :start and :start <= end
   or -- event starts during [:start, :end]
      :start <= start and start <= :end
   or -- event ends during [:start, :end]  
      :start <= end and end <= :end;

If you're looking for a particular :day, use :day as :start and :day + 1 day as :end.

这篇关于SQL日期间隔查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！