Generate file D-1

Asked

Viewed 40 times

0

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();

1 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

  • Performing this first way, it returns me the argument error of AND must be type Boolean, not type date.

  • I forgot to put the name of the field that would be filtered rsrs. I also added the interval in the first command, which is the most correct way.

  • Ah yes. then I get into another problem, I don’t have a specific field that serves as date 'base', I just wanted all the data generated on the date of the day before the current day.

  • But you need to set the field so that postgres knows which records it has to filter. If it is more than one field you will be adding with the AND or, depending on your business rule, but you have to define which column will receive the filter.

  • Okay, I got the solution. I’m going to check which field I can use, I appreciate the help!

  • You can use the special values of Postgresql: YESTERDAY AND TOMORROW. Run the following test: SELECT CURRENT_DATE, 'YESTERDAY':date AS "Yesterday", 'TOMORROW':: date AS "Tomorrow";

Show 1 more comment

Browser other questions tagged

You are not signed in. Login or sign up in order to post.