The query term for a date-time interval supports date delta expressions whenever the begin date-time or the end date-time uses the keyword TODAY or NOW. It is awkward for the user to write a different fixed date-time interval each time he wants to query over the last 30 days, for example:
service_date:[2015-10-06 TO 2015-11-05]He may now write a relative date-time expression to cover a date-time interval relative to the current date (or date-time), eliminating the need to change the dates in the query each time he runs the report. To do so, a pivot term (fixed term) and a delta term (the increment from the pivot term) are required.
Delta Operators
The delta refers to the date or time as it revolves around the pivot term. It is possible to use either a positive or a negative term in the search.
Below is a list of operators used to increment or decrement the date or time:
- ±1s: second
- ±1mi: minute
- ±1h: hour
- ±1d: day
- ±1w: week
- ±1mo: month
- ±1q: quarter
- ±1y: year
By convention, the start of the week is Sunday.
To increment or decrement more than one unit, enter the appropriate number instead of 1. For exactly one unit, the 1 is optional. The following queries over the last 30 days:
service_date:[TODAY-30d TO TODAY]%2B; otherwise, it gets converted to a blank. However, when entering data into the code section of Report Builder, the software typically does the percent encoding, and the + can be entered as +.Pivot Term
The keyword TODAY refers to the current date.
The keyword NOW refers to the current date-time (accurate to the nearest second).
Time zones used for reference points depend on which version you are using.
- Version 8.x and earlier: the server's local time zone
- Version 9.x and beyond: UTC time
As a convenience, either keyword, TODAY or NOW, only needs to appear in one term of the query, but one of the keywords must appear in at least one term. The other term may be a pure date delta expression. The two examples below yield the same results:
service_date:[-30d TO TODAY]service_date:[TODAY-30d TO TODAY]As long as one term resolves to an absolute date-time, known as the "pivot," the other term is computed from the pivot value.
You can use pivot terms when searching for a single date. The following returns all records that were indexed the prior day:
indextime:[TODAY-1d]Here, the ending date is the day before the query was executed, and the start date is 30 days before that. The pivot is TODAY minus one day (yesterday). The start date is the pivot value minus 30 days.
service_date:[-30d TO TODAY-1d]The pivot can be in the start term:
appointment_date:[TODAY+d TO 30d]Note that the "1" was optional in this example: "+d" is the same as "+1d." Also, the lead "+" was inferred for "30d" (same as "+30d"). The example indicates tomorrow is the pivot (TODAY plus one day), and the end date is tomorrow plus thirty days.
Date Delta with Absolute Date-time Strings
An absolute date string may be entered instead of the word TODAY or NOW. Replacing an absolute date or time performs the same functionality as a pivot term.
service_date:[-1mo TO 2026-11-06]service_date:[-1mo TO 20151106]service_date:[-1y TO 2015-10y]. The parser treats the hyphen as part of the date string and issues an error message. To correct the ambiguity, enter a space after the year: service_date:[-1y TO 2015 -10y].Round Down and Round Up Operators
The < (round down) and > (round up) symbols are used to round the relative date-time down or up to the boundary indicated by the time unit (minute, hour, day, week, month, quarter or year).
The following examples round TODAY down to the beginning of the year, causing 2026-01-01 (given 2026 is the current year) to be the pivot. The start date is one year earlier: 2025-01-01. Rounding always results in a legitimate date.
service_date:[-1year TO TODAY<year]service_date:[-1y TO TODAY<y]The examples below search for the service dates of last week. "TODAY<w" rounds down to the beginning of this week (to become the pivot). The start date is one week prior to that date.
service_date:[-1week TO TODAY<week]service_date:[-w TO TODAY<w]Inclusive or Exclusive Brackets
Use inclusive brackets, [ or ], to include dates, or exclusive braces, { or }, to eliminate the start day or the end date of the search.
This example uses the inclusive brackets for both start-of-week days, thus spanning eight days from Sunday to Sunday:
service_date:[-w TO TODAY<w]These next two examples use mixed symbols to span Monday through Sunday and return the same records (provided the current date is 6 November 2015):
service_date:{-w TO TODAY<w]service_date:{-w TO 20151106<w]This example returns records from Sunday through Saturday:
service_date:[-w TO TODAY<w}Compound Date Delta Expressions
Multiple date delta terms can be combined to demark the appropriate date as long as explicit operators separate them.
This example start date has one day added to the beginning of the previous week, making the start date begin on a Monday. The query runs from Monday to Sunday.
service_date:[-w+d TO TODAY<w]This is a more complex example. The pivot date is the end of this month plus two weeks. The start date is last year plus two months plus five days computed from the pivot date:
appointment_date:[-y+2mo+5d TO TODAY>mo+2w]