Query database based on date range


I am trying to set up a page that queries a database based on a few items that I am picking in a retool app. I have it working where I can select from a list of organizations, and it will query the database based on that selection by the organization ID. I am trying to make it possible to select a date range as well and have it either query or filter the results based on that date range as well.

In the table component I was able to get this to work by using the "filter" section to filter based on the date picker values, but I am also trying to just get a count of all of the results based on the date picker range as well.

I can't figure out if there's a way to use the table component to reference elsewhere and return the filtered results. For instance, if I do {{table1.data.length}}, that returns the count of everything in the table rather than the filtered results based on date range. Alternatively, if I do a specific database query using the date range components as part of the actual query, I can't get it to handle a scenario where I don't have one or both dates picked.

For example, if I use this query -

FROM tickets
WHERE organization_id = {{listbox1.selectedItem.id}}
  AND created_at BETWEEN {{dateRange1.value.start}} AND {{dateRange1.value.end}};

This functions correctly if the dates are both provided, but just fails if one or both of them are not entered. I would like it to just exclude whichever one is not provided if that is the case, for instance if the "end" date is not provided, just get everything up through the current date. If neither are provided, just search everything regardless of the date.

I'm not sure I'm approaching this the right way in either way of handling it, but does anyone have any suggestions on how you could handle this scenario?

Hi Robby,

I imagine Retool has a quick way of doing this with the table component but I don't know what it is.

You could probably implement what you want by adding criteria to the SQL query, taking into account that dateRange.value.start and .end are empty strings when then have not been specified. You can open the "Show State" feature in the editor to see this.

Here's an example of someone who wanted to fix exactly this problem but not in retool: sql - Using WHERE clause with BETWEEN and null date parameters - Stack Overflow


Hi Robby! Are you looking for something like this by any chance?


Or this


Or even this


Let me know! :slight_smile: