clear markdown compare help best fiddles feedback dbanow.uk
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. 2555266 fiddles created (37452 in the last week).

CREATE TABLE acs_objects (creation_date timestamp); INSERT INTO acs_objects SELECT '1970-1-1 0:0'::timestamp + random() * interval '46 years' FROM generate_series(1,50) g -- test with more rows at home, not in the fiddle ; CREATE INDEX foo ON acs_objects (creation_date);
50 rows affected
 hidden batch(es)


-- recursive CTE WITH RECURSIVE cte AS ( SELECT date_trunc('year', max(creation_date)) AS y FROM acs_objects UNION ALL SELECT (SELECT date_trunc('year', max(creation_date)) FROM acs_objects WHERE creation_date < cte.y) FROM cte WHERE cte.y IS NOT NULL ) SELECT to_char(y, 'YYYY') AS year FROM cte WHERE cte.y IS NOT NULL;
year
2015
2014
2013
2012
2011
2010
2008
2007
2006
2005
2004
2002
2001
1999
1997
1996
1995
1994
1993
1991
1989
1987
1986
1985
1983
1981
1980
1979
1978
1975
1974
1973
1971
 hidden batch(es)


-- generate_series() & EXISTS SELECT to_char(y, 'YYYY') AS year FROM ( SELECT generate_series(date_trunc('year', min(creation_date)) , max(creation_date) , interval '1 year') FROM acs_objects ) t(y) WHERE EXISTS ( SELECT FROM acs_objects WHERE creation_date >= y AND creation_date < y + interval '1 year' );
year
1971
1973
1974
1975
1978
1979
1980
1981
1983
1985
1986
1987
1989
1991
1993
1994
1995
1996
1997
1999
2001
2002
2004
2005
2006
2007
2008
2010
2011
2012
2013
2014
2015
 hidden batch(es)