CODEWITHSUNDEEP
postgresqlpostgresql-8.4
NakaBr
NakaBr

Reputation: 795

PostgreSQL: Add Interval to Timestamp from column value

I need to add minutes coming from an integer column with a timestamp to compare to another column.

Here's an example: 

 SELECT t1.id_liame, t1.id_table, t1.periodicidade , t3.data_extracao, 
    CASE WHEN(NOW() < (e.data_extracao + INTERVAL t1.periodicidade || '
    MINUTES')) 
    THEN 'yes' ELSE 'no' END
    FROM table1 as t1 LEFT JOIN liame_has_extracao as t2 USING(id_liame)
    LEFT JOIN extracao as t3 USING(id_extracao)

l.periodicidade is integer (minutes)
I need to verify if data_extracao(timestamp) is greater then NOW() + l.periodicidade(integer - minutes).

How can i do it?

Upvotes: 18

Views: 30197

Answers (1)

Frank Bollack
Frank Bollack

Reputation: 25166

You can write your query like this:

SELECT 
   t1.id_liame,
   t1.id_table,
   t1.periodicidade,
   t3.data_extracao,
   CASE
      WHEN(NOW() < (t3.data_extracao + (INTERVAL '1 min' * t1.periodicidade))) 
      THEN 'yes' ELSE 'no'
   END
FROM table1 AS t1
LEFT JOIN liame_has_extracao AS t2 USING(id_liame)
LEFT JOIN extracao AS t3 USING(id_extracao)

As you can see, you can multiply intervals with integers so with INTERVAL '1 minute' you define your base unit and multiply your actual time interval.

Hope that helps

Upvotes: 36

Related Questions