查找两个日期之间的日期(最佳做法) [英] Finding dates between two dates (Best practice)

问题描述

我有2个SQL脚本.像这样的一个:

I have 2 SQL scripts. One like this:

"Date" > '2014-04-11' AND "Date" <= '2014-04-12'

另一个是这样的:

"Date" BETWEEN '2014-04-11' AND '2014-04-12'

现在，我想知道是否有任何特定的最佳实践，一个理由比另一个更好，并且其中一个是否由于某种明显的原因而更好，所以我错过了未来的某个地方.

Now i wonder if there is any specific best practice, one reason to do one over the other and if one of them is better for some apparent reason I missed somewhere down the road.

推荐答案

您正在寻求最佳实践.我认为以下是最佳做法:

You are asking for best practices. I think the following is the best practice:

"Date" >= DATE '2014-04-11' AND "Date" < DATE '2014-04-12' + 1

首先，请注意使用DATE关键字.您的问题是关于日期的，但是您使用的是Oracle不直接支持的日期格式.令人高兴的是，Oracle支持ISO标准格式的ANSI标准DATE关键字.

First, note the use of the DATE keyword. You question is about dates and yet you are using a date format that Oracle does not directly support. Happily, Oracle support the ANSI standard DATE keyword with the ISO standard format.

第二，我添加了一个+1，以便您可以看到时间段的结束，这大概是您希望在代码中看到的.它不应该影响性能，因为+ 1是恒定的.

Second, I added a +1 so you can see the end of the time period, which is presumably what you want to see in the code. It shouldn't affect performance because the + 1 is on a constant.

第三，日期常量具有时间成分.如果未指定，则为该日期的午夜.因此，表达式:

Third, a date constant has a time component. When none is specified, it is midnight on the date. So, the expression:

"Date" BETWEEN '2014-04-11' AND '2014-04-12'

是真的:

"Date" >= TIMESTAMP '2014-04-11 00:00:00' AND "Date" <= TIMESTAMP '2014-04-12 00:00:00'

也就是说，包括从较晚的日期开始的确切时间，即午夜的第一个瞬间.这通常不是您想要的. Oracle通过两种方式使这个问题变得更糟:

That is, exactly one time from the later date is included, the first instant at midnight. This is not usually what you want. Oracle makes this problem worse in two ways:

1. date数据类型包括时间分量.
2. 显示date值的默认方式没有时间成分.

1. The date data type includes a time component.
2. The default way of presenting date values has no time component.

为了最安全，请使用以下规则:

So, to be safest, use the following rules:

• 请勿在日期上使用between.
• 使用>=作为第一个日期.
• 第二个用户<.

• Do not use between on dates.
• Use >= for the first date.
• User < for the second.

Aaron Bertrand有一个

Aaron Bertrand has a blog on exactly this topic. Although it is specifically about SQL Server, many of the ideas apply to Oracle -- especially because the date data type in Oracle includes times.

这篇关于查找两个日期之间的日期(最佳做法)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！