AS OF

AS OF reads what was on the books at the end of an earlier day. "What did we have for November a week ago?" is an AS OF question. Without AS OF, a query reads what stands now.

AS OF <day>

The day can be a date, an offset such as -7d, a date function or today, but not a day after today. Every schema works with AS OF except payments and customers, which keep no history.

November as it stood a week ago

FROM rooms
  SHOW room_nights_sold, room_revenue
  DURING 2026-11
  AS OF -7d
  GROUP BY room_type

Revenue on the books at the end of the 1st

FROM revenue
  SHOW total_revenue
  DURING this_month
  AS OF startOfMonth(0m)

What counts on a day

A booking counts from the day it was made until the day it was cancelled, voided or deleted.

• A stay cancelled later still counts before its cancellation day, with the order line it had then. In bookings, it is a booking before that day and a cancellation from that day on.
• A stay counts as checked in from its check-in day.
• A product, pass, fee or other line added to an order later counts from the day it was added. A fee counts from the day it was charged. An add-on counts from its stay's or booking's booking day.
• In activity_capacity, a seat counts from its booking day until it is cancelled. It counts as checked in from its check-in day, and as a no-show from the day its class ended. A session is sold out from the day its last seat sold.

Everything else is as it stands now. A stay's nights, guests, room type and channel, a line's amount, and rooms, room blocks and event capacity are read as they are today. Occupancy uses today's rooms. A stay cancelled and then reinstated counts as never cancelled, and a check-in undone as never made.

Without a range

With AS OF and no DURING or SINCE, the range is the 12 months from the AS OF day: everything that was on the books ahead of it.

FROM revenue
  SHOW total_revenue
  AS OF startOfMonth(-1m)
  COMPARE TO previous_year
  WITH PERCENT_CHANGE

Pace against last year

With COMPARE TO, each comparison is read as far before its own range as AS OF is before the current range. COMPARE TO stly then sets this year's bookings against last year's at the same lead time, so the comparison hasn't had extra months to fill.

FROM rooms
  SHOW room_nights_sold, room_revenue, occupancy
  DURING next_month
  AS OF today
  GROUP BY room_type
  COMPARE TO stly
  WITH PERCENT_CHANGE

How full this month's classes were a month ago

FROM activity_capacity
  SHOW seats_sold, fill_rate
  DURING this_month
  AS OF -1m
  GROUP BY activity

Errors

A schema that keeps no history can't be read AS OF a day:

FROM customers
  SHOW new_customers
  AS OF 2026-09-01

Nothing is on the books for a day that hasn't ended, so AS OF takes today at the latest:

FROM rooms
  SHOW room_nights_sold
  DURING next_month
  AS OF tomorrow