combine 4 queries in one

Question

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

Answer 1

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';