WHERE Clause 🔍

The where clause picks which time period and which rows to include before totals are calculated.

Think of it as narrowing the data going into your query — like choosing dates, lines, products, or uptime states.

Syntax

AGGREGATE <expression> AS <name>
WHERE TIME FROM <start> TO <end>
[AND <filters>]
-- or
WHERE TIME BETWEEN <start> AND <end>
[AND <filters>]

Every query that does not use from a helper query must include a time range.

Queries that use from inherit the time range from the helper query and usually do not need another time clause on the outside.

Specifying Time Range

Start and end can be:

  • Fixed dates — 2024-01-01 00:00:00
  • Relative to now — now()-30D, now()-4h
  • Watermark — watermark() for near-real-time data
  • Aligned boundaries — date_trunc(...), adjustTimeToInterval(...)

Absolute Timestamps

Use YYYY-MM-DD HH:MM:SS:

AGGREGATE aggregation.performance AS performance
WHERE TIME FROM 2024-01-01 00:00:00 TO 2024-02-01 00:00:00
AND factory.name = 'Factory A'
GROUP BY line.name AS linename

Relative Timestamp Expressions

Format: now()-<number><unit>

Units:

- `s` → second
- `m` → minute
- `h` → hour
- `D` → day
- `W` → week
- `M` → month (28 days in bucket calculations)
- `Q` → quarter
- `Y` → year

Example (last 30 days):

AGGREGATE aggregation.performance AS performance
WHERE TIME FROM now()-30D TO now()
AND factory.name = 'Factory A'
GROUP BY line.name AS linename

Using Watermarks

watermark() returns a time based on the latest data received — useful for live dashboards.

AGGREGATE aggregation.performance AS performance
WHERE TIME FROM watermark() - 5m TO now()
AND factory.name = 'Factory A'
GROUP BY line.name AS linename

This typically means “from five minutes before the latest data arrived, through now.”

Advanced Timestamp Adjustments

  • date_trunc(timestamp, 'unit') — round down to the start of a hour, day, week, etc.
  • adjustTimeToInterval(timestamp, boundary) — snap to shift or run boundaries (e.g. shift.start)

Example: Truncate to Day

AGGREGATE aggregation.performance AS performance
WHERE TIME FROM date_trunc(now()-30D, 'D') TO date_trunc(now(), 'D')
AND factory.name = 'Factory A'
GROUP BY line.name AS linename

Example: Align to Shifts

AGGREGATE aggregation.performance AS performance
WHERE TIME FROM adjustTimeToInterval(now()-30D, shift.start)
      TO adjustTimeToInterval(now(), shift.start)
AND factory.name = 'Factory A'
GROUP BY line.name AS linename

AGGREGATE aggregation.performance AS performance
WHERE TIME FROM adjustTimeToInterval(now()-7D, interval.run.start)
      TO adjustTimeToInterval(now(), shift.start)
AND line.name = 'Line 1'
GROUP BY line.name AS linename

Multiple time ranges

You can list more than one time from ... to ... in the same where, joined with or, when the query needs multiple windows.

Optional Filters

Beyond time ranges, WHERE supports logical filters with AND / OR.

Example: AND

AGGREGATE aggregation.performance
WHERE TIME FROM watermark() - 5m TO now()
AND line.name = 'Line 1'
AND interval.state.type = 'Uptime'

âś… All conditions must be true.

Example: OR

AGGREGATE aggregation.performance
WHERE TIME FROM watermark() - 5m TO now()
AND (
    interval.state.type = 'Uptime'
    OR interval.state.type = 'Scrapping'
    OR product.name = 'Twizzlers'
)

âś… At least one condition must be true.

Unary NOT

You can negate a single predicate with NOT. It applies only to the predicate immediately after it (one comparison or parenthesized expression), not to the whole WHERE clause.

When it helps. Many comparisons have a built-in “opposite” you can use instead of NOT: for example != instead of negating =, NOT IN instead of IN, and IS NOT instead of IS. Unary NOT is most useful when there is no comparable inverse—common cases include:

  • A CONTAINS predicate (OQL does not define a single NOT CONTAINS operator, so you negate the whole comparison with NOT).
  • Predicates built with functions that express a “positive” check—such as containsAny(...) (“has any of these values/substrings”) isNotNull(...), or similar—where spelling the condition as NOT (...) is simpler or clearer than finding another comparator or function.
-- No dedicated "NOT CONTAINS"; use unary NOT on the comparison
AND NOT interval.state.comment CONTAINS 'blocked'

-- Function-style predicate; negate the whole expression
AND NOT containsAny(interval.state.comment, 'alarm', 'reset', 'fault')
  • NOT is case-insensitive (not, Not, NOT, etc.).
  • There must be at least one space or line break between NOT and the predicate. For example, NOTline.id = '…' is invalid; use NOT line.id = '…' or put NOT on its own line before the predicate.
  • You cannot put a line comment (-- …) between NOT and the predicate—only spaces or newlines count as separators there.
  • NOT NOT … is not valid OQL.

The same NOT / AND / OR rules apply in HAVING when you filter on aggregated results.

NOT binds tighter than AND / OR: NOT a AND b means (NOT a) AND b, not NOT (a AND b). Use parentheses when you need to negate a group:

AGGREGATE aggregation.performance
WHERE TIME FROM watermark() - 5m TO now()
AND NOT line.id = 'uuid'
AND interval.state.type = 'Uptime'

AGGREGATE aggregation.performance
WHERE TIME FROM watermark() - 5m TO now()
AND NOT (
    interval.state.type = 'Uptime'
    OR product.name = 'product_name'
)

Visual Cheat Sheet

Operator Purpose Example
= Equal to interval.state.type = 'Uptime'
!= Not equal to product.name != 'Widget'
> Greater than timeinif()/60 > 5
< Less than count(interval.run) < 5
<= / >= Comparison time.hour_of_day >= 8
IN / NOT IN Membership line.id IN ('uuid1', 'uuid2')
IS / IS NOT Null checks interval.run.end IS NOT NULL
CONTAINS Substring match interval.state.comment CONTAINS 'blocked'
NOT Negate next predicate NOT line.id = '…'
AND All conditions true line.name = 'Line 1' AND interval.state.type = 'Uptime'
OR Any condition true product.name = 'A' OR product.name = 'B'

✨ Use WHERE to filter raw data before aggregation. And combine with HAVING to filter aggregated results for powerful queries.