Functions

OQL functions for the current user, relative dates, and date bucketing in group_by.

OQL ships with a small set of named functions for common dynamic values: the current user, relative time anchors, and date bucketing for group_by. All function names use uppercase by convention and always carry parentheses.

Identity

`ME()`

Resolves to the ID of the calling user. Valid on :object_id fields such as owner_id, assignee_id, and author_id.

{ "from": "contacts", "where": { "owner_id": "ME()" } }

{ "from": "actions", "where": { "assignee_id": "ME()", "completed": false } }

Relative dates

Date functions resolve in the requesting user’s profile timezone, and each one denotes a period — a range, not an instant. TODAY() is the whole of today; THIS_WEEK() is the whole week. What the range means in a filter depends on the operator:

Equality matches during the period. { "date": "TODAY()" } returns anything dated today.
Comparisons project the period’s endpoint. { "date": { "<": "TODAY()" } } → before today (yesterday or earlier); { "date": { ">": "TODAY()" } } → after today (tomorrow onwards). >= starts at the period, <= runs through the end of it.

Available functions

Function	Period
`TODAY()`	Today
`YESTERDAY()`	Yesterday
`OVERDUE()`	Everything dated before today
`THIS_WEEK()` · `THIS_MONTH()` · `THIS_QUARTER()` · `THIS_YEAR()`	The current week / month / quarter / year
`LAST_WEEK()` · `LAST_MONTH()` · `LAST_QUARTER()` · `LAST_YEAR()`	The previous week / month / quarter / year
`NEXT_WEEK()` · `NEXT_MONTH()` · `NEXT_QUARTER()` · `NEXT_YEAR()`	The next week / month / quarter / year
`LAST_7_DAYS()` · `LAST_14_DAYS()` · `LAST_30_DAYS()`	The last N calendar days, including today
`NEXT_7_DAYS()` · `NEXT_14_DAYS()` · `NEXT_30_DAYS()`	The next N calendar days, including today
`{"DAYS_AGO": [n]}`	The single day `n` days ago

OVERDUE() is purely a date predicate (“dated before today”). Pair it with a state filter to scope by task status:

{ "from": "actions", "where": { "date": "OVERDUE()", "completed": false } }

DAYS_AGO is the only function that takes an argument and therefore uses object form. n is an integer; negative values resolve to a date in the future.

{ "from": "contacts", "where": { "created_at": { ">=": { "DAYS_AGO": [7] } } } }

{ "from": "deals", "where": { "close_date": "LAST_QUARTER()" } }

Because each function is a range, it also pairs with between — a function endpoint uses its period’s half-open range (exclusive of the first instant after the period), while a literal endpoint is taken exactly. See Operators › between.

{
  "from": "calls",
  "where": {
    "call_time": { "between": [{ "DAYS_AGO": [30] }, "TODAY()"] }
  }
}

Date bucketing (for `group_by`)

When grouping by a date or time field, you must specify a bucket function. Raw timestamp grouping is rejected because it would key on the millisecond.

Function	Bucket
`DAY`	Calendar day
`WEEK`	Calendar week
`MONTH`	Calendar month
`QUARTER`	Calendar quarter
`YEAR`	Calendar year

Each takes a single field argument and uses object form:

{
  "from": "deals",
  "select": ["close_date", "count()", { "sum": ["amount"] }],
  "where": { "status": "won" },
  "group_by": [{ "MONTH": ["close_date"] }]
}

Bucket boundaries resolve in the requesting user’s profile timezone. The bucket field returned in each row is the start of the bucket, in ISO 8601 with the account’s offset.

Date bucketing functions are case insensitive (MONTH, Month, and month all work), but uppercase is the convention and is what the errors quote back to you.

Where functions can appear

Function	In `where`	In `group_by`
`ME()`	Y	—
Date functions (`TODAY()`, `OVERDUE()`, `THIS_()`, `LAST_()`, `NEXT_()`, `_DAYS()`)	Y	—
`DAYS_AGO`	Y	—
`DAY` / `WEEK` / `MONTH` / `QUARTER` / `YEAR`	—	Y

Mixing them across roles is rejected. For example, a date bucketing function in where, or ME() against a non-id field, fails validation with a clear error.