getBlockByNumber view

Column Type Required

blockNumber

String

Yes

transactionDetailsFlag

Boolean

Yes

blobGasUsed

String

No

receiptsRoot

String

No

hash

String

No

baseFeePerGas

String

No

excessBlobGas

String

No

difficulty

String

No

extraData

String

No

gasLimit

String

No

gasUsed

String

No

timestamp

String

No

logsBloom

String

No

miner

String

No

mixHash

String

No

parentBeaconBlockRoot

String

No

nonce

String

No

uncles

Array

No

number

String

No

parentHash

String

No

sha3Uncles

String

No

size

String

No

stateRoot

String

No

transactions

Array

No

withdrawalsRoot

String

No

transactionsRoot

String

No

withdrawals

Array

No

Examples

SELECT * FROM getBlockByNumber WHERE blockNumber = '0x5BAD55' AND transactionDetailsFlag = false
SELECT hash FROM getBlockByNumber WHERE blockNumber = 'finalized' AND transactionDetailsFlag = false


-- Return the number of transactions for a particular block.
SELECT gBBN.blockNumber, gBBN.timestamp, COUNT(gBBN_t.blockNumber) as transaction_count
FROM getBlockByNumber gBBN
LEFT JOIN getBlockByNumber_transactions gBBN_t ON gBBN.blockNumber = gBBN_t.blockNumber AND gBBN.transactionDetailsFlag = gBBN_t.transactionDetailsFlag
WHERE blockNumber = '0x5BAD55' AND transactionDetailsFlag = false

Microsoft SQL Server

-- Return the number of transactions for 5 blocks.
DECLARE @TSQL varchar(8000), @BLOCKNUMBER char(7), @COUNTER INT
SET @COUNTER = 1
SELECT @BLOCKNUMBER = '0x5BAD5'
WHILE ( @COUNTER <= 5)
BEGIN
    --  Replace Ethereum with the name of your linked server.
    SELECT @TSQL = 'SELECT * FROM OPENQUERY(Ethereum,''SELECT gBBN.blockNumber, gBBN.timestamp, COUNT(gBBN_t.blockNumber) as transaction_count FROM getBlockByNumber gBBN LEFT JOIN getBlockByNumber_transactions gBBN_t ON gBBN.blockNumber = gBBN_t.blockNumber AND gBBN.transactionDetailsFlag = gBBN_t.transactionDetailsFlag WHERE transactionDetailsFlag = false AND blockNumber = ''''' + CONCAT(@BLOCKNUMBER, @COUNTER) + ''''''')'
    EXEC (@TSQL)
    SET @COUNTER  = @COUNTER  + 1
END

Microsoft Excel

  1. In Excel, run Developer > Visual Basic to run the Microsoft Visual Basic For Applications editor.

  2. In the Project pane, double-click This WorkBook

  3. In the code pane, add this VBA code:

    Option Explicit

' Return the number of transactions for 5 blocks.
Public Sub GetBlockTransactionCount()

    Dim con             As New ADODB.Connection
    Dim rs              As New ADODB.Recordset
    Dim ws              As Worksheet
    Dim n               As Long
    Dim row             As Long
    Dim col             As Long
    Dim i               As Long
    Dim SQLquery        As String

    ' Replace Sheet 1 with the name of the worksheet where you want the Ethereum data to appear.
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.Cells.Clear

    ' Replace Ethereum with the name of your ODBC data source.
    ' In Tools > References, you need to add a reference to the Microsoft Active X Data Objects <n> Library
    con.Open "DATASTORE}"

    ' Return 5 records
    For n = 0 To 4
        SQLquery = "SELECT gBBN.blockNumber, gBBN.[timestamp], COUNT(gBBN_t.blockNumber) as transaction_count " _
            & "FROM getBlockByNumber gBBN " _
            & "LEFT JOIN getBlockByNumber_transactions gBBN_t ON gBBN.blockNumber = gBBN_t.blockNumber " _
            & "AND gBBN.transactionDetailsFlag = gBBN_t.transactionDetailsFlag WHERE transactionDetailsFlag = false " _
            & "AND blockNumber = '0x5BAD5" & n & "'"
        rs.Open SQLquery, con
        If Not rs.EOF Then

            ' Output column headers
            If n = 0 Then
                row = 1
                col = 1
                For i = 0 To rs.Fields.Count - 1
                    ws.Cells(row, col + i).Value = rs.Fields(i).Name
                Next i
            End If

            ' Output data
            row = 2 + n
            Do While Not rs.EOF
                For i = 0 To rs.Fields.Count - 1
                    ws.Cells(row, col + i).Value = rs.Fields(i).Value
                Next i
                row = row + 1
                rs.MoveNext
            Loop
        End If
        rs.Close
    Next n

    con.Close
    Set con = Nothing
End Sub

  4. Choose Run > Run Sub/UserForm

  5. Optionally, in your Excel worksheet, use this formula to change the UNIX hex timestamps to Excel datetime values:

    =HEX2DEC(RIGHT(B2,(LEN(B2)-2)))/86400 + DATE(1970,1,1)

    Change B2 to the cell containing the first timestamp.

  6. Right-click the cell containing the new formula, and choose Format Cells.

  7. In the Custom category, choose or add a datetime format. For example:

    mm/dd/yyyy hh:mm:ss

Here’s a chart based on the data returned by the Easysoft ODBC-Ethereum Driver:

Scatter chart showing transaction totals and block collation timestamps for five blocks.