MySQL Convert Bytes to Kilobytes, Megabytes, Gigabytes

Question

I have a logs table that stores various file sizes in bytes. I want to be able to query the database and return the smallest possible float which has been converted to MB GB etc. At present I can return the value in MB but how do I continue to divide further to smallest value and append the unit?

SELECT CONCAT( ROUND( SUM( data_transferred ) /1048576, 2 ) ,  ' MB' ) 
FROM  `logs`

Any help would be appreciated.

UPDATE:

Based on the link voodoo417 provided I updated my query to the following, which will output the most relevant file size to two decimal places and append the unit (1000 Bytes, 1 KB, 500 MB, 2 GB, etc):

SET @bytes := (SELECT SUM(data_transferred) FROM wp_ddownload_statistics);

SELECT
    CASE
WHEN ABS(@bytes) < 1024 THEN CONCAT( ROUND( @bytes, 2 ), ' Bytes')
      WHEN ABS(@bytes) < 1048576 THEN CONCAT( ROUND( (@bytes/1024), 2 ), ' KB')
      WHEN ABS(@bytes) < 1073741824 THEN CONCAT( ROUND( (@bytes/1048576), 2 ), ' MB')
      WHEN ABS(@bytes) < 1099511627776 THEN CONCAT( ROUND( (@bytes/1073741824), 2 ), ' GB' )
      WHEN ABS(@bytes) < 1125899906842624 THEN CONCAT( ROUND( (@bytes/1099511627776), 2 ), ' TB')
      WHEN ABS(@bytes) < 1152921504606846976 THEN CONCAT( ROUND( (@bytes/1125899906842624), 2 ), ' PB' )
      WHEN ABS(@bytes) < 1180591620717411303424 THEN CONCAT( ROUND( (@bytes/1152921504606846976) ,2), ' EB' )
      WHEN ABS(@bytes) < 1208925819614629174706176 THEN CONCAT( ROUND( (@bytes/1180591620717411303424), 2), ' ZB' )
      WHEN ABS(@bytes) < 1237940039285380274899124224 THEN CONCAT( ROUND( (@bytes/1208925819614629174706176), 2), ' YB' )
      WHEN ABS(@bytes) < 1267650600228229401496703205376 THEN CONCAT( ROUND( (@bytes/1237940039285380274899124224), 2), ' BB' )
    END

Patency

Can you provide some examples of the results you want? – Overflight

As voodoo417 linked to below but I want to perform this without defining a user function. myitforum.com/cs2/blogs/jnelson/archive/2008/01/11/111351.aspx – Patency

Answer 1

I know this is an old question, but I was looking for the same thing recently, and found out that MySQL 5.7 added format_bytes function exactly for this purpose:

mysql> SELECT format_bytes(512), format_bytes(18446644073709551615);
+-------------------+------------------------------------+
| format_bytes(512) | format_bytes(18446644073709551615) |
+-------------------+------------------------------------+
| 512 bytes         | 16383.91 PiB                       |
+-------------------+------------------------------------+

Footfall

SELECT VERSION(): 5.7.18-0ubuntu0.16.04.1 Kernel error: Error( 1305 ) 42000: "FUNCTION format_bytes does not exist". Yeap, I saw documentation :) – Seiter

github.com/mysql/mysql-sys/blob/master/functions/… – Torn

Is using the sys schema needed? It seems so. – Shorttempered

@AndreaBergonzo Yes indeed, you need to use sys.format_bytes. – Flourishing

Only available from MariaDB 10.6: mariadb.com/kb/en/sys-schema – Hemlock

Answer 2

I have a more elegant solution (also using a user defined function):

CREATE FUNCTION `format_filesize`(filesize FLOAT) RETURNS varchar(20) CHARSET utf8
BEGIN

DECLARE n INT DEFAULT 1;

LOOP
    IF filesize < 1024 THEN
        RETURN concat(round(filesize, 2), ' ', elt(n, 'Bytes', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB', 'BB'));
    END IF;
    SET filesize = filesize / 1024;
    SET n = n + 1;
END LOOP;

END

UPDATE:

Even better, and can be used outside procedures:

SET @filesize = 536870912;
SET @log = IFNULL(TRUNCATE(LOG(1024, @filesize), 0),0);
SELECT CONCAT(ROUND(@filesize / POW(1024, @log), 2), ' ',
            ELT(@log + 1, 'Bytes', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB', 'BB'));

Pianist

Answer 3

Renaat's code is failing when filesize is 0 (obviously you can't do LOG from zero). Therefore @log is filled with null and CONCAT produce null as well. Correct fix is:

SET @filesize = 536870912;
SET @log = IFNULL(TRUNCATE(LOG(1024, @filesize), 0),0);
SELECT CONCAT(ROUND(@filesize / POW(1024, @log), 2), ' ',
            ELT(@log + 1, 'Bytes', 'KB', 'MB', 'GB', 'TB', 'PB', 'EB', 'ZB', 'YB', 'BB'));

Magdau

Answer 4

select concat(round(data*1048576/1073741824,2),' GB')

for example: 1024 = 1gb select concat(round(1024*1048576/1073741824,2),' GB') 1gb

Lobule

please, format your answer, and elaborate how this answers the question. – Heartbreak

Answer 5

The top answer about using format_bytes, despite being answered in 2016, is sadly still not found in MariaDB.

I adapted the query from here to make a function I could use.

FUNCTION `format_bytes`(val float) RETURNS varchar(20) CHARSET latin1
BEGIN
    DECLARE pw smallint;
    IF val < 1024 THEN
        return CONCAT(val, ' B');
    END IF;
    SET pw = LEAST(7, FLOOR(LOG(val) / LOG(1024)));
    RETURN CONCAT(ROUND(val / POW(1024, pw), 2), ' ', SUBSTR('KMGTPEZY', pw, 1), 'B');
END



>>> SELECT format_bytes(512), format_bytes(18446644073709551615);
+-------------------+------------------------------------+
| format_bytes(512) | format_bytes(18446644073709551615) |
+-------------------+------------------------------------+
| 512 B             | 16.00 EB                           |
+-------------------+------------------------------------+

A few things could be tweaked to more closely mimic the MySQL format_bytes, but it's not what I was aiming for.

Dominoes