Funções para Adicionar/Subtrair DIAS em uma Data em uma consulta HQL usando PostgreSQL (RESOLVIDO)

Assuntos Gerais
#1

Alguém saberia me dizer como se pode adicionar/subtrair intervalos de data com HQL ou Criteria? Tentei usar a função date_add e date_sub (ex.: date_sub(foo.data, 2 day)), mas o hibernate (usando Postgresql 8.3) está lançando a exceção: “… org.hibernate.hql.ast.QuerySyntaxException: unexpected token: day near line 1, column 219 …”

Quero fazer algo do tipo:


em.createQuery("select f, b from Foo f, Bar b where f.data = date_sub(b.data, 2 day) and ... (outras condições que não importam para este tópico)");

Alguém sabe como fazer isso?

#2

Porque você não faz a operação antes de passar para o hibernate?! Ou precisa ser necessariamente feito no lado do banco?!

#3

Por motivo de performance. Se eu fizer a operação com a data fora da consulta, terei que fazer várias consultas ao banco (uma para cada data que eu calcular “do lado de fora”)…

#4

Você quer pesquisar um intervalo de datas?! Se não for, coloca aí o que você pretende fazer!

#5

Preciso testar melhor, mas parece que consegui resolver o meu problema.

Vou tentar resumir meu problema.

O sistema que estou implementando precisa fazer “Lancamentos de Crédito” (Class LancamentoCredito (na verdade é LancamentoCreditoTarifado, mas vamos ignorar esse sufixo)) em uma determinada conta (Class BancoConta).

Cada LancamentoCredito está associado (@ManyToOne) a uma “Vigência Tarifária” (Class VigenciaTarifaria), ou seja, um LancamentoCredito possui 1 única VigenciaTarifaria e 1 VigenciaTarifaria pode ter vários LancamentoCredito’s.

LancamentoCredito também possui 1 campo BigDecimal chamado “valor”, 1 campo BancoConta chamado “conta” e 1 campo CreditoTipo chamado “creditoTipo” (que representa o tipo de lançamento, DAC, TED, DOC, Home Banking, etc…).

Essa VigenciaTarifaria possui um campo int chamado “floatDias”. Esse floatDias representa o D+floatDias que o crédito lançado realmente entrará na conta.

Em um certo relatório, eu preciso saber o “Total de Créditos” que será transferido para uma determinada BancoConta, de um determinado CreditoTipo, entre o intervalo de datas dataInicial e dataFinal.

Se o floatDias fosse sempre o mesmo, isso seria facilmente resolvido. Por exemplo, se o floatDias fosse SEMPRE igual a “2” e eu quisesse saber o total de créditos que entrou em uma conta, entre o dia 16/04/2009 e 30/04/2009:

Calcularia a dataIni e a dataFim “por fora” (com a classe GregorianCalendar), resultando dataIni = 14/04/2009 e dataFim = 28/04/2009 e executaria a consulta hql:

"select sum(lanc.valor) from LancamentoCredito lanc where (lanc.dataLancamento between :dataIni and :dataFim) and (lanc.conta = :bancoConta) and (lanc.creditoTipo = :creditoTipo)"

Acontece que o floatDias pode variar durante o período selecionado. Por exemplo, do dia 16/04/2009 ao dia 20/04/2009 floatDias pode ser 2, e a partir do dia 21/04 até o dia 30/04 o floatDias pode passar a ser 3…

Então eu precisaria de uma função que pudesse somar uma quantidade de dias dentro da query para poder me dar o resultado desejado. Felizmente dá pra fazer isso com o PostgreSQL usando a seguinte sintaxe:

Somar dias e horas a uma data (Fonte: http://pt.wikibooks.org/wiki/PostgreSQL_Prático/Funções_Internas/Data_e_Hora):

Só que para usar isso eu não poderia usar HQL, teria que usar sql nativo. Para continuar usando HQL, tive que criar minhas funções HQL “add_day(data, dias)” e “sub_day(data, dias)” e extender o dialeto org.hibernate.dialect.PostgreSQLDialect:

public class CustomPostgreSQLDialect extends PostgreSQLDialect {
	  public CustomPostgreSQLDialect() {
		  super();
		  registerFunction("add_day", new SQLFunctionTemplate(Hibernate.DATE, "( cast((?1) as Date) + cast((?2) || ' days' as interval) )"));
		  registerFunction("sub_day", new SQLFunctionTemplate(Hibernate.DATE, "( cast((?1) as Date) - cast((?2) || ' days' as interval) )"));		  
	  }
}

Agora, finalmente, eu posso fazer a consulta HQL que eu gostaria (não testado a fundo ainda) :

Query query = em.createQuery("select sum(lanc1.valor) from LancamentoCreditoTarifado lanc1, LancamentoCreditoTarifado lanc2 where (lanc1.dataLancamento = add_day(lanc2.dataLancamento, lanc2.vigenciaTarifaria.floatDias)) and (lanc2.conta = :conta) and (lanc1.conta = :conta) and (lanc1.creditoTipoTarifado = :creditoTipoTarifado) and (lanc2.creditoTipoTarifado = :creditoTipoTarifado) and (add_day(lanc2.dataLancamento, lanc2.vigenciaTarifaria.floatDias) between :dataIni and :dataFim)");

Espero que isso possa ajudar alguém no futuro!

#6