postgresql – 如何在psql中获取当月周日的计数？

但如果您想要在给定日期范围内的星期日数量,那么您最好的选择是日历表.为了找到今年2月的星期日,我只是

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,CONSTRAINT calendar_check2 CHECK (day_of_month::double precision = date_part('day'::text,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;