Filtering by complete days

Charts

Sometimes it can be useful to filter using complete date buckets (days, weeks, months, etc.), as opposed to the last 24 hours, 7 days, etc.

Create the chart to filter

Chartio’s Relative Date (ex.”last N days”) and Between Dates (ex. “between N and M days before now”) filter options work by subtracting days from the current timestamp.

For example, filtering using the “last N days” filter for the last day will show the results for the last 24 hours.

Filters

If you’d like to include full days instead of using timestamps, you can either use Relative Date Variables as part of your filters in Interactive Mode or you can edit your query in SQL Mode.

Interactive Mode

If you’d like to filter using full dates in Interactive mode, simply use the “between and including” filter option and utilize our Relative Date Variables as the start and end dates for your date filter instead of absolute dates.

For example, to filter for the full last 2 days, we would use the following as the start and end dates for our filter:

{TODAY.SUB(2,'days')}
{TODAY}

Using Relative Date Variables in filters

SQL Mode

If you prefer to adjust your date filters in SQL Mode, you can make a few simple changes to the auto-generated query to use the current date instead of the current timestamp.

For example, if we wanted to filter a date column for the last 2 days, we would use the “last N days” filter in Chartio to produce this query:

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

To filter by the last 2 complete days, simply change the NOW() function to a date using the CURRENT_DATE function:

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

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

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


Related Charts Help Articles

See more