UI to filter query results based on timeframe

App Building
1

Hello,

I have a postgresql query that returns customer orders. I would like to allow the app user to filter this list of customer orders by some predefined time frames, for example:

  • Orders placed today
  • Orders placed in the past week
  • Orders placed in the last month

Is there an established pattern for how the query should be constructed, paired with components (select, etc) that folks have used to solve this in the past?

Thanks!

2

Hi @nick_salazar,

There are definitely multiple ways of doing thing, but my preferred option is to use a CASE in the WHERE clause. I'd use a Select (dropdown) component to populate the timeframe options because it's quite compact, but you can adjust based on your preferred UI.

An example of the query where the timeframes are mapped to the values 1, 2, and 3.

SELECT *
FROM orders
WHERE 
  CASE 
    WHEN {{timeframeSelect.value}} = 1 THEN 
      order_placed >= CURRENT_DATE AND order_placed < CURRENT_DATE + INTERVAL '1 day'
    WHEN {{timeframeSelect.value}} = 2 THEN 
      order_placed >= CURRENT_DATE - INTERVAL '7 days' AND order_placed < CURRENT_DATE + INTERVAL '1 day'
    WHEN {{timeframeSelect.value}} = 3 THEN 
      order_placed >= CURRENT_DATE - INTERVAL '1 month' AND order_placed < CURRENT_DATE + INTERVAL '1 day'
    ELSE 
      FALSE
  END;
3

Thank you very much @MikeCB for the quick response. I will try this out.

1 Like
4

@MikeCB this worked well, thank you for the info. really appreciate it!

1 Like