I have field records that have fields with years and months of this type:
id mes ano
----------
1 1 2005
----------
2 2 2005
----------
3 4 2005
----------
4 5 2006
----------
My question is: Is it possible to find in the period from 2005 to 2015 all the months that have not been registered?
Assuming your table has the following structure:
CREATE TABLE tb_ano_mes
(
id bigserial,
ano int,
mes int,
CONSTRAINT pk_ano_mes PRIMARY KEY ( id )
);
Containing the following data:
INSERT INTO tb_ano_mes ( id, mes, ano ) VALUES ( 1, 1, 2005 );
INSERT INTO tb_ano_mes ( id, mes, ano ) VALUES ( 2, 2, 2005 );
INSERT INTO tb_ano_mes ( id, mes, ano ) VALUES ( 3, 4, 2005 );
INSERT INTO tb_ano_mes ( id, mes, ano ) VALUES ( 4, 5, 2006 );
Your problem can be solved with the generate_series() function, supported since the version 9.1 of Postgres:
SELECT
EXTRACT( MONTH FROM s)::int AS mes,
EXTRACT( YEAR FROM s )::int AS ano
FROM
generate_series( '01/01/2005', '31/12/2015', '1 month'::interval ) AS s
LEFT JOIN
tb_ano_mes AS tbl ON ( EXTRACT(MONTH FROM s) = tbl.mes AND EXTRACT(YEAR FROM s) = tbl.ano )
WHERE
tbl.id IS NULL
ORDER BY
ano,
mes;
I hope I have helped!
A small modification to the solution only, the date pattern, which should be YYYY-MM-DD and for the most current versions of PostgreSQL, type conversion should be explicit in most cases:
SELECT
EXTRACT( MONTH FROM s)::int AS mes,
EXTRACT( YEAR FROM s )::int AS ano
FROM
generate_series( '2005-01-01'::date, '2015-12-31'::date, '1 month'::interval ) AS s
LEFT JOIN
tb_ano_mes AS tbl ON ( EXTRACT(MONTH FROM s) = tbl.mes AND EXTRACT(YEAR FROM s) = tbl.ano )
WHERE
tbl.id IS NULL
ORDER BY
ano,
mes;