Suggested
View this page as Markdown

block_at

Resolves a timestamp to a block boundary through an attached live client. at_or_before selects the highest block number whose timestamp is no later than the target; at_or_after selects the lowest block number whose timestamp is no earlier; closest selects the nearer timestamp. After the current tip timestamp, at_or_before and closest return the tip and at_or_after returns the next block number, tip + 1, which is not mined yet.

Scalar functionLive RPC RPC

Example

Live RPC

-- Last block at or before a specific UTC instant
SELECT block_at($client, '2024-01-15 00:00:00+00'::TIMESTAMPTZ) AS block_number;

API reference

block_at(CLIENT, TIMESTAMP WITH TIME ZONE | TIMESTAMP) # RPC required

Return the last block at or before the instant; a plain TIMESTAMP is read as UTC.

Exact signatures (2)
  • block_at(CLIENT, TIMESTAMP WITH TIME ZONE)
  • block_at(CLIENT, TIMESTAMP)

Inputs

Name Type Use
client CLIENT

Attached live CLIENT for the chain to search.

 required positional
timestamp TIMESTAMP WITH TIME ZONE | TIMESTAMP

Target instant, resolved at whole-second precision. TIMESTAMPTZ is an absolute instant; TIMESTAMP is interpreted as UTC.

 required positional

Returns

Name Type
block_number BIGINT

Block number at timestamp boundary. Returns the selected BIGINT block number. A NULL client or timestamp returns NULL. An invalid mode, unusable client, RPC failure, malformed block response, or non-converging search raises an error.

block_at(CLIENT, TIMESTAMP WITH TIME ZONE | TIMESTAMP, VARCHAR) # RPC required

Choose the boundary with mode at_or_before, at_or_after or closest.

Exact signatures (2)
  • block_at(CLIENT, TIMESTAMP WITH TIME ZONE, VARCHAR)
  • block_at(CLIENT, TIMESTAMP, VARCHAR)

Inputs

Name Type Use
client CLIENT

Attached live CLIENT for the chain to search.

 required positional
timestamp TIMESTAMP WITH TIME ZONE | TIMESTAMP

Target instant, resolved at whole-second precision. TIMESTAMPTZ is an absolute instant; TIMESTAMP is interpreted as UTC.

 required positional
mode VARCHAR

One of the exact, case-sensitive values at_or_before, at_or_after, or closest. Omitted or NULL mode uses at_or_before.

 required positional

Returns

Name Type
block_number BIGINT

Block number at timestamp boundary. Returns the selected BIGINT block number. A NULL client or timestamp returns NULL. An invalid mode, unusable client, RPC failure, malformed block response, or non-converging search raises an error.

Overload examples

Live RPC 

-- First block of 2024 using an inclusive start
SELECT block_at(
  $client,
  '2024-01-01 00:00:00+00'::TIMESTAMPTZ,
  'at_or_after'
) AS first_block_of_2024;

Guidance

Timestamp ties

Block timestamps and input timestamps are compared as whole Unix seconds.

When several blocks have the exact target timestamp, at_or_after returns the lowest block number, at_or_before returns the highest, and closest returns the lowest.

When the target is equally distant between two different block timestamps, closest returns the lower block number.

A target after the current tip timestamp returns the tip for at_or_before and closest, and tip + 1 for at_or_after. Before a known genesis timestamp, at_or_before returns -1 and the other modes return block 0.

Reproducible boundaries

Persist the resolved block number with the input timestamp and chain context before using it as a stable boundary in later queries.