Converting Bytes to Human-Readable Format in DuckDB

Bytes, the fundamental unit of digital information, often need to be presented in a more comprehensible format for users. DuckDB, an analytical database, offers a native function format_bytes() to transform bytes into a human-readable representation, such as KB, MB, or GB. For instance, SELECT format_bytes(16384) outputs 16KB.

However, there are instances where using the binary base 1024 (KiB, MiB, GiB) becomes necessary, which isn’t directly supported by DuckDB. Let’s explore a custom method to achieve this enhanced functionality.

Macro for 1024-Based Bytes Conversion

To bridge this gap, we can create a custom macro format_bytes_1024() in DuckDB. This macro offers the capability to convert bytes to human-readable sizes using the base 1024 system.

CREATE OR REPLACE MACRO format_bytes_1024(bytes) AS (
    SELECT
        CASE WHEN bytes < 1024 THEN bytes || 'B'
            ELSE
                round((bytes / power(1024, floor(log(bytes) / log(1024)))), 2) ||
                ' ' ||
                substr('BKMGTPEZY', (floor(log(bytes) / log(1024) + 1))::INT, 1) ||
                'iB'
        END AS format_bytes_1024
);

SELECT 
    format_bytes(162800469938172) AS format_bytes,
    format_bytes_1024(162800469938172) AS format_bytes_1024

With DuckDB’s flexibility, we’ve enhanced byte conversion capabilities, addressing scenarios where conversions based on powers of 1024 are necessary.

Stay tuned for more informative recipes in the Ducklicious DuckDB Cookbook.