Syntax for using INTERVAL

Question

Syntax for using INTERVAL

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

I have the following excerpt from a CASE in my SQL

SELECT  CASE WHEN (tabela01.data_abertura - INTERVAL tabela02.garantia 'month') <= tabela01.data_nf
             THEN 'Dentro do Prazo'
             ELSE 'Fora do Prazo'
        END  AS medicao_garantia

... Where:

date_abertura: Start date of a service
data_nf: Product invoice date
Warranty: Number of months the product is under warranty

I have to compute INTERVAL , but I'm missing the syntax.

I've tried:

INTERVAL(tbl_produto.garantia 'month')

INTERVAL 'tbl_produto.garantia month'

INTERVAL tbl_produto.garantia 'month'

But with no satisfactory results.

postgresql

asked by anonymous 02.08.2016 / 15:56

2 answers

1

My question has been resolved as follows:

SELECT  CASE WHEN (tabela01.data_abertura::date - (tabela02.garantia || 'month')::interval) <= tabela01.data_nf
             THEN 'Dentro do Prazo'
             ELSE 'Fora do Prazo'
        END  AS medicao_garantia

02.08.2016 / 18:48

date conversion with php [duplicate] Help with textbox validation

score 2 · Accepted Answer

Rather than concatenating strings is doing arithmetic:

t1.data_abertura::date - t2.garantia * interval '1 month' <= t1.data_nf

The fact that type interval can be multiplied gives a lot of flexibility when processing dates in Postgresql:

select '2016-08-03'::date + 1.5 * interval '1 day';
      ?column?       
---------------------
 2016-08-04 12:00:00