如何在psql中获取当前月份星期日的计数? [英] How to get the count of current month Sunday's in psql?

问题描述

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

How to get total number of Sunday's for given date in postgresql

推荐答案

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

The total number of Sundays for a given date can only be either 0 or 1.

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

But if you want the number of Sundays within a given date range, then your best bet is a calendar table. To find how many Sundays are in February this year, I'd just

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 不支持的任何功能.

Here's a basic calendar table that you can start with. I also included a PostgreSQL function to populate the calendar table. I haven't tested this in 8.3, but I'm pretty sure I'm not using any features that 8.3 doesn't support.

请注意，dow"部分假设您的日子是英语.但是您可以轻松编辑这些部分以匹配任何语言.(我想.但我可能对轻松"有误.)

Note that the "dow" parts assume your days are in English. But you can easily edit those parts to match any language. (I think. But I could be wrong about "easily".)

-- 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 中测试过.

And a rudimentary function to populate the table. I haven't tested this in 8.3 either.

-- 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;

这篇关于如何在psql中获取当前月份星期日的计数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！