CODEWITHSUNDEEP
postgresql
user1920845
user1920845

Reputation: 343

Date subtraction in postgres

I want to subtract minute from NOW() and the value of "how many minutes" I am reading from another table:

SELECT * FROM A, B 
WHERE 
   A.entity_type_id = B.entity_type_id  
   AND A.status = 'PENDING'  
   AND A.request_time < (NOW() - INTERVAL B.retry_interval MINUTE) 
   AND A.retry_count >= B.retry_allowed_count

Here the problem is B.retry_interval is fetched from another table, while normally the queries like these are A.request_time < (NOW() - INTERVAL '10 MINUTE')

How do I achieve this?

Upvotes: 4

Views: 13262

Answers (2)

whehe
whehe

Reputation: 11

I know its quite old enough, but here what i do. Btw my postgres version is 9.4.18.

try using interval data type. Convert the number from column to sting and concat it.

   SELECT * 
   FROM A, B WHERE 
   A.entity_type_id = B.entity_type_id  
   AND A.status = 'PENDING'  
   AND A.request_time < (NOW() - concat(B.retry_interval::text,'minute')::interval
   AND A.retry_count >= B.retry_allowed_count

Thanks to my coworker who find "interval" data type

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125454

Multiply the interval by interval '1 minute'

SELECT *
FROM A, B 
WHERE 
   A.entity_type_id = B.entity_type_id  
   AND A.status = 'PENDING'  
   AND A.request_time < NOW() - B.retry_interval * INTERVAL '1 minute'
   AND A.retry_count >= B.retry_allowed_count

Upvotes: 5

Related Questions