# Predicting the block - Boring Sat Night Coding

By [evahteev](https://paragraph.com/@evahteev) · 2023-12-25

---

TLDR
----

Was bored on Christmas Eve Saturday night, so faced the problem of predicting block number for new year date (UTC 1704067200). Sniffed around and found that itself interesting problem to tackle applying linear regression to data we have on blocks in Guru Data Warehouse. Created Public API and Warehouse SDK out of it, so prepaired to that feature (block time/number prediction) to be released in Guru Block Explorers from backend side.

Process
-------

For whatever reason, needed a new year date calculated in blocks on different chains.

    epoch timestamp: 1704067200`
    Timestamp in milliseconds: 1704067200000
    Date and time (GMT): Monday, January 1, 2024 12:00:00 AM
    

### Found that there is no clear UI for that

![](https://storage.googleapis.com/papyrus_images/7f40a1d48285ff5c20d0fa8f6655116002d6876aa22cc3b2abea4e8f0d8846fa.png)

Tried LLAMA API [https://defillama.com/docs/api](https://defillama.com/docs/api) (not working)

![Defi llama API](https://storage.googleapis.com/papyrus_images/50b99b934f835d4903626b72bd10e0df603cc75d67aa208b4d9f29b0a5590a18.png)

Defi llama API

![Tried 1704067200000, got results for 1603964988](https://storage.googleapis.com/papyrus_images/9cf932036e9bdbd16396fa9cae21adc89910922fd2a1de0d32622a7353d6079e.png)

Tried 1704067200000, got results for 1603964988

What a hell, let’s hack some code
---------------------------------

![](https://storage.googleapis.com/papyrus_images/392d5a062701b79a59b03da85f75638146a9a889f3a19875951e5d49ea72e544.png)

![](https://storage.googleapis.com/papyrus_images/744ef2a1d8fd26df01e2ff7d4d170872315f6db522e28afef369ff571953ae75.png)

**Prediction**
--------------

To predict blocks in future we need info on blocks in past on each chain, and we have guru warehouse for that

![](https://storage.googleapis.com/papyrus_images/e5903b46ef0c8729c18df3d85594af7885d26320fa716b12857ef36f8e6c75cf.png)

**GURU AI Assist:**

Asked our assist to help me with getting linear regression for block number vs timestamp out of blocks table in warehouse:

![](https://storage.googleapis.com/papyrus_images/4dd5ea70229fc2a749115efe27727117af4becc5a68b3a9d276f202e8d764fb4.png)

Unfortunately Guru AI Assist had no clue on Clickhouse Linear Regression functionality :( so needed to figure out myself.

Because we are using Clickhouse and we can Utilize [Clickhouse Linear Regression](https://clickhouse.com/docs/en/sql-reference/functions/linear-regression-functions/#simplelinearregression) For number/timestamp predictions and backwards, so created following query:

        SELECT
          simpleLinearRegression(number, timestamp) as lr
        FROM (
            SELECT
              number,
              timestamp
            FROM
              {{ network }}.blocks FINAL
            ORDER BY
              number DESC
            LIMIT {{ training_set_size }}
        ) 
    

### Now set parameter from supported networks and get it as an API:

![](https://storage.googleapis.com/papyrus_images/694163922c1c3fad36d38623ee3bf7ec9b0e61a9ac9b080bf8fed649d771e0bd.png)

Warehouse SDK
-------------

Thinking a little bit on how I'm going to combine those into meaningful answers with methods like:

        def get_block_by_timestamp(self, network: str, timestamp: int) -> Optional[BlockModel]:
            pass
            
            
       def get_block(network: str, block_number: int) -> Optional[BlockModel]:
            pass                      
    

I've decided to create Warehouse SDK under dex-guru and created PR there [https://github.com/dex-guru/dg-warehouse-sdk/pull/1](https://github.com/dex-guru/dg-warehouse-sdk/pull/1)

Main idea here is to query for linear regression in Warehouse if block/timestamp is in the future, and return one if it's in the past:

Example:

        def get_block_by_timestamp(self, network: str,
                                   timestamp: int) -> Optional[BlockModel]:
    
            """
            Method either predicts or returns block by timestamp
            :param network:
            :param timestamp:
            :return:
            """
            timestamp = self._transform_timestamp(timestamp)
            last_indexed_block = self.get_last_indexed_block(network)
            if not last_indexed_block:
                return None
            last_indexed_block = last_indexed_block[0]
            if timestamp > last_indexed_block['timestamp']:
                # 10% OF ALL BLOCKS
                training_set_size = round(last_indexed_block['number'] * 0.1)
                lr_coefficients = self.get_lr_coefficients(network, training_set_size)
                if not lr_coefficients:
                    return None
                lr_coefficients = lr_coefficients[0]['lr']
                block_number = round((timestamp - lr_coefficients["b"]) // lr_coefficients["k"])
                return BlockModel(number=block_number, timestamp=timestamp)
            else:
                block_by_timestamp = self.get_indexed_block_by_timestamp(network,
                                                                         timestamp)
                if not block_by_timestamp:
                    return None
                block_by_timestamp = block_by_timestamp[0]
                return BlockModel(**block_by_timestamp)
    

Embedded it into DexGuru Public API free of charge (no API KEY needed):

![](https://storage.googleapis.com/papyrus_images/abcbe404b6218706169d229e60ae4f493cb24c2e8bcf319fd6b0eab17cfd64be.png)

**Let's figure out block number for 1704067200(New Year) timestamp:**

![](https://storage.googleapis.com/papyrus_images/ee6a62ea7d5dca91c48dcf4b0f033632835f7c05c1ed7da8d16bd6c601e74db0.png)

Cool! Next time would put it into Guru Block Explorer to have Etherscan type of page with time prediction there :)

![](https://storage.googleapis.com/papyrus_images/9533722290d2c1c9b87a1f2c351dae8ab8689862ff324f8a70b16962fc0e3a8b.png)

---

*Originally published on [evahteev](https://paragraph.com/@evahteev/predicting-the-block-boring-sat-night-coding)*