PostgreSQL: Add Interval to Timestamp from column value

Question

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?

Aside: wrong alias in your query: e.data_extracao <--> extracao as t3 — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 2, 2012 at 16:24

Erwin Brandstetter · Accepted Answer · 2012-07-02 16:26:10Z

36

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

edited Jul 2, 2012 at 16:26

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

answered Jul 2, 2012 at 14:28

Frank Bollack

25.3k5 gold badges51 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: Add Interval to Timestamp from column value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related