block_at
Scalar function
Convert a timestamp/date to a block number. Essential for querying blockchain events by date - use with get_logs to fetch transfers, swaps, or any events on a specific day or time range.
Overloads
block_at(TRANSPORT, TIMESTAMP WITH TIME ZONE)
Returns the block number at or before the given timestamp. Use for finding 'what block was mined at midnight on Jan 1st?' or 'get me the block from 24 hours ago'.
Parameters
| Name | Type |
|---|---|
| transport | TRANSPORT |
| timestamp | TIMESTAMP WITH TIME ZONE |
Returns
| Name | Type |
|---|---|
| block_number | BIGINT |
1
-- Find block at specific date
2
SELECT block_at($transport, '2024-01-15'::TIMESTAMPTZ);
3
4
-- Find block from 24 hours ago
5
SELECT block_at($transport, now() - INTERVAL '24 hours');
Notebook ready in readonly mode.
block_at(TRANSPORT, TIMESTAMP WITH TIME ZONE, VARCHAR)
With mode parameter: 'at_or_before' (default) returns last block at or before timestamp, 'at_or_after' returns first block at or after timestamp. Use 'at_or_after' for inclusive start of ranges.
Parameters
| Name | Type |
|---|---|
| transport | TRANSPORT |
| timestamp | TIMESTAMP WITH TIME ZONE |
| mode | VARCHAR |
Returns
| Name | Type |
|---|---|
| block_number | BIGINT |
1
-- First block of 2024 (at_or_after for inclusive start)
2
SELECT block_at($transport, '2024-01-01'::TIMESTAMPTZ, 'at_or_after');
Notebook ready in readonly mode.
block_at(TRANSPORT, TIMESTAMP)
Parameters
| Name | Type |
|---|---|
| arg1 | TRANSPORT |
| arg2 | TIMESTAMP |
Returns
| Name | Type |
|---|---|
| result | BIGINT |
block_at(TRANSPORT, TIMESTAMP, VARCHAR)
Parameters
| Name | Type |
|---|---|
| arg1 | TRANSPORT |
| arg2 | TIMESTAMP |
| arg3 | VARCHAR |
Returns
| Name | Type |
|---|---|
| result | BIGINT |