Ethereum Blocks Subgraph: for Time Travelers

by Chris Piatt

The Graph released a new feature called time-travel queries. Here's how it works and how we simplified things with a new subgraph.

Time-Travel Queries

Time-travel queries were introduced in Graph Node v0.17. These queries return the historical state of an entity for any given block.

Follow along with our Bancor subgraph while we look at snapshots of top ETHBNT holders.

Query #1 - Before the airdrop

We query user smart token balances before the airdrop. We can see in the result, there are no token holders.

{
  userSmartTokenBalances(
    first: 3, 
    skip: 0,
    orderBy: balance, 
    orderDirection: desc, 
    block:{number: 9194249}
    where: {
      smartToken: "0xb1cd6e4153b2a390cf00a6556b0fc1458c4a5533"}
  ) {
    user {
      id
    }
    balance
  }
}

Result

{
  "data": {
    "userSmartTokenBalances": []
  }
}

Query #2 - During the airdrop (1,000 blocks later)

Next, we query user smart token balances during the airdrop. We can see in the result, there are token holders.

{
  userSmartTokenBalances(
    first: 3, 
    skip: 0,
    orderBy: balance, 
    orderDirection: desc, 
    block:{number: 9195249}
    where: {
      smartToken: "0xb1cd6e4153b2a390cf00a6556b0fc1458c4a5533"}
  ) {
    user {
      id
    }
    balance
  }
}

Result

{
  "data": {
    "userSmartTokenBalances": [
      {
        "balance": "6914864156349761588543730",
        "user": {
          "id": "0x9d0357d184122b85dbc095d196b5ebbafc7f3010"
        }
      },
      {
        "balance": "199407982881404563228",
        "user": {
          "id": "0x293167a9e884b71bea59d17bdda6a42a99d3255b"
        }
      },
      {
        "balance": "50976844189805257087",
        "user": {
          "id": "0xabe648928a8d5b1d943d5d237de71bb2a0c2794c"
        }
      }
    ]
  }
}

Time-travel queries for time-based metrics

Time-travel queries are great for comparing statistics across time frames. See how they can be used on the Bancor subgraph to calculate 24-hour trading volumes.

One limitation of time-travel queries is that they do not accept block timestamps as an argument. You have to convert a timestamp into a block number to time travel.

You could estimate ~1 block per ~15 seconds and convert your timestamp, but block times tend to vary. How do you turn a timestamp into a block number?

Ethereum Blocks Subgraph

Turn a timestamp into a block number using the Ethereum Blocks subgraph, available here.

Sample Query

Our sample query looks for the first block after a given timestamp (Jan 1, 2020 00:00 UTC).

{
  blocks(first: 1, orderBy: timestamp, orderDirection: asc, where: {timestamp_gt: "1577836800"}) {
    id
    number
    timestamp
  }
}

Result

The result shows that the first block after Jan 1, 2010 00:00 UTC is 9193266.

{
  "data": {
    "blocks": [
      {
        "id": "0xfa39cb98792d28b79138fc0a2ab7c08e59c00b43bd0dcd0b7e4bd49684f7216c",
        "number": "9193266",
        "timestamp": "1577836811"
      }
    ]
  }
}

Use this resulting block number in your time-travel queries to specify exactly the right point in time for your needs.

Speed boost

Updated February 3, 2020

David from the Graph (@lutterkort) offers a technique to speed up our sample query. He wrote:

The 'First block of 2020' query will be a ton faster if you write it as 'First block of the first 10 minutes of 2020' (execution time goes from ~ 2s to ~3ms) The new query looks like
{
  blocks(first: 1, orderBy: timestamp, orderDirection: asc,
         where: {timestamp_gt: "1577836800", timestamp_lt:"1577837400"}) {
    id
    number
    timestamp
  }
}

Thanks!