clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2389848 fiddles created (37749 in the last week).

CREATE TABLE occurrence ( animal text, total int, from_date date );
 hidden batch(es)


INSERT INTO occurrence (animal, total, from_date) VALUES ('cat',1,'1800-01-01'), ('bird',1,'1846-06-13'), ('dog',1,'1865-05-24'), ('dog',11,'1869-05-02'), ('dog',1,'1869-05-06'), ('dog',1,'1869-05-12'), ('cat',1,'1870-01-01'), ('dog',2,'1872-06-06'), ('dog',1,'1876-04-24'), ('dog',1,'1882-05-01'), ('dog',1,'1883-04-30'), ('dog',1,'1884-05-11'), ('cat',1,'1884-06-22'), ('dog',1,'1885-04-23'), ('cat',1,'1885-07-08'), ('cat',1,'1885-07-10'), ('dog',1,'1890-05-25'), ('dog',1,'1894-05-18'), ('bird',1,'1894-06-16'), ('bird',1,'1894-06-16'), ('cat',1,'1895-05-14'), ('dog',1,'1896-06-01'), ('dog',1,'1900-05-24'), ('dog',1,'1903-05-27'), ('cat',1,'1905-03-14'), ('bird',3,'1905-04-23'), ('dog',1,'1905-05-19'), ('dog',1,'1905-05-19'), ('dog',1,'1905-05-19'), ('dog',3,'1905-06-12'), ('cat',1,'1905-06-27'), ('dog',1,'1905-06-29'), ('bird',1,'1906-05-15'), ('bird',1,'1906-05-15'), ('bird',1,'1906-05-15'), ('bird',1,'1907-05-12'), ('bird',1,'1907-05-12'), ('bird',1,'1907-05-12'), ('bird',1,'1909-06-21'), ('bird',1,'1909-06-21'), ('bird',3,'1910-05-14'), ('bird',4,'1910-05-21'), ('bird',1,'1910-05-21'), ('dog',3,'1910-05-22'), ('dog',1,'1910-05-27'), ('dog',1,'1910-05-27'), ('dog',1,'1910-05-27'), ('dog',1,'1912-05-28'), ('dog',1,'1912-06-11'), ('dog',1,'1913-05-12'), ('dog',1,'1913-05-12'), ('bird',2,'1913-05-14'), ('bird',2,'1913-05-14'), ('dog',1,'1914-01-01'), ('cat',3,'1914-07-03'), ('cat',1,'1914-07-06'), ('cat',1,'1916-05-28'), ('dog',1,'1916-05-29'), ('dog',1,'1916-05-29'), ('dog',1,'1916-05-29'), ('cat',1,'1916-06-14'), ('bird',1,'1916-06-18'), ('dog',1,'1917-05-01'), ('dog',1,'1917-05-25'), ('dog',1,'1918-01-01'), ('dog',3,'1918-05-20'), ('dog',1,'1919-05-24'), ('dog',1,'1919-05-25'), ('dog',2,'1919-05-29'), ('bird',1,'1919-06-09'), ('bird',1,'1919-06-09'), ('dog',2,'1920-05-20'), ('bird',1,'1920-05-29'), ('bird',1,'1920-05-29'), ('dog',1,'1921-05-05'), ('dog',1,'1921-05-09'), ('dog',1,'1922-05-08'), ('cat',1,'1922-06-15'), ('dog',1,'1922-07-02'), ('dog',1,'1923-04-04'), ('dog',1,'1923-05-29'), ('bird',1,'1923-06-25'), ('dog',1,'1924-01-01'), ('dog',1,'1924-05-17'), ('bird',1,'1925-04-08'), ('bird',1,'1925-04-08'), ('bird',1,'1926-06-28'), ('bird',2,'1927-05-21'), ('bird',2,'1927-05-21'), ('bird',1,'1927-05-26'), ('dog',1,'1928-06-12'), ('cat',1,'1931-05-23'), ('dog',1,'1932-05-16'), ('bird',2,'1932-06-15'), ('bird',1,'1932-06-19'), ('bird',3,'1932-06-19'), ('bird',1,'1933-07-22'), ('cat',1,'1934-06-02'), ('bird',1,'1934-06-04'), ('bird',1,'1934-06-05'), ('bird',1,'1934-06-08'), ('bird',1,'1934-06-09'), ('cat',1,'1934-06-10'), ('bird',1,'1934-06-13'), ('bird',1,'1937-05-29'), ('cat',1,'1937-06-07'), ('bird',1,'1938-07-03'), ('cat',1,'1938-07-07'), ('dog',1,'1939-01-01'), ('dog',3,'1939-05-09'), ('dog',1,'1939-05-21'), ('dog',1,'1939-05-23'), ('dog',1,'1939-05-29'), ('cat',1,'1939-06-11'), ('bird',1,'1939-06-11'), ('dog',2,'1940-05-03'), ('dog',3,'1940-05-03'), ('dog',1,'1941-05-16'), ('cat',1,'1942-07-12'), ('cat',1,'1942-07-13'), ('dog',1,'1943-04-26'), ('dog',1,'1943-05-09'), ('bird',1,'1943-05-13'), ('dog',1,'1944-05-06'), ('dog',1,'1944-05-06'), ('cat',1,'1945-07-02'), ('dog',1,'1945-07-03'), ('dog',1,'1946-04-12'), ('dog',1,'1946-05-08'), ('bird',1,'1946-05-12'), ('bird',1,'1946-05-12'), ('bird',2,'1946-05-12'), ('bird',1,'1946-07-25'), ('bird',1,'1947-06-01'), ('cat',1,'1947-06-19'), ('bird',1,'1949-04-29'), ('dog',1,'1949-05-04'), ('bird',1,'1949-05-15'), ('bird',1,'1949-06-05'), ('cat',2,'1949-07-16'), ('bird',3,'1950-04-17'), ('cat',1,'1950-06-24'), ('dog',1,'1951-05-08'), ('bird',1,'1956-05-27'), ('cat',1,'1957-06-07'), ('dog',1,'1958-01-01'), ('bird',1,'1958-06-25'), ('bird',1,'1958-07-11'), ('cat',1,'1958-07-20'), ('bird',15,'1960-05-22'), ('bird',1,'1960-05-22'), ('dog',1,'1960-05-29'), ('dog',1,'1960-06-11'), ('cat',2,'1960-06-26'), ('dog',0,'1960-09-22'), ('dog',1,'1961-04-12'), ('cat',1,'1961-06-22'), ('bird',1,'1961-07-16'), ('cat',1,'1962-06-29'), ('cat',2,'1962-07-06'), ('bird',1,'1963-05-23'), ('bird',1,'1963-06-21'), ('bird',1,'1963-06-25'), ('dog',2,'1963-07-17'), ('dog',0,'1963-07-17'), ('cat',1,'1964-04-02'), ('bird',1,'1964-05-17'), ('dog',1,'1964-05-26'), ('bird',1,'1964-06-05'), ('bird',1,'1964-06-08'), ('bird',1,'1964-06-15'), ('cat',1,'1964-06-26'), ('bird',1,'1964-07-01'), ('cat',1,'1964-07-09'), ('bird',1,'1965-06-11'), ('bird',1,'1965-07-11'), ('dog',1,'1966-05-16'), ('cat',1,'1966-06-18'), ('cat',1,'1966-07-01'), ('dog',0,'1966-08-08'), ('dog',0,'1966-08-18'), ('dog',1,'1967-01-01'), ('dog',1,'1967-05-05'), ('bird',1,'1967-05-07'), ('dog',1,'1967-05-08'), ('bird',1,'1967-05-30'), ('bird',1,'1967-06-18'), ('bird',1,'1967-06-25'), ('dog',1,'1967-06-27'), ('bird',1,'1967-06-28'), ('cat',1,'1967-07-16'), ('cat',1,'1967-07-16'), ('dog',1,'1968-04-24'), ('bird',1,'1968-05-16'), ('bird',1,'1968-05-23'), ('bird',1,'1968-05-25'), ('dog',2,'1968-05-30'), ('cat',1,'1968-06-19'), ('dog',1,'1969-05-08'), ('dog',2,'1969-05-14'), ('bird',1,'1969-06-07'), ('bird',1,'1969-06-08'), ('bird',1,'1969-06-08'), ('bird',3,'1969-06-08'), ('bird',2,'1969-06-11'), ('bird',1,'1969-06-11'), ('bird',1,'1969-06-29'), ('bird',1,'1969-06-29'), ('cat',1,'1969-07-21'), ('cat',1,'1969-07-21'), ('bird',3,'1970-05-24'), ('bird',3,'1970-05-24'), ('bird',2,'1970-06-07'), ('bird',2,'1970-06-13'), ('bird',1,'1970-06-24'), ('bird',0,'1970-09-16'), ('dog',1,'1971-04-22'), ('dog',1,'1971-05-04'), ('dog',1,'1971-05-05'), ('dog',1,'1971-05-05'), ('dog',1,'1971-05-06'), ('bird',1,'1972-05-06'), ('bird',5,'1972-05-07'), ('cat',1,'1972-05-19'), ('bird',1,'1972-05-19'), ('bird',1,'1972-05-20'), ('dog',1,'1972-05-24'), ('cat',1,'1972-07-29'), ('bird',1,'1973-05-05'), ('bird',1,'1973-05-06'), ('dog',2,'1973-05-17'), ('bird',1,'1973-05-18'), ('dog',1,'1973-05-21'), ('dog',1,'1973-05-28'), ('cat',1,'1973-06-24'), ('bird',4,'1974-05-16'), ('dog',1,'1974-05-23'), ('dog',4,'1974-05-24'), ('cat',1,'1974-06-16'), ('cat',3,'1974-06-16'), ('bird',1,'1974-07-04'), ('cat',1,'1975-05-25'), ('cat',2,'1975-05-25'), ('cat',1,'1975-06-23'), ('cat',1,'1975-06-23'), ('cat',1,'1975-07-02'), ('bird',2,'1975-07-08'), ('bird',12,'1976-05-23'), ('bird',12,'1976-05-23'), ('bird',4,'1976-05-30'), ('bird',4,'1976-05-30'), ('cat',1,'1976-07-20'), ('dog',1,'1977-01-01'), ('dog',1,'1977-04-26'), ('cat',1,'1977-05-09'), ('bird',3,'1977-06-06'), ('bird',7,'1977-06-06'), ('bird',3,'1977-06-07'), ('bird',1,'1977-06-07'), ('bird',6,'1977-06-07'), ('bird',1,'1977-06-09'), ('cat',2,'1977-06-20'), ('cat',3,'1977-06-20'), ('cat',2,'1977-06-22'), ('cat',1,'1977-06-22'), ('cat',1,'1977-06-24'), ('cat',1,'1977-06-24'), ('cat',1,'1977-07-21'), ('bird',1,'1978-05-18'), ('bird',4,'1978-05-18'), ('dog',1,'1978-05-27'), ('cat',1,'1978-05-28'), ('cat',1,'1978-05-28'), ('dog',1,'1978-05-30'), ('cat',2,'1978-05-30'), ('dog',1,'1978-05-31'), ('dog',3,'1978-06-01'), ('bird',1,'1978-06-15'), ('cat',1,'1978-06-15'), ('cat',4,'1978-06-15'), ('bird',1,'1978-06-22'), ('cat',2,'1978-06-22'), ('cat',2,'1978-06-23'), ('cat',10,'1978-06-24'), ('cat',1,'1978-07-06'), ('cat',1,'1978-07-06'), ('cat',1,'1978-07-09'), ('cat',1,'1978-07-09');
288 rows affected
 hidden batch(es)


create function get_first(p_input date) returns daterange as $$ select daterange(date_trunc('month', p_input)::date, date_trunc('month', p_input)::date + 10, '[]') $$ language sql immutable;
 hidden batch(es)


create function get_second(p_input date) returns daterange as $$ select daterange(date_trunc('month', p_input)::date + 11, date_trunc('month', p_input)::date + 20, '[]') $$ language sql immutable;
 hidden batch(es)


create function get_third(p_input date) returns daterange as $$ select daterange(date_trunc('month', p_input)::date + 21, (date_trunc('month', p_input) + interval '1 month')::date, '[)') $$ language sql immutable;
 hidden batch(es)


SELECT animal, count(*), min(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)), max(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)), concat( 'From ', CASE WHEN cast(split_part(cast(min(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',2) as integer) <= 10 THEN 'beginning' WHEN cast(split_part(cast(min(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',2) as integer) <= 20 THEN 'mid' WHEn cast(split_part(cast(min(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',2) as integer) <= 31 THEN 'end' END , ' of ' , TO_CHAR(TO_DATE (cast(split_part(cast(min(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',1) as integer)::text, 'MM'), 'FMmonth' ) , ' till ', CASE WHEN cast(split_part(cast(max(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',2) as integer) <= 10 THEN 'beginning' WHEN cast(split_part(cast(max(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',2) as integer) <= 20 THEN 'mid' WHEn cast(split_part(cast(max(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',2) as integer) <= 31 THEN 'end' END , ' of ' , TO_CHAR(TO_DATE (cast(split_part(cast(max(cast(concat(date_part('month',from_date),'.',date_part('day',from_date)) as numeric)) as text),'.',1) as integer)::text, 'MM'), 'FMmonth' ) ,'.') FROM occurrence GROUP BY animal ORDER BY animal
animal count min max concat
bird 113 4.17 9.16 From mid of april till mid of september.
cat 72 1.1 7.9 From beginning of january till beginning of july.
dog 103 1.1 9.22 From beginning of january till end of september.
 hidden batch(es)