如何在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屋！