Generate file D-1

Question

How to perform the select of data in format D-1 (current day - 1), what would be in VBA a AGORA() - 1, how it would be in postgresql?

I have the following query and would like to take yesterday to generate reports.

select * 
from 
    ivr_contatos, 
    ivr_campanha,
    ivr_business 
where ivr_campanha.id = '1' 
    and ivr_contatos.campanha = '1' 
    and ivr_business.idvisita = ivr_contatos.codigo 
    and ivr_contatos.status = 0 
    and tentativas >= qtdtentativas 
    and date();

Answer 1

The CURRENT_DATE which returns the current date. If you need the date and time use the CURRENT_TIMESTAMP.

select * 
from 
    ivr_contatos, 
    ivr_campanha,
    ivr_business
where ivr_campanha.id = '1' 
    and ivr_contatos.campanha = '1' 
    and ivr_business.idvisita = ivr_contatos.codigo 
    and ivr_contatos.status = 0 
    and tentativas >= qtdtentativas 
    and nome_do_campo = CURRENT_DATE - interval '1 day';

There are other possibilities such as defining the range (interval) that you want to add or subtract in your query, if for example you need the date of the previous month, could perform the query as below:

select * 
from 
    ivr_contatos, 
    ivr_campanha,
    ivr_business
where ivr_campanha.id = '1' 
    and ivr_contatos.campanha = '1' 
    and ivr_business.idvisita = ivr_contatos.codigo 
    and ivr_contatos.status = 0 
    and tentativas >= qtdtentativas 
    and nome_do_campo = CURRENT_DATE - interval '1 month';

https://www.postgresql.org/docs/10/static/datatype-datetime.html