Syntax for INTERVAL use

Asked

Viewed 279 times

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:

  • data_opening: Start date of a call
  • data_nf: Date of the product invoice
  • Warranty: Quantity of months the product is under warranty

I have to calculate the INTERVAL, but I’m missing the syntax.

I’ve tried to:

INTERVAL(tbl_produto.garantia 'month')

INTERVAL 'tbl_produto.garantia month'

INTERVAL tbl_produto.garantia 'month'

But without satisfactory results.

postgresql

  • Which error appears? try concatenating the warranty value with Month, see if it works: INTERVAL tabela02.garantia || ' month'

  • The mistake is the famous Syntax error, and always refers to the table02.garantia. Your solution had no changes, continues in Syntax error

2 answers

2


Better than concatenating strings is doing arithmetic:

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

The suit of the guy interval can be multiplied gives great flexibility to date processing in Postgresql: 

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

  • Very useful and worked. But it is used when I know which drive I will add / decrease from the date with the INTERVAL. If I do not know, there is no way. But I marked as the right answer by the fact of flexibility.

1

My question was 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

Browser other questions tagged postgresql

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