---
title: "Functions"
description: "OQL functions for the current user, relative dates, and date bucketing in group_by."
canonical_url: https://developer.onepagecrm.com/oql/functions/
source: Markdown mirror of https://developer.onepagecrm.com/oql/functions/
---

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`.

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

```json
{ "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:

```json
{ "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.

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

```json
{ "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](/oql/operators/).

```json
{
  "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:

```json
{
  "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.