如何计算Postgres中两个日期之间除星期天以外的天数？ [英] How to count days except Sundays between two dates in Postgres?

问题描述

要查找两个日期之间的天数，我们可以使用类似这样的东西：

To find the number of days between two dates we can use something like this:

SELECT date_part('day',age('2017-01-31','2017-01-01')) as total_days;

在上面的查询中，我们得到30而不是31的输出。为什么？

而且我还想找到除周日以外的天数。间隔（'2017-01-01'，'2017-01-31'）的预期输出：

In the above query we got 30 as output instead of 31. Why is that?
And I also want to find the number of days except Sundays. Expected output for the interval ('2017-01-01', '2017-01-31'):

Total Days = 31
Total Days except Sundays = 26

推荐答案

您需要更紧密地定义两个日期之间 。下限和上限是否包括在内？常见的定义是 包括 下限， 排除 上限。另外，当上下限相同时，将结果定义为0。这个定义恰好与日期减去恰好相符。

You need to define "between two dates" more closely. Lower and upper bound included or excluded? A common definition would be to include the lower and exclude the upper bound of an interval. Plus, define the result as 0 when lower and upper bound are identical. This definition happens to coincide with date subtraction exactly.

SELECT date '2017-01-31' - date '2017-01-01' AS days_between

此确切定义对于排除星期日非常重要。对于给定的定义，从太阳到太阳的间隔（1周后）不包括上限，因此只有 1 周日要减去。

This exact definition is important for excluding Sundays. For the given definition an interval from Sun - Sun (1 week later) does not include the upper bound, so there is only 1 Sunday to subtract.

interval in days  | sundays
0                 | 0
1-6               | 0 or 1
7                 | 1
8-13              | 1 or 2
14                | 2
...

7天的间隔始终包括一个星期日。

An interval of 7 days always includes exactly one Sunday.

我们可以用一个简单的整数除法（ days / 7 ）来获得最小结果，该结果会被截断。

We can get the minimum result with a plain integer division (days / 7), which truncates the result.

剩余的1-6天的额外星期日取决于间隔的第一天。如果是星期天，宾果游戏；如果是星期一，那太糟了。等等，我们可以从中得出一个简单的公式：

The extra Sunday for the remainder of 1 - 6 days depends on the first day of the interval. If it's a Sunday, bingo; if it's a Monday, too bad. Etc. We can derive a simple formula from this:

SELECT days, sundays, days - sundays AS days_without_sundays
FROM  (
   SELECT z - a AS days
      , ((z - a) + EXTRACT(isodow FROM a)::int - 1 ) / 7 AS sundays
   FROM  (SELECT date '2017-01-02' AS a       -- your interval here
               , date '2017-01-30' AS z) tbl
   ) sub;

在给定间隔内可用于任何。

注意： isodow ，而不是 dow 的 EXTRACT（） 。

Works for any given interval.
Note: isodow, not dow for EXTRACT().

要 包括 上限，只需将 z-a 替换为（z-a）+ 1 。 （由于运算符的优先级，本来可以没有括号，但是最好弄清楚。）

To include the upper bound, just replace z - a with (z - a) + 1. (Would work without parentheses, due to operator precedence, but better be clear.)

性能特征为 O（1） （常量），而不是使用 O（N）生成的集合上的条件聚合。

Performance characteristic is O(1) (constant) as opposed to a conditional aggregate over a generated set with O(N).

相关：

• 如何使用PostgreSQL确定上个月的最后一天？


• 在PostgreSQL中计算两个日期之间的工作时间

• How do I determine the last day of the previous month using PostgreSQL?
• Calculate working hours between 2 dates in PostgreSQL

这篇关于如何计算Postgres中两个日期之间除星期天以外的天数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！