API Reference
Start typing to search…

DateTime Operators

Octopus API provides a number of special functions that can be used when searching on DateTime fields. These functions allow you to search for dates and date ranges relative to the current date.

The following functions are available when searching on DateTime fields:

  • $NOW() - Evaluates to the current date and time.
  • $TODAY() - Evaluates to the current date, from 00:00:00 to 23:59:59.
  • $THISWEEK() - Evaluates to the current calendar week, from 00:00:00 on Monday morning to 23:59:59 on Sunday night.
  • $THISMONTH() - Evaluates to the current calendar month, from 00:00:00 on the first day of the month to 23:59:59 on the last day of the month.
  • $THISYEAR() - Evaluates to the current calendar year, from 00:00:00 on 1 January to 23:59:59 on 31 December.

Each of the special functions outlined in this section evaluates to a specific date range. However, when included in a search query, only the upper- or lower-bound of that range is used in the evaluation of the search, depending on whether the search operator being used is inclusive or exclusive.

The following conventions are used when evaluating search queries that include a special date range operator:

  • $LT - The lower-bound of the operator's range is used, and the query matches all entries with a value below the lower-bound, excluding values within the range.
  • $LE - The upper-bound of the operator's range is used, and the query matches all entries with a value below the upper-bound, including values within the range.
  • $GT - The upper-bound of the operator's range is used, and the query matches all entries with a value above the lower-bound.
  • $GE - The lower-bound of the operator's range is used, and the query matches all entries with a value above the upper-bound, including values within the range.
  • $RANGE - When used as the lower-bound of a $RANGE query, the operator's lower-bound is used, and when used as the upper-bound of a $RANGE query, the operator's upper-bound is used, so values within the operator's range are included in the results.
  • $NRANGE - When used as the lower-bound of a $RANGE query, the operator's upper-bound is used, and when used as the upper-bound of a $RANGE query, the operator's lower-bound is used, so values within the operator's range are not included in the results.

For example, the $TODAY() function evaluates to a date range from a time of 00:00:00 to 23:59:59 on the current date. When used in a $LT (less-than) query, the lower-bound of the range (00:00:00) would be used, and the search would return all entries with a DateTime occurring before the current date. Alternatively, when used in a $LE (less-than-or-equal-to) query, the upper-bound of the range (23:59:59) would be used, and the search would return all entries with a DateTime occuring on or before the current date.

$NOW()

  • Available Since API Version: 2.7

The $NOW() function may be used to search for DateTime values relative to the current time. When included in a search query, it evaluates to the current date and time. 

Example with $LT

The following example illustrates how you could use the $NOW() function to search for all entries with a CreationDate before the current time.

{
  "SearchQuery":{
    "CreationDate": {
      "$LT": "$NOW()"
    }
  }
}

If the above query were to be executed on 1 January 2016 at 2:30 in the afternoon, it would be equivalent to the following query:

{
  "SearchQuery":{
    "CreationDate":{
      "$LT": "2016-01-01T14:30:00"
    }
  }
}

Example with $LE

The following example illustrates how you could use the $NOW() function to search for all entries with a CreationDate on or before the current time.

{
  "SearchQuery":{
    "CreationDate": {
      "$LE": "$NOW()"
    }
  }
}

If the above query were to be executed on 1 January 2016 at 2:30 in the afternoon, it would be equivalent to the following query:

{
  "SearchQuery":{
    "CreationDate":{
      "$LE": "2016-01-01T14:30:00"
    }
  }
}

$THISMONTH()

  • Available Since API Version: 2.7

The $THISMONTH() function may be used to search for DateTime values relative to the current month. When included in a search query, it evaluates to a date range including the first and last days of the current calendar month. 

Example with $LT

The following example illustrates how you could use the $THISMONTH() function to search for all entries with a CreationDate before the current month.

{
  "SearchQuery":{
    "CreationDate": {
      "$LT": "$THISMONTH()"
    }
  }
}

If the above query were to be executed during the January 2016 calendar year, it would be equivalent to the following query:

{
  "SearchQuery":{
    "CreationDate":{
      "$LT": "2016-01-01T00:00:00"
    }
  }
}

Example with $LE

The following example illustrates how you could use the $THISMONTH() function to search for all entries with a CreationDate before or during the current month.

{
  "SearchQuery":{
    "CreationDate": {
      "$LE": "$THISMONTH()"
    }
  }
}

If the above query were to be executed during the January 2016 calendar year, it would be equivalent to the following query:

{
  "SearchQuery":{
    "CreationDate":{
      "$LE": "2016-01-31T23:59:59"
    }
  }
}

$THISWEEK()

  • Available Since API Version: 2.7

The $THISWEEK() function may be used to search for DateTime values relative to the current week. When included in a search query, it evaluates to a date range including the first and last days of the current calendar week. 

Example with $LT

The following example illustrates how you could use the $THISWEEK() function to search for all entries with a CreationDate before the current month.

{
  "SearchQuery":{
    "CreationDate": {
      "$LT": "$THISWEEK()"
    }
  }
}

If the above query were to be executed during the second week of January 2016, it would be equivalent to the following query:

{
  "SearchQuery":{
    "CreationDate":{
      "$LT": "2016-01-04T00:00:00"
    }
  }
}

Example with $LE

The following example illustrates how you could use the $THISWEEK() function to search for all entries with a CreationDate before or during the current week.

{
  "SearchQuery":{
    "CreationDate": {
      "$LE": "$THISWEEK()"
    }
  }
}

If the above query were to be executed during the second week of January 2016, it would be equivalent to the following query:

{
  "SearchQuery":{
    "CreationDate":{
      "$LE": "2016-01-10T23:59:59"
    }
  }
}

$THISYEAR()

  • Available Since API Version: 2.7

The $THISYEAR() function may be used to search for DateTime values relative to the current year. When included in a search query, it evaluates to a date range including the first and last days of the current calendar year. 

Example with $RANGE

The following example illustrates how you could use the $THISYEAR() function to search for all entries with a CreationDate during the current year.

{
  "SearchQuery":{
    "CreationDate": {
      "$RANGE": [
        "$THISYEAR()",
        "$THISYEAR()"
      ]
    }
  }
}

If the above query were to be executed during the 2016 calendar year, it would be equivalent to the following $RANGE query:

{
  "SearchQuery":{
    "CreationDate":{
      "$RANGE":[
        "2016-01-01T00:00:00",
        "2016-12-31T23:59:59"
      ]
    }
  }
}

$TODAY()

  • Available Since API Version: 2.7

The $TODAY() function may be used to search for DateTime values relative to the current day. When included in a search query, it evaluates to a date range spanning the current day from 00:00:00 in the morning to 23:59:59 at night. 

Example with $LT

The following example illustrates how you could use the $TODAY() function to search for all entries with a CreationDate before the current day.

{
  "SearchQuery":{
    "CreationDate": {
      "$LT": "$TODAY()"
    }
  }
}

If the above query were to be executed on 1 January 2016, it would be equivalent to the following query:

{
  "SearchQuery":{
    "CreationDate":{
      "$LT": "2016-01-01T00:00:00"
    }
  }
}

Example with $LE

The following example illustrates how you could use the $TODAY() function to search for all entries with a CreationDate on or before the current day.

{
  "SearchQuery":{
    "CreationDate": {
      "$LE": "$TODAY()"
    }
  }
}

If the above query were to be executed on 1 January 2016, it would be equivalent to the following query:

{
  "SearchQuery":{
    "CreationDate":{
      "$LE": "2016-01-01T23:59:59"
    }
  }
}