block_at

Finds the block at a timestamp boundary. Choose at_or_before, at_or_after, or closest to control which neighboring block is returned.

Example

Needs RPC · RPC required

1
-- Last block at or before a specific UTC instant
2
SELECT block_at($client, '2024-01-15 00:00:00+00'::TIMESTAMPTZ) AS block_number;
Notebook ready in readonly mode.

API reference

Exact signatures with descriptions, requirements, inputs, returns, and examples.

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

Accepts a TIMESTAMPTZ value and returns the last block at or before that instant.

Inputs

Name Type Use
client CLIENT

Attached live CLIENT for the chain to search.

 required positional
timestamp TIMESTAMP WITH TIME ZONE

Target instant. TIMESTAMPTZ is interpreted as an absolute time; TIMESTAMP overloads are UTC.

 required positional

Returns

Name Type
block_number BIGINT

Block number at timestamp boundary. BIGINT block number selected by the requested timestamp boundary.

Guidance

Choose a timestamp boundary

The two-argument overload uses at_or_before. Add mode when a range boundary or nearest-block lookup requires different behavior.

TIMESTAMPTZ represents an absolute instant. Plain TIMESTAMP values are interpreted as UTC.

  • Use at_or_after for an inclusive time-range start.
  • Use at_or_before for an inclusive time-range end.
  • closest returns the lower block when two blocks are equally distant.

Additional overloads

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

Accepts a TIMESTAMPTZ value and an explicit at_or_before, at_or_after, or closest boundary mode.

Inputs

Name Type Use
client CLIENT

Attached live CLIENT for the chain to search.

 required positional
timestamp TIMESTAMP WITH TIME ZONE

Target instant. TIMESTAMPTZ is interpreted as an absolute time; TIMESTAMP overloads are UTC.

 required positional
mode VARCHAR

Boundary mode: at_or_before, at_or_after, or closest.

 required positional

Returns

Name Type
block_number BIGINT

Block number at timestamp boundary. BIGINT block number selected by the requested timestamp boundary.

Overload examples

Named parameters · RPC required

1
-- First block of 2024 (at_or_after for inclusive start)
2
SELECT block_at(
3
$client,
4
'2024-01-01 00:00:00+00'::TIMESTAMPTZ,
5
'at_or_after'
6
) AS first_block_of_2024;
block_at(CLIENT, TIMESTAMP) # RPC required

Accepts a plain TIMESTAMP as UTC and returns the block number at or before that instant.

Inputs

Name Type Use
client CLIENT

Attached live CLIENT for the chain to search.

 required positional
timestamp TIMESTAMP

Target instant. TIMESTAMPTZ is interpreted as an absolute time; TIMESTAMP overloads are UTC.

 required positional

Returns

Name Type
block_number BIGINT

Block number at timestamp boundary. BIGINT block number selected by the requested timestamp boundary.

block_at(CLIENT, TIMESTAMP, VARCHAR) # RPC required

Accepts a plain TIMESTAMP as UTC and applies the requested boundary mode.

Inputs

Name Type Use
client CLIENT

Attached live CLIENT for the chain to search.

 required positional
timestamp TIMESTAMP

Target instant. TIMESTAMPTZ is interpreted as an absolute time; TIMESTAMP overloads are UTC.

 required positional
mode VARCHAR

Boundary mode: at_or_before, at_or_after, or closest.

 required positional

Returns

Name Type
block_number BIGINT

Block number at timestamp boundary. BIGINT block number selected by the requested timestamp boundary.

Related functions

Category and tags

Category
Chain reads
Tag
RPC