Learn how to fetch time series data with The Graph's Time Travel Queries. This explanatory post shows how you can keep your subgraphs simple and make efficient, flexible queries to fetch data over time.

• If you're new to time traveling, see my introductory post.

Time Series Data

We use time series data on Pools.fyi to show liquidity providers' ROI over time. For example, the ROI of pool deposits over the last 30 days:

Time series are a list of results with timestamps and values. We see a lot of techniques applied to fetch results over time using The Graph – the most common is to index summary data on a daily basis. The downsides to this technique are that it is technically complex to implement and inflexible for looking at more granular timeframes (e.g. hourly).

Step 1/3: Create a list of timestamps

The first step is to create a list of timestamps. If you know your start date, end date and the number of periods, you can create your list something like this:

timestamp_start = 1590969600 # Start date as an integer timestamp
delta_per_period = 86400     # Every 24 hours (in secs)
number_of_periods = 30       # For 30 days

timestamps = []
for i in range(number_of_periods):
    timestamps.append(
        int(timestamp_start + i * delta_per_period)
    )

# [1590969600, 1591056000, ...]

Note: your timestamps must be cast as integers.

Step 2/3: Create a list of blocks

The next step is to use your timestamps to create a list of blocks. This involves forming and making a query to our Ethereum Blocks subgraph. Each timestamp is used to form an 'alias' that allows you to query the same field multiple times within the same query - passing in different arguments each time (in this case, the start and end timestamp for each period).

Aliases must start with a letter and so we've chosen to accomplish this by prepending the letter "t" to each timestamp here:

query = "{"

for timestamp in timestamps:
    query += """
      t{0}:blocks(
        first: 1, 
        orderBy: number, 
        orderDirection: asc, 
        where: {
          { 
            timestamp_gte: "{0}", 
            timestamp_lt: "{1}"
          }
        }
      ) {
          {
            number
          }
      }
    """.format(timestamp, timestamp+600)

query += "}"

result = json.loads(blocks_subgraph.execute(query))
blocks = result['data']

# {"t1590969600": [{"number": "10176690"}], ...}

Note: 'blocks_subgraph' is a GraphQL client pointing to this subgraph

Step 3/3: Fetch time series data

The final step is to use this list of blocks to form and make a final time travel query to the subgraph you wish to use. Here is an example looking at the value of liquidity stored in the Curve sUSD pool using our Curve subgraph:

query = "{"

for row in blocks:
    block_number = blocks[row][0]['number']
    query += """
      {0}:exchange(
        id: "0xa5407eae9ba41422680e2e00537571bcc53efbfd"
          block:{
		    { number: {1} }
          }
      ) {
          {
            underlyingBalanceDecimal
          }
      }
    """.format(row, block_number)

query += "}"

result = json.loads(curve_subgraph.execute(query))
results = result['data']

# {
#   "t1590969600": {
#     "totalUnderlyingBalanceDecimal": "6326438.889954332892639262"
#   }, 
#   "t1591056000": {
      "totalUnderlyingBalanceDecimal": "6379766.366595064457980405"
#   }, ...}

Note: You will need to split and cast each alias back to an integer timestamp. For example, t1590969600 -> 1590969600.

Where to go from here

Now you have timestamped data with which to create chart data. This technique takes some extra steps but provides excellent flexibility:

• Adjust your data's granularity on the fly (e.g. instead of 30 periods x 24 hours, you can  fetch data for 60 periods x 1 minute)
• Adjust your starting time (e.g. instead of starting at midnight UTC, use values from exactly 24 hours ago).

Can we help?

The Graph is an enormously powerful tool for indexing Ethereum data. We take pride and enjoy contributing to the growing collection of subgraphs. Please contact us for help with your subgraph.