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

概述如何在 postgresql中获取给定日期的星期日总数 给定日期的星期日总数只能是0或1. 但如果您想要在给定日期范围内的星期日数量,那么您最好的选择是日历表.为了找到今年2月的星期日,我只是 select count(*) from calendarwhere cal_date between '2011-02-01' and '2011-02-28' and day_of_we 如何在 postgresql中获取给定日期的星期日总数解决方法 给定日期的星期日总数只能是0或1.

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

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

要么

select count(*)from calendarwhere 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::textEND))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;

总结

以上是内存溢出为你收集整理的postgresql – 如何在psql中获取当月周日的计数？全部内容，希望文章能够帮你解决postgresql – 如何在psql中获取当月周日的计数？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。