postgresql - 从 PostgreSQL 中的时间间隔获取特定星期几(周末)的数量

Question

给定 postgres 中的 2 个时间戳，你如何在不计算整个星期六和星期日的情况下计算时差？

或

如何计算给定时间间隔内的周六和周日的数量？

Answer 1

以下函数返回两个日期之间的完整周末天数。由于您需要一整天，因此可以在调用函数之前将时间戳转换为日期。如果第一个日期不严格早于第二个日期，则返回 0。

CREATE FUNCTION count_full_weekend_days(date, date)
  RETURNS int AS
$BODY$
  SELECT
    ($1 < $2)::int
      *
    (
      (($2 - $1) / 7) * 2
        + 
      (EXTRACT(dow FROM $1)<6 AND EXTRACT(dow FROM $2)>0 AND EXTRACT(dow FROM $1)>EXTRACT(dow FROM $2))::int * 2
        +
      (EXTRACT(dow FROM $1)=6 AND EXTRACT(dow FROM $2)>0)::int
        +
      (EXTRACT(dow FROM $2)=0 AND EXTRACT(dow FROM $1)<6)::int
    );
$BODY$
  LANGUAGE 'SQL' IMMUTABLE STRICT;

例子:

SELECT COUNT_FULL_WEEKEND_DAYS('2009-04-10', '2009-04-20');
# returns 4

SELECT COUNT_FULL_WEEKEND_DAYS('2009-04-11', '2009-04-20');
# returns 3 (11th is Saturday, so it shouldn't be counted as full day)

SELECT COUNT_FULL_WEEKEND_DAYS('2009-04-12', '2009-04-20');
# returns 2 (12th is Sunday, so it shouldn't be counted as full day)

SELECT COUNT_FULL_WEEKEND_DAYS('2009-04-13', '2009-04-20');
# returns 2

要获得除周末以外的天数，只需从上面的函数中减去天数:

SELECT
  '2009-04-20'::date
    -
  '2009-04-13'::date
    -
   COUNT_FULL_WEEKEND_DAYS('2009-04-13', '2009-04-20');