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?

share|improve this question
Aside: wrong alias in your query: e.data_extracao <--> extracao as t3 – Erwin Brandstetter Jul 2 '12 at 16:24
feedback

1 Answer

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

share|improve this answer
Great help! Thanks – Mike Blyth Jul 25 '12 at 13:28
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.