How to get the count of current month Sunday's in psql?

Question

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

Randazzo

Answer 1

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

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

or

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

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.

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

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;

Janiuszck

calendar table is a default table or we need to create? – Randazzo

I'm using PostgreSQL 8.3. I don't have the calendar table – Randazzo

Use generate_series, see my answer. – Kalasky

@ungalnanban: I posted code to create and populate a calendar table. – Rentroll

Why so complex? generate_series is all you need. – Kalasky

It's not part of pg as far as I am aware, you could generate one if you wanted using an online time dimension generator, but I would try using extract first – Ureide

@Frank Heikens: The calendar table is more general--simple queries on it can answer a wide range of questions. ("Simple" means maintenance programmers and interns can do it without asking me questions.) Queries and views on it can take advantage of indexes. Using typical queries from work, the calendar table is 2 to 15 times faster than generate_series(), and it can be easily used in joins. Calendar tables are also platform agnostic--they'll even work in Microsoft Access. – Rentroll

Answer 2

You need EXTRACT:

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

This can result in true or false, it's a sunday or it's not. I have no idea what you mean by "total number" because that will always be 0 (the date is not a sunday) or 1 (the given data is a sunday).

Edit: Something like this?

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

Kalasky

His question is "How to get the count of current month Sunday's in psql?" So you should make a count() on the range – Hame

@Catcall i quoted the question (see the headline) above the description, i assume the "given date" is the date of the month. But yeah, it's easy to miss :) – Hame

You could also use SUM() btw, (i think, ehrm) ;-) – Hame