Working with raw data: Hex-to-Integer Function

Function Name

ethereum_mainnet.converthextobiginteger -> takes in text and outputs the decoded integer in text.

The reason for the output being in text is in case the value is too large to be stored in a bignumeric type.

Availability

Currently, this function is only available for our realtimeDB offering, which is indicated by the change from data-lake to realtime bellow:

Running a New Query: indication for realtimeDB

What is it used for

Hexadecimal values are commonly encountered when working with the raw event logs and input/output data from transactions or traces tables.

These values are similar to what you find on platforms like Etherscan in the 'Input data' section or the 'Logs' section. In many cases, you may need to convert these hexadecimal values into integers to obtain the decoded values.

Figure 2: How a function looks before decoding (source: Etherscan)

The conversion from hexadecimal to integer is useful for various purposes, such as extracting information like token amount from swap events or token IDs from NFT sales. It's important to note that the process of converting hex values to integers is not limited to Ethereum data and can be applied to any EVM (Ethereum Virtual Machine) chain that we support in our real-time database.

Figure 3: How a function looks after decoding (source: Etherscan)

In most cases this function is not needed as we provide decoded data in single-digit second latency to the end-user:

Figure 4: Logs table for a specific event, including decoded logs (source: Zettablock).

But in the case where you are doing something mission-critical and you want to rely directly on the raw data as they come first, this is how you would use this function.

How to use

Example 1

If you wanted to decode a Uniswap v2 swap in real-time, this is how you would get the swap amounts:

select
  transaction_hash,
  block_time,
  block_number,
  log_index,
  event,
  contract_address as pair_address,
  argument_names,
  argument_values,
  ethereum_mainnet.converthextobiginteger(substring(data,1,66)) as amount0In,
  ethereum_mainnet.converthextobiginteger(substring(data,67,64)) as amount1In,
  ethereum_mainnet.converthextobiginteger(substring(data,67+64,64)) as amount0Out,
  ethereum_mainnet.converthextobiginteger(substring(data,67+64+64,64)) as amount1Out
FROM ethereum_mainnet.logs
WHERE data_creation_date = '2023-06-22' -- Use data_creation_date index for faster response time!
AND topics[1] = '0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822'
AND transaction_hash = '0x999071209d75fe16d092a2f848fa7f21f78e68b121b2dc7bbf34cca77236e806'

Example 2

If you wanted to decode a transaction input, how much usdt is sent to mint Compound Finance's USDT token for example you'd do it like this:

select
  hash as transaction_hash,
  block_time,
  block_number,
  func_name,
  argument_names,
  argument_values,
  ethereum_mainnet.converthextobiginteger(substring(input,11,64)) as transfered_usdt
FROM ethereum_mainnet.transactions
WHERE data_creation_date = '2023-06-22'
AND hash = '0x81cb112b52dcfb05b9787405d8f3b4296fc2a989b406d03e793b735a46d6d555'