# 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.

Canonical HTML: <https://determica.com/docs/functions/block_at>

- Kind: scalar function
- Category: [Chain reads](/docs/category/chain-reads.md)
- Tags: RPC

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.

## Overload 1: block_at(CLIENT, TIMESTAMP WITH TIME ZONE)

```sql
block_at(CLIENT, TIMESTAMP WITH TIME ZONE)
```

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

- Kind: scalar
- Execution context: Live RPC
- Behavior: Immediate Read
- Execution modes: Live Client
- Input shape: Scalar Arguments
- Planning contract: Runtime
- Cardinality: One Per Input
- Determinism: External State
- Return shape: Scalar
- Schema stability: Fixed
- Stability: Stable
- Side effects: network_io

### Requirements

- attached_live_client
- rpc

### Risks

- provider_limits
- chain_reorganization

### Inputs

- `client` (CLIENT; required; positional) — Attached live CLIENT for the chain to search.
- `timestamp` (TIMESTAMP WITH TIME ZONE; required; positional) — Target instant, resolved at whole-second precision. TIMESTAMPTZ is an absolute instant; TIMESTAMP is interpreted as UTC.

### Returns

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_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.

### Examples

_Named parameters · Needs RPC · RPC required_

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

## Overload 2: block_at(CLIENT, TIMESTAMP WITH TIME ZONE, VARCHAR)

```sql
block_at(CLIENT, TIMESTAMP WITH TIME ZONE, VARCHAR)
```

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

- Kind: scalar
- Execution context: Live RPC
- Behavior: Immediate Read
- Execution modes: Live Client
- Input shape: Scalar Arguments
- Planning contract: Runtime
- Cardinality: One Per Input
- Determinism: External State
- Return shape: Scalar
- Schema stability: Fixed
- Stability: Stable
- Side effects: network_io

### Requirements

- attached_live_client
- rpc

### Risks

- provider_limits
- chain_reorganization

### Inputs

- `client` (CLIENT; required; positional) — Attached live CLIENT for the chain to search.
- `timestamp` (TIMESTAMP WITH TIME ZONE; required; positional) — Target instant, resolved at whole-second precision. TIMESTAMPTZ is an absolute instant; TIMESTAMP is interpreted as UTC.
- `mode` (VARCHAR; required; positional) — One of the exact, case-sensitive values at_or_before, at_or_after, or closest. Omitted or NULL mode uses at_or_before.

### Returns

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_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.

### Examples

_Named parameters · Needs RPC · RPC required_

```sql
-- 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;
```

## Overload 3: block_at(CLIENT, TIMESTAMP)

```sql
block_at(CLIENT, TIMESTAMP)
```

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

- Kind: scalar
- Execution context: Live RPC
- Behavior: Immediate Read
- Execution modes: Live Client
- Input shape: Scalar Arguments
- Planning contract: Runtime
- Cardinality: One Per Input
- Determinism: External State
- Return shape: Scalar
- Schema stability: Fixed
- Stability: Stable
- Side effects: network_io

### Requirements

- attached_live_client
- rpc

### Risks

- provider_limits
- chain_reorganization

### Inputs

- `client` (CLIENT; required; positional) — Attached live CLIENT for the chain to search.
- `timestamp` (TIMESTAMP; required; positional) — Target instant, resolved at whole-second precision. TIMESTAMPTZ is an absolute instant; TIMESTAMP is interpreted as UTC.

### Returns

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_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 4: block_at(CLIENT, TIMESTAMP, VARCHAR)

```sql
block_at(CLIENT, TIMESTAMP, VARCHAR)
```

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

- Kind: scalar
- Execution context: Live RPC
- Behavior: Immediate Read
- Execution modes: Live Client
- Input shape: Scalar Arguments
- Planning contract: Runtime
- Cardinality: One Per Input
- Determinism: External State
- Return shape: Scalar
- Schema stability: Fixed
- Stability: Stable
- Side effects: network_io

### Requirements

- attached_live_client
- rpc

### Risks

- provider_limits
- chain_reorganization

### Inputs

- `client` (CLIENT; required; positional) — Attached live CLIENT for the chain to search.
- `timestamp` (TIMESTAMP; required; positional) — Target instant, resolved at whole-second precision. TIMESTAMPTZ is an absolute instant; TIMESTAMP is interpreted as UTC.
- `mode` (VARCHAR; required; positional) — One of the exact, case-sensitive values at_or_before, at_or_after, or closest. Omitted or NULL mode uses at_or_before.

### Returns

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_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.

## 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.

## Related functions

- [block_range](/docs/functions/block_range.md) — See Also
- [get_logs](/docs/functions/get_logs.md) — Usually Before
- [latest_block](/docs/functions/latest_block.md) — See Also