[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.

date-range-filter

Examples

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:

3 Likes

This is how I filter a datetime column between two dates:

hi! what might this mean, when I insert your condition into my query?
invalid input syntax for type boolean: ""

Hi @Ana,

Thanks for reaching out. Can we see a screenshot? I'm guessing that one of the conditions isn't evaluating to true or false

There's a way to create a timerange like analytics timerange?

With "today", "yesterday", "last week"?

@igordisco , you could create some buttons that set these values to the dateRange.value.start and .end.

It requires some JS to calculate the proper start and end dates, but should not be difficult with the momentJS library. You can calculate dates eg, today minus 1 week or 12 days.

2 Likes

Agreed! Something like this:

1 Like

Yeah! That's works.

The only problem is design, but it's not a big problem.

But work like a charm!