Date-based filter conditions

Dates are composed of two parts: date and time. In general, when configuring a filter condition with a date field type, there are two types of conditions you can create: fixed date and relative date.

Note:

To filter features by date, the layer must support standardized SQL functions.

When filtering dates, enter the time component in your local time zone. Behind the scenes, the dashboard searches the target layer's properties for time zone information. If time zone information is found, queries are sent in that time zone. This may involve converting the input values to the target layer's time zone. If time zone information is not found, the dashboard converts the input values to coordinated universal time (UTC) before they are sent.

Tip:

Fixed-date conditions

Fixed-date filter conditions include a specific date value. You can enter this value can by choosing a date from the date picker on the Value tab or by choosing another date field on the Field tab. Choosing a date field works in the same way as entering a specific date value that can update based on data changes to the field.

Options for entering values for fixed date filters
Note:

The Field tab only appears if the element's layer includes more than one date field.

A fixed-date filter condition can only be created with the following operators:

  • is
  • is not
  • is before
  • is or is before
  • is after
  • is or is after

Note:

You can also create a fixed-date filter condition with a range of fixed dates by creating individual filters with fixed dates and combining them using logical operators.

Relative-date conditions

Relative-date filter conditions include a range of date values. The range can be anchored to or include the current moment in time (now), or it can be a predefined period (for example, last week). The range or anchor type you can enter depends on the operator you choose.

OperatorAnchor type

is within the last

Now

is before the last

Now

is within the next

Now

is after the next

Now

is

Now or predefined

is not

Now or predefined

is before

Now or predefined

is or is before

Predefined

is after

Now or predefined

is or is after

Predefined

Anchored to now

The benefit of using date ranges anchored to now is that they allow elements to refresh at the rate that data might be changing. For example, an indicator configured to display data that is within the last 30 minutes can potentially show different data every minute if its layer also refreshes that often. However, an indicator that shows data from yesterday will only show different data after today's date has changed (that is, after midnight) regardless of the layer's refresh interval.

When selecting an operator associated with an anchor type of now, you are prompted to specify a unit multiplier (N) as well as a time unit (for example, hour, minute, or second) or calendar unit (for example, day, month, quarter, or year).

Note:

Unit multipliers must always be whole values. For example, if you want to filter for half a day, instead of 0.5 days, your filter should be 12 hours.

Time units are measured from now (the current time). For example, if the time range is within the last 24 hours, the unit multiplier is 24 and the unit is hours. The range starts 24 hours before now and continues until now.

Date range with time unit

Calendar units include full days, from 12:00:00 a.m. to 11:59:59 p.m. for each day in the range, relative to the user's local time zone. For example, if the date range is within the last 7 days, the unit multiplier is 7 and the unit is day. The range starts at 12:00:00 a.m. 7 days before today.

Date range with calendar unit
The following table describes date ranges anchored to now. Note that all date values are in your local time zone.

OperatorTime unitDescription

is within the last

seconds

N seconds before now.

minutes

N minutes before now.

hours

N hours before now.

days

Starts at 12:00:00 a.m. N days before the current day up to now.

weeks

Starts at 12:00:00 a.m. on the first day of the week N weeks before now.

months

Starts at 12:00:00 a.m. on the first day of the month N months before now.

quarters

Starts at 12:00:00 a.m. on the first day of the quarter N quarters before now.

years

Starts at 12:00:00 a.m. on the first day of the year N years before now.

is before the last

seconds

N seconds before now and continues indefinitely.

minutes

N minutes before now and continues indefinitely.

hours

N hours before now and continues indefinitely.

days

Starts at 12:00:00 a.m. N days before the current day and continues indefinitely.

weeks

Starts at 12:00:00 a.m. N weeks before the current week and continues indefinitely.

months

Starts at 12:00:00 a.m. N months before the current month and continues indefinitely.

quarters

Starts at 12:00:00 a.m. N quarters before the current quarter and continues indefinitely.

years

Starts at 12:00:00 a.m. N years before the current year and continues indefinitely.

is within the next

seconds

Starts now and continues N seconds from now.

minutes

Starts now and continues N minutes from now.

hours

Starts now and continues N hours from now.

days

Starts now and continues until 11:59:59 p.m. N days from now.

weeks

Starts now and continues until 11:59.59 p.m. N weeks from now.

months

Starts now and continues until 11:59:59 p.m. on the last day of the month, N months from now.

quarters

Starts now and continues until 11:59:59 p.m. on the last day of the quarter, N quarters from now.

years

Starts now and continues until 11:59:59 p.m. on the last day of the year, N years from now.

is after the next

seconds

N seconds after now continues indefinitely.

minutes

N minutes after now and continues indefinitely.

hours

N hours now and continues indefinitely.

days

Starts at 12:00:00 a.m. N days after the current day and continues indefinitely.

weeks

Starts at 12:00:00 a.m. N weeks after the current week and continues indefinitely.

months

Starts at 12:00:00 a.m. N months after the current month and continues indefinitely.

quarters

Starts at 12:00:00 a.m. N quarters after the current quarter and continues indefinitely.

years

Starts at 12:00:00 a.m. N years after the current year and continues indefinitely.

is before

Now

Includes everything up to now.

is after

Now

Includes everything from now.

is

Year to date

Starts at 12:00:00 a.m. January 1 of the current year and continues to now.

is not

Year to date

Excludes everything in the current year to date. The current year to date starts at 12:00:00 a.m. January 1 of the current year and continues to now.

Anchored to a predefined period

Using date ranges anchored to a predefined period is appropriate when you don't need the data to update as frequently as the layer refreshes.

When selecting an operator associated with a predefined anchor type, you need to specify an interval on the Period tab.

Date range anchored to a predefined period

These periods are based on the following units. Note that start and end times are always based on your local time zone.

  • Day—A day starts at 12:00:00 a.m. and continues for 24 hours, ending at 11:59:59 p.m. On a 24-hour clock, a day starts at 00:00:00 and continues for 24 hours, ending at 23:59:59.
  • Week—A week starts at 12:00:00 a.m. on the first day of the week and continues for seven consecutive days, ending at 11:59:59 p.m. on the seventh day.
    Note:

    The first day of the week is determined by the calendar on your device. For example, a week may be Sunday to Saturday or Monday to Sunday.

  • Month—A month is defined according to the Gregorian calendar. Each month starts at 12:00:00 a.m. (local time) on the first day of the month and continues until 11:59:59 p.m. (local time) on the last day of the month.
  • Quarter—Each year is divided into quarters that are three months long, as described below.
    • 1st quarter—January through March
    • 2nd quarter—April through June
    • 3rd quarter—July through September
    • 4th quarter—October through December
  • Year—A year is the 12-month time period beginning at 12:00:00 a.m. on January 1 and ending at 11:59:59 p.m. on December 31.

Predefined time periodDescription

Today

Starts at 12:00:00 a.m. on the current day and continues for 24 hours.

Yesterday

Starts at 12:00:00 a.m. on the day before the current day and continues for 24 hours.

Tomorrow

Starts at 12:00:00 a.m. on the day after the current day and continues for 24 hours.

This week

Starts at 12:00:00 a.m. on the first day of the current week and continues for seven days.

Last week

The 7-day period that ends immediately before the current week starts. Last week starts at 12:00:00 a.m. on the first day of the week that precedes the current week and continues for 7 days.

Next week

The 7-day period that starts immediately after the current week ends. Next week starts at 12:00:00 a.m. the first day of the week after the current week and continues for 7 days.

This month

Starts at 12:00:00 a.m. on the first day of the current month and continues until 11:59:59 p.m. on the last day of the month.

Last month

Starts at 12:00:00 a.m. on the first day of the month that precedes the current month and continues until 11:59:59 p.m. on the last day of the month.

Next month

Starts at 12:00:00 a.m. on the first day of the month that follows the current month and continues until 11:59:59 p.m. on the last day of the month.

This quarter

Starts at 12:00:00 a.m. on the first day of the current quarter and continues for three months, ending at 11:59:59 p.m. on the last day of the quarter.

Last quarter

Starts at 12:00:00 a.m. on the first day of the quarter immediately preceding the current quarter and continues for three months, ending at 11:59:59 p.m. on the last day of the quarter.

Next quarter

Starts at 12:00:00 a.m. on the first day of the quarter after the current quarter ends, and continues for three months, ending at 11:59:59 p.m. on the last day of that quarter.

This year

Starts at 12:00:00 a.m. January 1 of the current year and continues for 12 months (through 11:59:59 p.m. on December 31).

Last year

Starts at 12:00:00 a.m. January 1 of the year immediately preceding the current year and continues for 12 months (through 11:59:59 p.m. on December 31).

Next year

Starts at 12:00:00 a.m. January 1 of the year immediately following the current year and continues for 12 months (through 11:59:59 p.m. on December 31).