Filtering by date ranges

Filtering data by date ranges is as simple as filtering any field with comparison operators. This example queries survey records where the customer response date was between February 1, 2019 and June 1, 2019. The date must be formatted as yyyy-mm-dd.

Tip: For feedback operations, when you are filtering by a specific timeperiod, run your query several hours after the period ends to ensure completeness. 10 hours is usually a good delay.

Data query

query getData($filter: Filter){
  feedback(
    filter: $filter
  ){
    nodes{ #object with data
        fieldData(fieldId:"e_responsedate"){ values}
    }
  }
}

Variables

{
  "filter": {
    "and": [
      {"fieldIds": ["e_responsedate"], "gt": "2019-02-01"},
      {"fieldIds": ["e_responsedate"], "lt": "2019-06-01"}
    ]
  }
}

Using relative dates and periods

The example above shows absolute dates, but relative dates and date ranges can also be specified in the queries. Use relative dates to perform date calculations and get expressions such as "the first day of the month in which start date occurs" or "the day after start date".

Use relative expressions to represent any subset of the time coordinates in relation to the local date. This relation is expressed replacing the fixed coordinate values by parenthesis and a signed offset. For instance if today is May 25, 2019 then the expression (+0)-(+1)-10 represents the June 10 (next month) for 2019 (current year).

Expressions can include ceiling/floor rounding to a given time unit. For this the date should be enclosed by a rounding function which is named like the time unit with a prefix sign which is "+" for ceiling and "-" for floor.

The following table lists example date patterns for relative date expressions.

Date patternResult when today is 9 July, 2019Description
(+1)-05-252020-05-25Year 2020 = (2019 + 1), month 5 and day 25 are given as absolute
(+0)-(+1)-252019-08-25Year 2019 = (2019 + 0), month 8 = (7 + 1), day 25 is absolute
 (-1)-(+0)-(+0)2018-07-09Year 2018 = (2019 - 1), month 7 = (7 + 0), day 9 = (9 + 0)
1982-(+0)-(+0)1982-07-09Year 1982 is absolute, month 7 = (7 + 0), day 9 = (9 + 0)

The following table lists actual dates for various date ranges with rounding in effect.

Date patternResult when today is 9 July, 2019Description
+month(2018-05-25)2018-05-3131 because that day is the end of the month
-week(2018-05-25)2018-05-2020 because that day is the start of the week
+Year(2018-05-25)2018-12-3112-31 because that is the end of the year
+QUARTER((+1)-05-25))2020-06-30Year 2020 = (2019 + 1), month 6 and day 30 because that is when the quarter ends
+WEEK(1982-(+0)-(+0))1982-07-10Year 1982 is absolute, month 7 = (7 + 0), day 10 because that day is the end of the week.

This example queries survey records where the customer response date was between February 1, 2019 and March 31, 2019 (the end of that quarter in the calendar year).

Data query

query getData($filter: Filter){
  feedback(
    filter: $filter
  ){
    nodes{ #object with data
        fieldData(fieldId:"e_responsedate"){ values}
    }
  }
}

Variables

{
  "filter": {
    "and": [
      {"fieldIds": ["e_responsedate"], "gte": "2019-02-01", "lt":"+QUARTER(2019-02-01)"}
}