从PostgreSQL中的间隔获取某星期几（周末）的数量 [英] Getting number of certain days-of-the-week (weekend) from interval in PostgreSQL

问题描述

给出postgres中的2个时间戳，如何计算时差而不计算整个星期六和星期日？

Given 2 timestamps in postgres, how do you calculate the time difference without counting whole Saturdays and Sundays?

OR

如何计算给定时间间隔内的星期六和星期日的数目？

How do you count the number of Saturdays and Sundays in a given time interval?

推荐答案

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

The following function is returning the number of full weekend days between two dates. As you need full days, you can cast the timestamps to dates before calling the function. It returns 0 in case the first date is not strictly before the second.

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

要获得除整个周末以外的天数，只需减去上面函数的天数：

To obtain the number of days except full weekend days, simply subtract the number of days from the function above:

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

这篇关于从PostgreSQL中的间隔获取某星期几（周末）的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！