clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36295 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)