0

I have 4 queries I want to combine in one to reduce DB calls. Each interval list the products for that time interval and is used for a specific sub treatment.

select * from product where add_time >= NOW() - INTERVAL '12 hour' (get product add the last 12h)   
select * from product where add_time >= NOW() - INTERVAL '24 hour' (get product add the last 24h)   
select * from product where add_time >= NOW() - INTERVAL '7 day' (get product add the last week)   
select * from product where add_time >= NOW() - INTERVAL '30 day' (get product add the last month)    

Is it possible? what will the query look like and how to access each group interval (12h, 24h, 7 day, 30 day) to process the result?

PostgreSQL 9.5.12

0

1 Answer 1

4

Just do it in the client if you must.

SELECT *
FROM product
WHERE add_time >= NOW() - interval '30 day';

But if you must you can offload some of this to the server, thought I wouldn't because it's largely view-logic.

SELECT
  add_time >= NOW() - interval '12 hours' AS 12_hours,
  add_time >= NOW() - interval '24 hours' AS 24_hours,
  add_time >= NOW() - interval '7 day' AS 7_day,
  *
FROM product
WHERE add_time >= NOW() - interval '30 day';

You could also put it all into a case statement if you really wanted...

SELECT
  CASE
    WHEN add_time >= NOW() - interval '12 hours' THEN '12h'
    WHEN add_time >= NOW() - interval '24 hours' THEN '24h'
    WHEN add_time >= NOW() - interval '7 day' THEN '7d'
  END,
  *
FROM product
WHERE add_time >= NOW() - interval '30 day';
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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