[how-to] Write a query that filters data using a date range component

With the Date Range component already created, you can link this component to your query by adding a WHERE clause that references the .start and .end properties of the Date Range component.



Here's an example query that returns data on or after the specific start date and before the specified end date.

Here's what the .start and .end properties look like in the State panel of the left panel in your app:

If you want to show all the data by default (and only show filtered data when both a start and end date are selected), you can write a query that looks something like this, which follows the query logic found in our docs here:

Copyable code:

SELECT * FROM leads 
WHERE ({{ !(datePickerForProjects.endValue&&datePickerForProjects.startValue) }} 
OR (leads.month <= {{moment(datePickerForProjects.endValue).format('YYYY-MM-DD')}} 
AND leads.month >= {{moment(datePickerForProjects.startValue).format('YYYY-MM-DD')}}) )

Common gotcha (date formatting :woozy_face:)

Sometimes the Date data the customer has is formatted in a different way and may cause the filtering to not work properly. For example, dates can sometimes be in period notation:

The table dates and the filter dates all need to be in the same format. In this case, you would have to modify the date to allow the filtering to work properly (‘YY-MM-DD’). You can do this by using a transformer like so:

You can also use the built-in library MomentJS to help you format dates if necessary. You can read more about Moment here and a using Moment in Retool here.

credit: @cperea :sparkles:

1 Like