SuiteQL doesn't accept INTERVAL '30' MINUTE

The query

A queue processor needed to pick up work items stuck in a processing state for more than thirty minutes, the usual sign a previous run died mid-flight. The natural way to write that:

SELECT id FROM work_queue
WHERE status = 'PROCESSING'
  AND lastmodified < SYSDATE - INTERVAL '30' MINUTE

It fails with a parse error pointing at '30'. In a Suitelet the user sees an empty screen or a generic error. In a Map/Reduce the input stage fails and nothing runs.

Why

SuiteQL is close to Oracle SQL but not identical, and ANSI interval literals are one of the things its parser doesn’t take. Date and timestamp arithmetic works on days: subtract 1 and you go back a day.

The fix

Subtract a fraction of a day. There are 1,440 minutes in a day, so thirty minutes is 30/1440 — or 1/48:

SELECT id FROM work_queue
WHERE status = 'PROCESSING'
  AND lastmodified < SYSDATE - (30/1440)

The same works with CURRENT_TIMESTAMP:

AND created > CURRENT_TIMESTAMP - (1.0/48.0)   -- last 30 minutes

A handy table:

Verify it filters, not just that it parses

A query that parses can still be wrong. Check the arithmetic against a record whose time you know:

SELECT id, TO_CHAR(created, 'HH24:MI') AS at,
       CASE WHEN created > CURRENT_TIMESTAMP - (1/48)
            THEN 'within 30 min' ELSE 'older' END AS recency
FROM work_queue
WHERE id = ?

Related: the header location that SuiteQL says is empty, and reading a NetSuite stack trace.