Filtering by complete days

Charts

Sometimes it’s useful to filter using complete date buckets (days, weeks, months, etc.), as opposed to the last 24 hours, seven days, etc.

Chartio’s “between N and M days before now” and “last N days” filters work by subtracting days from the current timestamp; filtering by Last N days will show the results for the last 24 hours.

Create the chart to filter

You also need to make a few changes to the query in SQL Mode. Instead of filtering using the current timestamp, use the current date.

For example, if we wanted to filter a date column for the last two days, we use the last two days filter in Chartio to produce this query:

WHERE ("date_column" BETWEEN NOW() - INTERVAL '2 DAY' AND NOW())

If we wanted to filter by the last two complete days, change the NOW() to a date:

WHERE ("date_column" BETWEEN CURRENT_DATE - INTERVAL '2 DAY' AND CURRENT_DATE)

This will filter the query to the last two complete days as opposed to the last 48 hours.

This example uses Postgres, but for other databases, replace the NOW() and CURRENT_DATE with the equivalent timestamp and current date variables.


Related Charts Help Articles

See more