Converting bytes to kilobytes/megabytes

Question

I have an attachments table that stores the size of the document in Bytes.

I'm needing to display a result set of all documents in either KB or MB. In KB if the document is less than 1MB or in MB if the document is larger than 1MB.

At the moment I have this within my query, but I'm struggling to get the display properly formatted in terms of rounding.

    CASE WHEN D.DocumentSize < 1000000 THEN
        CONCAT(D.DocumentSize / 1024, 'KB')
    ELSE
        CONCAT(D.DocumentSize / 1048576, 'MB')
    END AS DocumentSizeText,

Here are some example values:

87336
1458250
346
8434
8434

346 bytes is going to display 0KB also, so ideally displaying 1KB would be ideal as a minimum.

Rental

You should do formatting in your UI layer, not in the database. – Marvelous

@Marvelous this is useful when you need to extract data for reporting purposes (i.e powerBI) as it will be easier in the SQL than doing the calculations later on. – Lavettelavigne

@HasanPatel No, PowerBI can also format those numbers, probably easier that way too. – Marvelous

Answer 1

Use FORMAT if you want to have a specified significant number of decimal values in MB. N3 here is for showing 3 decimal numbers.

(CASE WHEN D.DocumentSize < 1000000 THEN
           CONCAT(CEILING(D.DocumentSize / 1024.0), 'KB')
      ELSE 
           CONCAT(FORMAT(D.DocumentSize / 1048576.0, 'N3'), 'MB')
 END) AS DocumentSizeText

Pacemaker

Answer 2

How do you want to round the values? If you want to round up, use ceiling():

(CASE WHEN D.DocumentSize < 1000000 THEN
           CONCAT(CEILING(D.DocumentSize / 1024.0), 'KB')
      ELSE CONCAT(CEILING(D.DocumentSize / 1048576.0), 'MB')
 END) AS DocumentSizeText,

Contagium

Thanks Gordon. It displays 2MB though for 1458250 when it should be 1.45MB. 87336 86KB 1458250 2MB 346 1KB 8434 9KB 8434 9KB – Rental

Answer 3

In the original question, it suggested the values should be rounded up when it said

346 bytes is going to display 0KB also, so ideally displaying 1KB would be ideal as a minimum

However clarification comments against an answer suggest that decimal values should be shown, rather than rounding:

It displays 2MB though for 1458250 when it should be 1.45MB

Further, the question said

I'm needing to display a result set ... in KB if the document is less than 1MB or in MB if the document is larger than 1MB

However the suggested answer posed in the question looked at splitting the data on 1000000 bytes, which is really only an approximation of 1 megabyte and all prior answers followed that lead. Funnily enough, when formatting the data, the exact value of a megabyte is used: 1048576.

I'll provide three answers below -

• one that rounds up, as implied in the question
• one that provides decimal values, as implied in the comments
• one that rounds properly (up or down)

I'll extend the answer to show Gigabytes too (even though the question doesn't ask for it - just remove that clause if it is not required, but I think readers may find this extended definition helpful). In both cases I'll use the accurate definitions of megabyte and gigabyte. Full credit goes to the earlier answers which actually solved the initial problem.

Rounding up to nearest value

If dealing with file size, this is being conservative and counting more bytes than is actually being reported.

case 
    when D.DocumentSize < 1048576 then concat(ceiling(D.DocumentSize / 1024.0), ' KB')
    when D.DocumentSize < 1073741824 then concat(ceiling(D.DocumentSize / 1048576.0), ' MB')
    else concat(ceiling(D.DocumentSize / 1073741824.0), ' GB')
end as [DocumentSizeText Rounded Up]

Showing 3 decimal values

Perhaps more accurate, and less readable.

case 
    when D.DocumentSize < 1048576 then concat(format(D.DocumentSize / 1024.0, 'N3'), ' KB')
    when D.DocumentSize < 1073741824 then concat(format(D.DocumentSize / 1048576.0, 'N3'), ' MB')
    else concat(format(D.DocumentSize / 1073741824.0, 'N3'), ' GB')
end as [DocumentSizeText With Decimals]

Rounding properly (up or down)

More accurate than rounding up ... when summing multiple values.

case
    when D.DocumentSize < 1048576 then concat(format(D.DocumentSize / 1024.0, 'N0'), ' KB')
    when D.DocumentSize < 1073741824 then concat(format(D.DocumentSize / 1048576.0, 'N0'), ' MB')
    else concat(format(D.DocumentSize / 1073741824.0, 'N0'), ' GB')
end as [DocumentSizeText Rounded Proper]

Molybdous

Answer 4

Note: this answer deviates from the question, but still illustrates an approach that might be useful for anyone coming to this question.

While I totally agree with the comments that this should usually be done in the BI layer, I did this recently since I just wanted some quick summary stats of my database objects without having to move the data into another tool to do the formatting for me

I prefer to use joins over case statements -- here's an MWE in DuckDB using a CTE to set up the ratio/unit mappings and an as-of join to get them onto the base table:

create or replace table documents (
    id    integer primary key,
    size  bigint not null
);
insert into documents
values
    (1, 100),
    (2, 200_000),
    (3, 300_000_000),
    (4, 400_000_000_000),
    (5, 500_000_000_000_000),
    (6, 600_000_000_000_000_000)
;

with sizes(ratio, unit) as (
    values
        (1024^0, 'B'),
        (1024^1, 'KB'),
        (1024^2, 'MB'),
        (1024^3, 'GB'),
        (1024^4, 'TB'),
        (1024^5, 'PB')
)

select
    documents.id,
    documents.size,
    format(
        '{:.2f} {}',
        documents.size / sizes.ratio,
        sizes.unit
    ) as size_formatted
from documents
    asof left join sizes
        on documents.size > sizes.ratio
order by id

Since as-of joins aren't implemented in most database, this could be ported to other databases by using a lateral join instead:

with sizes(ratio, unit) as (...)

select
    documents.id,
    documents.size,
    format(
        '{:.2f} {}',
        documents.size / ratio_unit.ratio,
        ratio_unit.unit
    ) as size_formatted
from documents
    cross join lateral (
        select ratio, unit
        from sizes
        where documents.size > ratio
        order by ratio desc
        limit 1
    ) as ratio_unit
order by id

In both cases, the output is the same:

id	size	size_formatted
1	100	100.00 B
2	200000	195.31 KB
3	300000000	286.10 MB
4	400000000000	372.53 GB
5	500000000000000	454.75 TB
6	600000000000000000	532.91 PB

Most databases support some concept of lateral -- in SQL Server/T-SQL, it's called APPLY:

• https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver16#using-apply

Exudation

Answer 5

Old thread revival!

You could use pg_size_pretty(...)

https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE

pg_size_pretty ( bigint ) → text

pg_size_pretty ( numeric ) → text

Converts a size in bytes into a more easily human-readable format with size units (bytes, kB, MB, GB, TB, or PB as appropriate). Note that the units are powers of 2 rather than powers of 10, so 1kB is 1024 bytes, 1MB is 10242 = 1048576 bytes, and so on.

SELECT  pg_size_pretty(87336::NUMERIC),
        pg_size_pretty(1458250::NUMERIC),
        pg_size_pretty(346::NUMERIC),
        pg_size_pretty(8434::NUMERIC),
        pg_size_pretty(8434::NUMERIC);

-- Will return:
-- 85 kB,1424 kB,346 bytes,8434 bytes,8434 bytes

Observance

Answer 6

TRIM((SELECT TOP (1) 
         STR (@Bytes/(POWER(CAST(1024 AS BIGINT), o-1)*1.0), 8, 2) + SPACE(1) + p
  FROM   ( VALUES (1, 'B'), (2, 'KB'), (3, 'MB'), (4, 'GB'), (5, 'TB') )_u(o, p)
  WHERE  @Bytes < POWER(CAST(1024 AS BIGINT), o)
  ORDER BY o )) [Size],

Stenotypy