Como você faz a matemática que ignora o ano?

Estou tentando selecionar datas que tenham aniversário nos próximos 14 dias. Como posso selecionar com base nas datas excluindo o ano? Eu tentei algo como o seguinte.

SELECT * FROM events WHERE EXTRACT(month FROM "date") = 3 AND EXTRACT(day FROM "date") < EXTRACT(day FROM "date") + 14 

O problema com isso é que meses embrulhar.
Eu preferiria fazer algo assim, mas não sei como ignorar o ano.

 SELECT * FROM events WHERE (date > '2013-03-01' AND date < '2013-04-01') 

Como posso realizar esse tipo de data no Postgres?

Se você não se importa com explicações e detalhes, use a “versão da magia negra” abaixo.

Todas as consultas apresentadas até agora operam com condições que não são sargáveis – elas não podem usar um índice e precisam calcular uma expressão para cada linha da tabela base para encontrar linhas correspondentes. Com pequenas tabelas, isso não importa muito. Com grandes tabelas, no entanto, isso é muito importante .

Dada a seguinte tabela simples:

 CREATE TABLE event ( event_id serial PRIMARY KEY , event_date date ); 

Inquerir

Versão 1. e 2. pode usar um índice simples do formulário:

 CREATE INDEX event_event_date_idx ON event(event_date); 

Mas as soluções a seguir são ainda mais rápidas sem indexação .

1. versão simples

 SELECT * FROM ( SELECT ((current_date + d) - interval '1 year' * y)::date AS event_date FROM generate_series(0, 14) d CROSS JOIN generate_series(13, 113) y ) x JOIN event USING (event_date); 

A subconsulta x calcula todas as datas possíveis em um determinado intervalo de anos a partir de um CROSS JOIN de duas chamadas generate_series() . A seleção é feita com uma simples equi-join.

2. Versão avançada

 WITH val AS ( SELECT extract(year FROM age(now()::date + 14, min(event_date)))::int AS max_y , extract(year FROM age(now()::date, max(event_date)))::int AS min_y FROM event ) SELECT e.* -- count(*) -- FROM ( SELECT ((current_date + d) - interval '1y' * yy)::date AS event_date FROM generate_series(0, 14) AS d) d ,(SELECT generate_series(min_y, max_y) AS y FROM val) y ) x JOIN event e USING (event_date); 

A faixa de anos é deduzida da tabela automaticamente, minimizando os anos gerados.
Você poderia até dar um passo adiante e destilar uma lista de anos existentes se você tiver lacunas em sua faixa de anos.

A eficácia co-depende da distribuição de datas. Poucos anos com muitas linhas cada, tornam minha solução mais útil. Muitos anos com poucas linhas cada, tornam-no menos útil.

Simples SQL Fiddle para brincar.

3. versão da magia negra

Atualizado em 2016 para remover a “coluna gerada” desnecessária, que bloquearia as atualizações do HOT e usaria uma function mais simples e rápida.
Atualizado 2018 para calcular MMDD com expressões IMMUTABLE para permitir inline de function.

Crie uma function SQL simples para calcular um integer do padrão 'MMDD' :

 CREATE FUNCTION f_mmdd(date) RETURNS int LANGUAGE sql IMMUTABLE AS 'SELECT (EXTRACT(month FROM $1) * 100 + EXTRACT(day FROM $1))::int'; 

Eu usei to_char(time, 'MMDD') no começo, mas mudei para a expressão acima que acabou sendo a mais rápida em novos testes no Postgres 9.6 e 10:

db <> toca aqui

Ele ainda permite inline de function porque ele usa somente EXTRACT (xyz FROM date) – que é implementado com a function IMMUTABLE date_part(text, date) internamente.

Precisa ser IMMUTABLE para ser usado no índice de expressão de várias IMMUTABLE :

 CREATE INDEX event_mmdd_event_date_idx ON event(f_mmdd(event_date), event_date); 

Várias colunas por diversos motivos: pode ajudar com ORDER BY ou com a seleção de determinados anos. Leia aqui Quase sem custo adicional para o índice. Uma date se ajusta aos 4 bytes que seriam perdidos para o preenchimento devido ao alinhamento de dados. Leia aqui
Além disso, como as duas colunas de índice fazem referência à mesma coluna da tabela, não há desvantagem em relação às atualizações HOT . Leia aqui

Uma function de tabela PL / pgSQL para controlá-los todos

Bifurcação para uma das duas consultas para cobrir a virada do ano.

 CREATE OR REPLACE FUNCTION f_anniversary(date = current_date, int = 14) RETURNS SETOF event AS $func$ DECLARE d int := f_mmdd($1); d1 int := f_mmdd($1 + $2 - 1); -- fix off-by-1 due to including upper bound BEGIN IF d1 > d THEN RETURN QUERY SELECT * FROM event e WHERE f_mmdd(e.event_date) BETWEEN d AND d1 ORDER BY f_mmdd(e.event_date), e.event_date; ELSE -- wrap around end of year RETURN QUERY SELECT * FROM event e WHERE f_mmdd(e.event_date) >= d OR f_mmdd(e.event_date) <= d1 ORDER BY (f_mmdd(e.event_date) >= d) DESC, f_mmdd(e.event_date), event_date; -- chronological across turn of the year END IF; END $func$ LANGUAGE plpgsql; 

Chame usando padrões: 14 dias começando “hoje”:

 SELECT * FROM f_anniversary(); 

Ligue para 7 dias a partir de ‘2014-08-23’:

 SELECT * FROM f_anniversary('2014-08-23'::date, 7); 

SQL Fiddle comparando EXPLAIN ANALYZE .

29 de fevereiro

Ao lidar com aniversários ou “aniversários”, você precisa definir como lidar com o caso especial em 29 de fevereiro em anos bissextos.

Ao testar as faixas de datas, o dia Feb 29 geralmente é incluído automaticamente, mesmo que o ano atual não seja um ano bissexto . O intervalo de dias é estendido por 1 retroativamente quando abrange esse dia.
Por outro lado, se o ano atual for um ano bissexto e você quiser procurar por 15 dias, poderá obter resultados por 14 dias em anos bissextos se seus dados forem de anos sem salto.

Diga, Bob nasceu no dia 29 de fevereiro:
Minha consulta 1. e 2. incluem 29 de fevereiro apenas em anos bissextos. Bob tem aniversário apenas a cada 4 anos.
Minha consulta 3. inclui 29 de fevereiro no intervalo. Bob tem aniversário todo ano.

Não há solução mágica. Você tem que definir o que você quer para cada caso.

Teste

Para substanciar meu ponto, fiz um teste extenso com todas as soluções apresentadas. Eu adaptei cada uma das consultas à tabela dada e obtive resultados idênticos sem ORDER BY .

A boa notícia: todos eles estão corretos e produzem o mesmo resultado – exceto pela consulta de Gordon que tinha erros de syntax e @ consulta do wildplasser que falha quando o ano se aproxima (fácil de corrigir).

Insira 108.000 linhas com datas aleatórias do século 20, que é semelhante a uma tabela de pessoas vivas (13 anos ou mais).

 INSERT INTO event (event_date) SELECT '2000-1-1'::date - (random() * 36525)::int FROM generate_series (1, 108000); 

Exclua ~ 8% para criar algumas tuplas mortas e torne a tabela mais “real life”.

 DELETE FROM event WHERE random() < 0.08; ANALYZE event; 

Meu caso de teste teve 99289 linhas, 4012 accesss.

C - Catcall

 WITH anniversaries as ( SELECT event_id, event_date ,(event_date + (n || ' years')::interval)::date anniversary FROM event, generate_series(13, 113) n ) SELECT event_id, event_date -- count(*) -- FROM anniversaries WHERE anniversary BETWEEN current_date AND current_date + interval '14' day; 

Idéia de C1 - Catcall reescrita

Além de pequenas otimizações, a principal diferença é adicionar apenas a quantidade exata de anos date_trunc('year', age(current_date + 14, event_date)) para obter o aniversário deste ano, o que evita a necessidade de um CTE completo:

 SELECT event_id, event_date FROM event WHERE (event_date + date_trunc('year', age(current_date + 14, event_date)))::date BETWEEN current_date AND current_date + 14; 

D - Daniel

 SELECT * -- count(*) -- FROM event WHERE extract(month FROM age(current_date + 14, event_date)) = 0 AND extract(day FROM age(current_date + 14, event_date)) <= 14; 

E1 - Erwin 1

Veja "1. Versão simples" acima.

E2 - Erwin 2

Veja "2. Versão avançada" acima.

E3 - Erwin 3

Veja "3. Versão da Magia Negra" acima.

G - Gordon

 SELECT * -- count(*) FROM (SELECT *, to_char(event_date, 'MM-DD') AS mmdd FROM event) e WHERE to_date(to_char(now(), 'YYYY') || '-' || (CASE WHEN mmdd = '02-29' THEN '02-28' ELSE mmdd END) ,'YYYY-MM-DD') BETWEEN date(now()) and date(now()) + 14; 

H - a_horse_with_no_name

 WITH upcoming as ( SELECT event_id, event_date ,CASE WHEN date_trunc('year', age(event_date)) = age(event_date) THEN current_date ELSE cast(event_date + ((extract(year FROM age(event_date)) + 1) * interval '1' year) AS date) END AS next_event FROM event ) SELECT event_id, event_date FROM upcoming WHERE next_event - current_date <= 14; 

W - wildplasser

 CREATE OR REPLACE FUNCTION this_years_birthday(_dut date) RETURNS date AS $func$ DECLARE ret date; BEGIN ret := date_trunc( 'year' , current_timestamp) + (date_trunc( 'day' , _dut) - date_trunc( 'year' , _dut)); RETURN ret; END $func$ LANGUAGE plpgsql; 

Simplificado para retornar o mesmo que todos os outros:

 SELECT * FROM event e WHERE this_years_birthday( e.event_date::date ) BETWEEN current_date AND current_date + '2weeks'::interval; 

W1 - consulta do wildplasser reescrita

O texto acima sofre de inúmeros detalhes ineficientes (além do escopo deste post já considerável). A versão reescrita é muito mais rápida:

 CREATE OR REPLACE FUNCTION this_years_birthday(_dut INOUT date) AS $func$ SELECT (date_trunc('year', now()) + ($1 - date_trunc('year', $1)))::date $func$ LANGUAGE sql; SELECT * FROM event e WHERE this_years_birthday(e.event_date) BETWEEN current_date AND (current_date + 14); 

Resultado dos testes

Eu executei este teste com uma tabela temporária no PostgreSQL 9.1.7. Os resultados foram coletados com EXPLAIN ANALYZE , melhor de 5.

Resultados

 Sem índice
 C: Tempo de execução total: 76714.723 ms
 C1: Tempo de execução total: 307.987 ms -!
 D: Tempo de execução total: 325.549 ms
 E1: Tempo total de execução: 253,671 ms -!
 E2: Tempo de execução total: 484.698 ms - min () & max () caro sem índice
 E3: Tempo total de execução: 213.805 ms -!
 G: Tempo de execução total: 984.788 ms
 H: Tempo de execução total: 977.297 ms
 W: Tempo de execução total: 2668.092 ms
 W1: Tempo de execução total: 596.849 ms -!

 Com índice
 E1: Tempo de execução total: 37.939 ms - !!
 E2: Tempo de execução total: 38.097 ms - !!

 Com índice na expressão
 E3: Tempo total de execução: 11.837 ms - !!

Todas as outras consultas executam o mesmo com ou sem índice porque usam expressões não-sargable .

Conclusão

  • Até agora, a consulta do @ Daniel foi a mais rápida.

  • A abordagem @wildplassers (reescrita) também é aceita de maneira aceitável.

  • A versão do @Catcall é algo como a abordagem inversa da minha. O desempenho fica fora de controle rapidamente com tabelas maiores.
    A versão reescrita funciona muito bem, no entanto. A expressão que eu uso é algo como uma versão mais simples da function this_years_birthday() do @ wildplassser.

  • Minha "versão simples" é mais rápida, mesmo sem índice , porque precisa de menos cálculos.

  • Com o índice, a "versão avançada" é tão rápida quanto a "versão simples", porque min() e max() tornam - se muito baratos com um índice. Ambos são substancialmente mais rápidos que os demais que não podem usar o índice.

  • Minha "versão da magia negra" é mais rápida com ou sem indexação . E é muito simples ligar.
    A versão atualizada (após o benchmark) é um pouco mais rápida ainda.

  • Com uma tabela da vida real, um índice fará uma diferença ainda maior . Mais colunas tornam a tabela maior e a varredura seqüencial mais cara, enquanto o tamanho do índice permanece o mesmo.

Eu acredito que o teste a seguir funciona em todos os casos, assumindo uma coluna chamada anniv_date :

 select * from events where extract(month from age(current_date+interval '14 days', anniv_date))=0 and extract(day from age(current_date+interval '14 days', anniv_date)) <= 14 

Como um exemplo de como funciona ao atravessar um ano (e também um mês), digamos que uma data de aniversário é 2009-01-04 e a data em que o teste é executado é 2012-12-29 .

Queremos considerar qualquer data entre 2012-12-29 e 2013-01-12 (14 dias)

age('2013-01-12'::date, '2009-01-04'::date) é 4 years 8 days .

extract(month...) disto é 0 e extract(days...) é 8 , que é menor que 14 então coincide.

Que tal agora?

 select * from events e where to_char(e."date", 'MM-DD') between to_char(now(), 'MM-DD') and to_char(date(now())+14, 'MM-DD') 

Você pode fazer a comparação como strings.

Para levar o ano em consideração, vamos converter de volta para as datas:

 select * from events e where to_date(to_char(now(), 'YYYY')||'-'||to_char(e."date", 'MM-DD'), 'YYYY-MM-DD') between date(now()) and date(now())+14 

Você precisa fazer um pequeno ajuste para o dia 29 de fevereiro. Eu poderia sugerir:

 select * from (select e.*, to_char(e."date", 'MM-DD') as MMDD from events ) e where to_date(to_char(now(), 'YYYY')||'-'||(case when MMDD = '02-29' then '02-28' else MMDD), 'YYYY-MM-DD') between date(now()) and date(now())+14 

Por conveniência, criei duas funções que geram o birsthday (esperado ou passado) no ano atual e o próximo aniversário.

 CREATE OR REPLACE FUNCTION this_years_birthday( _dut DATE) RETURNS DATE AS $func$ DECLARE ret DATE; BEGIN ret = date_trunc( 'year' , current_timestamp) + (date_trunc( 'day' , _dut) - date_trunc( 'year' , _dut) ) ; RETURN ret; END; $func$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION next_birthday( _dut DATE) RETURNS DATE AS $func$ DECLARE ret DATE; BEGIN ret = date_trunc( 'year' , current_timestamp) + (date_trunc( 'day' , _dut) - date_trunc( 'year' , _dut) ) ; IF (ret < date_trunc( 'day' , current_timestamp)) THEN ret = ret + '1year'::interval; END IF; RETURN ret; END; $func$ LANGUAGE plpgsql; -- -- call the function -- SELECT date_trunc( 'day' , t.topic_date) AS the_date , this_years_birthday( t.topic_date::date ) AS the_day , next_birthday( t.topic_date::date ) AS next_day FROM topic t WHERE this_years_birthday( t.topic_date::date ) BETWEEN current_date AND current_date + '2weeks':: interval ; 

NOTA: os castings são necessários porque eu só tinha timestamps disponíveis.

Isso deve lidar com wrap-arounds no final do ano também:

 with upcoming as ( select name, event_date, case when date_trunc('year', age(event_date)) = age(event_date) then current_date else cast(event_date + ((extract(year from age(event_date)) + 1) * interval '1' year) as date) end as next_event from events ) select name, next_event, next_event - current_date as days_until_next from upcoming order by next_event - current_date 

Você pode filtrar do que na expressão next_event - current_date para aplicar os “próximos 14 dias”

O case ... só é necessário se você considerar events que seriam “hoje” como “futuros” também. Caso contrário, isso pode ser reduzido à else parte da instrução case.

Observe que eu ” event_date ” a coluna "date" para event_date . Principalmente porque as palavras reservadas não devem ser usadas como um identificador, mas também porque a date é um nome de coluna terrível. Não diz nada sobre o que armazena.

Você pode gerar uma tabela virtual de aniversários e selecionar a partir dela.

 with anniversaries as ( select event_date, (event_date + (n || ' years')::interval)::date anniversary from events, generate_series(1,10) n ) select event_date, anniversary from anniversaries where anniversary between current_date and current_date + interval '14' day order by event_date, anniversary 

A chamada para generate_series(1,10) tem o efeito de gerar 10 anos de aniversários para cada event_date. Eu não usaria o valor literal 10 em produção. Em vez disso, eu calcularia o número correto de anos para usar em uma subconsulta ou usaria um literal grande como 100.

Você desejará ajustar a cláusula WHERE para ajustar-se ao seu aplicativo.

Se você tiver um problema de desempenho com a tabela virtual (quando houver muitas linhas em “events”), substitua a expressão de tabela comum por uma tabela base que tenha a estrutura idêntica. Armazenar aniversários em uma tabela base torna seus valores óbvios (especialmente para, digamos, aniversários de 29 de fevereiro), e as consultas em tal tabela podem usar um índice. Consultar uma tabela de aniversário de meio milhão de linhas usando apenas a instrução SELECT acima leva 25ms na minha área de trabalho.

Eu encontrei uma maneira de fazer isso.

 SELECT EXTRACT(DAYS FROM age('1999-04-10', '2003-05-12')), EXTRACT(MONTHS FROM age('1999-04-10', '2003-05-12')); date_part | date_part -----------+----------- -2 | -1 

Posso apenas verificar que o mês é 0 e os dias são menores que 14.

Se você tiver uma solução mais elegante, por favor, publique-a. Vou deixar a questão em aberto um pouco.

Eu não trabalho com o postgresql, então eu pesquisei suas funções de data e achei isso: http://www.postgresql.org/docs/current/static/functions-datetime.html

Se eu o ler corretamente, procurar events nos próximos 14 dias é tão simples quanto:

  where mydatefield >= current_date and mydatefield < current_date + integer '14' 

Claro que posso não estar lendo corretamente.