如何为日期和时间维度设置SQL范围? [英] How do I do a SQL range for date and time dimensions?

问题描述

我有一个报告表，其中包含saledateid(天)和saletimeid(分钟)维度.我需要选择一个可能少于或超过一天的范围.如果范围超过一天(例如(1705, 901) -> (1708, 1140)代表2015-09-01 15:00:00 -> 2015-09-04 18:59:00)，我可以使用:

I have a reporting table that has a saledateid (days) and saletimeid (minutes) dimensions. I need to select a range that may be less or more than one day. If the range is more than one day (e.g. (1705, 901) -> (1708, 1140) to represent 2015-09-01 15:00:00 -> 2015-09-04 18:59:00) I can use:

WHERE (saledateid = 1705 AND saletimeid >= 901)
   OR (saledateid BETWEEN 1705 + 1 AND 1708 - 1)
   OR (saledateid = 1708 AND saletimeid <= 1140))

但是，当saledateid与(1708, 901) to (1708, 1140)相同(少于一天)(仅4小时)时，这将不起作用，因为整天将被退回.我将不得不使用:

However, this doesn't work when the saledateid's are the same (less than one day) like (1708, 901) to (1708, 1140) (only a 4 hour period) because the whole day will be returned. I would have to use:

WHERE saledateid = 1708 AND saletimeid BETWEEN 901 AND 1140

是否可以使用正确的表达式来涵盖这两种情况，还是在执行SQL之前已在应用程序代码中做出决定?

Is there a correct expression I can use to cover both scenarios or do I have decide in application code before executing the SQL?

推荐答案

我认为这可以满足您的要求:

I think this will do what you want:

where (saledateid > 1705 or
       saledateid = 1705 and saletimeid >= 901
      ) and
      (saledateid < 1708 or
       saledateid = 1708 and saletimeid <= 1140
      )

这篇关于如何为日期和时间维度设置SQL范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！