如何在psql中获取当月星期日的计数？

Question

如何在postgresql中获取给定日期的星期日总数

Answer 1

您需要 EXTRACT：

SELECT 
    EXTRACT(DOW FROM DATE '2011-02-16') = 0; -- 0 is Sunday

这可能会导致 true 或 false、今天是星期日还是不是星期日。我不知道“总数”是什么意思，因为它总是 0（日期不是星期日）或 1（给定的数据是星期日）。

编辑：类似这样的事情吗？

SELECT 
    COUNT(*)
FROM
    generate_series(timestamp '2011-01-01', '2011-03-01', '1 day') AS g(mydate)
WHERE
    EXTRACT(DOW FROM mydate) = 0;

Answer 2

给定日期的星期日总数只能是 0 或 1。

但是，如果您想要给定日期范围内的星期日数，那么最好的选择是日历表。要查找今年二月有多少个星期日，我只需

select count(*) 
from calendar
where cal_date between '2011-02-01' and '2011-02-28' and
      day_of_week = 'Sun';

或

select count(*)
from calendar
where year_of_date = 2011 and
      month_of_year = 2 and 
      day_of_week = 'Sun';

您可以从以下基本日历表开始。我还包含了一个 PostgreSQL 函数来填充日历表。我还没有在 8.3 中测试过这一点，但我很确定我没有使用 8.3 不支持的任何功能。

请注意，“dow”部分假设您的日子是英语的。但您可以轻松编辑这些部分以匹配任何语言。 （我想。但我对“容易”的看法可能是错误的。）

-- Table: calendar

-- DROP TABLE calendar;

CREATE TABLE calendar
(
  cal_date date NOT NULL,
  year_of_date integer NOT NULL,
  month_of_year integer NOT NULL,
  day_of_month integer NOT NULL,
  day_of_week character(3) NOT NULL,
  CONSTRAINT calendar_pkey PRIMARY KEY (cal_date),
  CONSTRAINT calendar_check CHECK (year_of_date::double precision = date_part('year'::text, cal_date)),
  CONSTRAINT calendar_check1 CHECK (month_of_year::double precision = date_part('month'::text, cal_date)),
  CONSTRAINT calendar_check2 CHECK (day_of_month::double precision = date_part('day'::text, cal_date)),
  CONSTRAINT calendar_check3 CHECK (day_of_week::text = 
CASE
    WHEN date_part('dow'::text, cal_date) = 0::double precision THEN 'Sun'::text
    WHEN date_part('dow'::text, cal_date) = 1::double precision THEN 'Mon'::text
    WHEN date_part('dow'::text, cal_date) = 2::double precision THEN 'Tue'::text
    WHEN date_part('dow'::text, cal_date) = 3::double precision THEN 'Wed'::text
    WHEN date_part('dow'::text, cal_date) = 4::double precision THEN 'Thu'::text
    WHEN date_part('dow'::text, cal_date) = 5::double precision THEN 'Fri'::text
    WHEN date_part('dow'::text, cal_date) = 6::double precision THEN 'Sat'::text
    ELSE NULL::text
END)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE calendar OWNER TO postgres;

-- Index: calendar_day_of_month

-- DROP INDEX calendar_day_of_month;

CREATE INDEX calendar_day_of_month
  ON calendar
  USING btree
  (day_of_month);

-- Index: calendar_day_of_week

-- DROP INDEX calendar_day_of_week;

CREATE INDEX calendar_day_of_week
  ON calendar
  USING btree
  (day_of_week);

-- Index: calendar_month_of_year

-- DROP INDEX calendar_month_of_year;

CREATE INDEX calendar_month_of_year
  ON calendar
  USING btree
  (month_of_year);

-- Index: calendar_year_of_date

-- DROP INDEX calendar_year_of_date;

CREATE INDEX calendar_year_of_date
  ON calendar
  USING btree
  (year_of_date);

以及填充表格的基本功能。我也没有在 8.3 中测试过这个。

-- Function: insert_range_into_calendar(date, date)

-- DROP FUNCTION insert_range_into_calendar(date, date);

CREATE OR REPLACE FUNCTION insert_range_into_calendar(from_date date, to_date date)
  RETURNS void AS
$BODY$

DECLARE
    this_date date := from_date;
BEGIN

    while (this_date <= to_date) LOOP
        INSERT INTO calendar (cal_date, year_of_date, month_of_year, day_of_month, day_of_week)
        VALUES (this_date, extract(year from this_date), extract(month from this_date), extract(day from this_date),
        case when extract(dow from this_date) = 0 then 'Sun'
             when extract(dow from this_date) = 1 then 'Mon'
             when extract(dow from this_date) = 2 then 'Tue'
             when extract(dow from this_date) = 3 then 'Wed'
             when extract(dow from this_date) = 4 then 'Thu'
             when extract(dow from this_date) = 5 then 'Fri'
             when extract(dow from this_date) = 6 then 'Sat'
        end);
        this_date = this_date + interval '1 day';
    end loop;       

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;