​
Hourly Token Prices

common.prices.hourly provides hourly price data by token address across various Blockchains combined with token prices computed from DEX trades.

We preferentially select non-DEX data. That is, if for the same hour, there is a same chain + address that has DEX token prices and externally ingested prices, we will select the externally ingested price.

This table is unique by timestamp (hour), chain and address.

​
Sample Query

Using common.prices.hourly to investigate the USD volume of Flash loans performed on Balancer in the last 7 days

select

    date(block_timestamp) as date,

    params['token']::varchar as token,

    hourly.symbol as token_symbol,

    sum(params ['amount'] / pow(10, hourly.decimals) * hourly.price) as usd_volume

from

    ethereum.decoded.logs

    left join common.prices.hourly on date_trunc('hour', block_timestamp) = hourly.timestamp

    and hourly.address = token

    and chain = 'ethereum'

where logs.block_timestamp >= current_timestamp - interval '7 days'

    and logs.address = '0xba12222222228d8ba445958a75a0704d566bf2c8'

    and logs.topic0 = '0x0d7d75e01ab95780d3cd1c8ec0dd6c2ce19e3a20427eec8bf53283b6fb8e95f0'

group by all

To get the hourly USD price of ETH, filter by chain = ‘ethereum’ and is_native = true:

select *

from common.prices.hourly

where chain = 'ethereum' and is_native = true

​
Table Columns

Unique key: timestamp, chain, address

ColumnDescriptionExample
timestampHourly timestamp.2020-12-03 09:00:00
chainBlockchain (in lowercase).ethereum
addressToken contract address equivalent on the blockchain.
For native tokens on EVM chains, they will be represented by the zero address.		0x0000000000000000000000000000000000000000
symbolToken symbol in lower casing.eth
priceUSD Value of the token at the hour.606.345
decimalsToken decimals (if present).18
is_nativeBoolean flag indicating whether the token is a native (gas) token.TRUE
is_dex_onlyBoolean flag indicating whether the price source was solely computed from DEX trades.FALSE
_created_atTimestamp of entry creation.2024-07-02 04:12:42
_updated_atTimestamp of entry update.2024-07-02 04:12:42