| 1 |
<!DOCTYPE html> |
| 2 |
<html><head> |
| 3 |
<meta name="viewport" content="width=device-width, initial-scale=1.0"> |
| 4 |
<meta http-equiv="content-type" content="text/html; charset=UTF-8"> |
| 5 |
<link href="sqlite.css" rel="stylesheet"> |
| 6 |
<title>Floating Point Numbers</title> |
| 7 |
<!-- path= --> |
| 8 |
</head> |
| 9 |
<body> |
| 10 |
<div class=nosearch> |
| 11 |
<a href="index.html"> |
| 12 |
<img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0"> |
| 13 |
</a> |
| 14 |
<div><!-- IE hack to prevent disappearing logo --></div> |
| 15 |
<div class="tagline desktoponly"> |
| 16 |
Small. Fast. Reliable.<br>Choose any three. |
| 17 |
</div> |
| 18 |
<div class="menu mainmenu"> |
| 19 |
<ul> |
| 20 |
<li><a href="index.html">Home</a> |
| 21 |
<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> |
| 22 |
<li class='wideonly'><a href='about.html'>About</a> |
| 23 |
<li class='desktoponly'><a href="docs.html">Documentation</a> |
| 24 |
<li class='desktoponly'><a href="download.html">Download</a> |
| 25 |
<li class='wideonly'><a href='copyright.html'>License</a> |
| 26 |
<li class='desktoponly'><a href="support.html">Support</a> |
| 27 |
<li class='desktoponly'><a href="prosupport.html">Purchase</a> |
| 28 |
<li class='search' id='search_menubutton'> |
| 29 |
<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> |
| 30 |
</ul> |
| 31 |
</div> |
| 32 |
<div class="menu submenu" id="submenu"> |
| 33 |
<ul> |
| 34 |
<li><a href='about.html'>About</a> |
| 35 |
<li><a href='docs.html'>Documentation</a> |
| 36 |
<li><a href='download.html'>Download</a> |
| 37 |
<li><a href='support.html'>Support</a> |
| 38 |
<li><a href='prosupport.html'>Purchase</a> |
| 39 |
</ul> |
| 40 |
</div> |
| 41 |
<div class="searchmenu" id="searchmenu"> |
| 42 |
<form method="GET" action="search"> |
| 43 |
<select name="s" id="searchtype"> |
| 44 |
<option value="d">Search Documentation</option> |
| 45 |
<option value="c">Search Changelog</option> |
| 46 |
</select> |
| 47 |
<input type="text" name="q" id="searchbox" value=""> |
| 48 |
<input type="submit" value="Go"> |
| 49 |
</form> |
| 50 |
</div> |
| 51 |
</div> |
| 52 |
<script> |
| 53 |
function toggle_div(nm) { |
| 54 |
var w = document.getElementById(nm); |
| 55 |
if( w.style.display=="block" ){ |
| 56 |
w.style.display = "none"; |
| 57 |
}else{ |
| 58 |
w.style.display = "block"; |
| 59 |
} |
| 60 |
} |
| 61 |
function toggle_search() { |
| 62 |
var w = document.getElementById("searchmenu"); |
| 63 |
if( w.style.display=="block" ){ |
| 64 |
w.style.display = "none"; |
| 65 |
} else { |
| 66 |
w.style.display = "block"; |
| 67 |
setTimeout(function(){ |
| 68 |
document.getElementById("searchbox").focus() |
| 69 |
}, 30); |
| 70 |
} |
| 71 |
} |
| 72 |
function div_off(nm){document.getElementById(nm).style.display="none";} |
| 73 |
window.onbeforeunload = function(e){div_off("submenu");} |
| 74 |
/* Disable the Search feature if we are not operating from CGI, since */ |
| 75 |
/* Search is accomplished using CGI and will not work without it. */ |
| 76 |
if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ |
| 77 |
document.getElementById("search_menubutton").style.display = "none"; |
| 78 |
} |
| 79 |
/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ |
| 80 |
function hideorshow(btn,obj){ |
| 81 |
var x = document.getElementById(obj); |
| 82 |
var b = document.getElementById(btn); |
| 83 |
if( x.style.display!='none' ){ |
| 84 |
x.style.display = 'none'; |
| 85 |
b.innerHTML='show'; |
| 86 |
}else{ |
| 87 |
x.style.display = ''; |
| 88 |
b.innerHTML='hide'; |
| 89 |
} |
| 90 |
return false; |
| 91 |
} |
| 92 |
var antiRobot = 0; |
| 93 |
function antiRobotGo(){ |
| 94 |
if( antiRobot!=3 ) return; |
| 95 |
antiRobot = 7; |
| 96 |
var j = document.getElementById("mtimelink"); |
| 97 |
if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); |
| 98 |
} |
| 99 |
function antiRobotDefense(){ |
| 100 |
document.body.onmousedown=function(){ |
| 101 |
antiRobot |= 2; |
| 102 |
antiRobotGo(); |
| 103 |
document.body.onmousedown=null; |
| 104 |
} |
| 105 |
document.body.onmousemove=function(){ |
| 106 |
antiRobot |= 2; |
| 107 |
antiRobotGo(); |
| 108 |
document.body.onmousemove=null; |
| 109 |
} |
| 110 |
setTimeout(function(){ |
| 111 |
antiRobot |= 1; |
| 112 |
antiRobotGo(); |
| 113 |
}, 100) |
| 114 |
antiRobotGo(); |
| 115 |
} |
| 116 |
antiRobotDefense(); |
| 117 |
</script> |
| 118 |
<div class=fancy> |
| 119 |
<div class=nosearch> |
| 120 |
<div class="fancy_title"> |
| 121 |
Floating Point Numbers |
| 122 |
</div> |
| 123 |
<details class="fancy_toc"> |
| 124 |
<summary>Table Of Contents</summary> |
| 125 |
<div id="toc_sub"><div class="fancy-toc1"><a href="#how_sqlite_stores_numbers">1. How SQLite Stores Numbers</a></div> |
| 126 |
<div class="fancy-toc2"><a href="#floating_point_accuracy">1.1. Floating-Point Accuracy</a></div> |
| 127 |
<div class="fancy-toc2"><a href="#floating_point_numbers">1.2. Floating Point Numbers</a></div> |
| 128 |
<div class="fancy-toc3"><a href="#unrepresentable_numbers">1.2.1. Unrepresentable numbers</a></div> |
| 129 |
<div class="fancy-toc3"><a href="#is_it_close_enough_">1.2.2. Is it close enough?</a></div> |
| 130 |
<div class="fancy-toc2"><a href="#rounding_floating_point_numbers_for_display">1.3. Rounding Floating-Point Numbers For Display</a></div> |
| 131 |
<div class="fancy-toc3"><a href="#choosing_the_number_of_floating_point_digits_to_display">1.3.1. Choosing The Number Of Floating-Point Digits To Display</a></div> |
| 132 |
<div class="fancy-toc1"><a href="#extensions_for_dealing_with_floating_point_numbers">2. Extensions For Dealing With Floating Point Numbers</a></div> |
| 133 |
<div class="fancy-toc2"><a href="#the_ieee754_c_extension">2.1. The ieee754.c Extension</a></div> |
| 134 |
<div class="fancy-toc3"><a href="#the_ieee754_function">2.1.1. The ieee754() function</a></div> |
| 135 |
<div class="fancy-toc3"><a href="#the_ieee754_mantissa_and_ieee754_exponent_functions">2.1.2. The ieee754_mantissa() and ieee754_exponent() functions</a></div> |
| 136 |
<div class="fancy-toc3"><a href="#the_ieee754_from_blob_and_ieee754_to_blob_functions">2.1.3. The ieee754_from_blob() and ieee754_to_blob() functions</a></div> |
| 137 |
<div class="fancy-toc2"><a href="#the_decimal_c_extension">2.2. The decimal.c Extension</a></div> |
| 138 |
<div class="fancy-toc3"><a href="#the_decimal_add_a_b_decimal_sub_a_b_and_decimal_mul_a_b_functions">2.2.1. The decimal_add(A,B), decimal_sub(A,B), and decimal_mul(A,B) functions</a></div> |
| 139 |
<div class="fancy-toc3"><a href="#the_decimal_pow2_n_function">2.2.2. The decimal_pow2(N) function</a></div> |
| 140 |
<div class="fancy-toc3"><a href="#the_decimal_x_and_decimal_exp_x_functions">2.2.3. The decimal(X) and decimal_exp(X) functions</a></div> |
| 141 |
<div class="fancy-toc3"><a href="#the_decimal_cmp_x_function">2.2.4. The decimal_cmp(X) function</a></div> |
| 142 |
<div class="fancy-toc3"><a href="#the_decimal_sum_x_aggregate_function">2.2.5. The decimal_sum(X) aggregate function</a></div> |
| 143 |
<div class="fancy-toc3"><a href="#the_decimal_collating_sequence">2.2.6. The decimal collating sequence</a></div> |
| 144 |
</div> |
| 145 |
</details> |
| 146 |
</div> |
| 147 |
|
| 148 |
|
| 149 |
|
| 150 |
|
| 151 |
|
| 152 |
|
| 153 |
<h1 id="how_sqlite_stores_numbers"><span>1. </span>How SQLite Stores Numbers</h1> |
| 154 |
|
| 155 |
<p> |
| 156 |
SQLite stores integer values in the 64-bit |
| 157 |
<a href="https://en.wikipedia.org/wiki/Two%27s_complement">twos-complement</a> |
| 158 |
format¹. |
| 159 |
This gives a storage range of -9223372036854775808 to +9223372036854775807, |
| 160 |
inclusive. Integers within this range are exact. |
| 161 |
|
| 162 |
<a name="fpapprox"></a> |
| 163 |
|
| 164 |
</p><p> |
| 165 |
So-called "REAL" or floating point values are stored in the |
| 166 |
<a href="https://en.wikipedia.org/wiki/IEEE_754">IEEE 754</a> |
| 167 |
<a href="https://en.wikipedia.org/wiki/Double-precision_floating-point_format">Binary-64</a> |
| 168 |
format¹. |
| 169 |
This gives a range of positive values between approximately |
| 170 |
1.7976931348623157e+308 and 4.9406564584124654e-324 with an equivalent |
| 171 |
range of negative values. A binary-64 can also be 0.0 (and -0.0), positive |
| 172 |
and negative infinity and "NaN" or "Not-a-Number". Floating point |
| 173 |
values are approximate. |
| 174 |
|
| 175 |
</p><p> |
| 176 |
Pay close attention to the previous sentence: |
| 177 |
</p><blockquote><b> |
| 178 |
Floating point values are approximate. |
| 179 |
</b></blockquote> |
| 180 |
|
| 181 |
<p> |
| 182 |
If you need an exact answer, you should not use binary-64 floating-point |
| 183 |
values, in SQLite or in any other product. This is not an SQLite limitation. |
| 184 |
It is a mathematical limitation inherent in the design of floating-point numbers. |
| 185 |
|
| 186 |
</p><p>—<br>¹ |
| 187 |
Exception: The <a href="rtree.html">R-Tree extension</a> stores information as 32-bit floating |
| 188 |
point or integer values. |
| 189 |
|
| 190 |
</p><h2 id="floating_point_accuracy"><span>1.1. </span>Floating-Point Accuracy</h2> |
| 191 |
|
| 192 |
<p> |
| 193 |
SQLite will preserve as many significant digits of a floating |
| 194 |
point value as it can. But an IEEE 754 double only holds about |
| 195 |
15.95 significant digits. Furthermore, SQLite makes no guarantees that |
| 196 |
the accuracy of computations on floating point values will even be |
| 197 |
this accurate, because no such guarantees are possible. |
| 198 |
Performing math on floating-point values introduces error. |
| 199 |
For example, consider what happens if you attempt to subtract two floating-point |
| 200 |
numbers of similar magnitude: |
| 201 |
|
| 202 |
</p><blockquote> |
| 203 |
<table border="0" cellpadding="0" cellspacing="0"> |
| 204 |
<tr><td align="right">1152693165.1106291898</td></tr> |
| 205 |
<tr><td align="right">-1152693165.1106280772</td></tr> |
| 206 |
<tr><td><hr> |
| 207 |
</td></tr><tr><td align="right">0.0000011126 |
| 208 |
</td></tr></table> |
| 209 |
</blockquote> |
| 210 |
|
| 211 |
<p>The result shown above (0.0000011126) is the correct answer. But if you |
| 212 |
do this computation using binary-64 floating-point, the answer you get is |
| 213 |
0.00000095367431640625 - an error of about 14%. If you do many similar |
| 214 |
computations as part of your program, the errors add up so that your final |
| 215 |
result might be completely meaningless. |
| 216 |
|
| 217 |
</p><p>The error arises because only about 15.95 significant digits of |
| 218 |
each number are stored accurately using the IEEE 754 binary-64 format, and the |
| 219 |
first difference between the two numbers being subtracted is in the 16th digit. |
| 220 |
|
| 221 |
</p><h2 id="floating_point_numbers"><span>1.2. </span>Floating Point Numbers</h2> |
| 222 |
|
| 223 |
<p> |
| 224 |
The binary-64 floating-point format uses 64 bits per number. Hence there |
| 225 |
are 1.845e+19 different possible floating point values. On the other hand |
| 226 |
there are infinitely many real numbers in the range of |
| 227 |
1.7977e+308 and 4.9407e-324. It follows then that binary-64 cannot possibly |
| 228 |
represent all possible real numbers within that range. Approximations are |
| 229 |
required. |
| 230 |
|
| 231 |
</p><p> |
| 232 |
An IEEE 754 floating-point value is an integer multiplied by a power |
| 233 |
of two: |
| 234 |
|
| 235 |
</p><blockquote> |
| 236 |
<big>M × 2<sup><small>E</small></sup></big> |
| 237 |
</blockquote> |
| 238 |
|
| 239 |
<p>The M value is the "mantissa" and E is the "exponent". Both |
| 240 |
M and E are integers. |
| 241 |
|
| 242 |
</p><p>For Binary-64, M is a 53-bit integer and E is an 11-bit integer that is |
| 243 |
offset so that represents a range of values between -1074 and +972, inclusive. |
| 244 |
|
| 245 |
</p><p><i>(NB: The usual description of IEEE 754 is more complex, and it is important |
| 246 |
to understand the added complexity if you really want to appreciate the details, |
| 247 |
merits, and limitations of IEEE 754. However, the integer description shown |
| 248 |
here, while not exactly right, is easier to understand and is sufficient for |
| 249 |
the purposes of this article.)</i></p> |
| 250 |
|
| 251 |
<h3 id="unrepresentable_numbers"><span>1.2.1. </span>Unrepresentable numbers</h3> |
| 252 |
|
| 253 |
<p>Not every decimal number with fewer than 16 significant digits can be |
| 254 |
represented exactly as a binary-64 number. In fact, most decimal numbers |
| 255 |
with digits to the right of the decimal point lack an exact binary-64 |
| 256 |
equivalent. For example, if you have a database column that is intended |
| 257 |
to hold an item price in dollars and cents, the only cents value that |
| 258 |
can be exactly represented are 0.00, 0.25, 0.50, and 0.75. Any other |
| 259 |
numbers to the right of the decimal point result in an approximation. |
| 260 |
If you provide a "price" value of 47.49, that number will be represented |
| 261 |
in binary-64 as: |
| 262 |
|
| 263 |
</p><blockquote> |
| 264 |
6683623321994527 × 2<sup><small>-47</small></sup> |
| 265 |
</blockquote> |
| 266 |
|
| 267 |
<p>Which works out to be: |
| 268 |
|
| 269 |
</p><blockquote> |
| 270 |
47.49000000000000198951966012828052043914794921875 |
| 271 |
</blockquote> |
| 272 |
|
| 273 |
<p>That number is very close to 47.49, but it is not exact. It is a little |
| 274 |
too big. If we reduce M by one to 6683623321994526 so that we have the |
| 275 |
next smaller possible binary-64 value, we get: |
| 276 |
|
| 277 |
</p><blockquote> |
| 278 |
47.4899999999999948840923025272786617279052734375 |
| 279 |
</blockquote> |
| 280 |
|
| 281 |
|
| 282 |
<p> |
| 283 |
This second number is too small. |
| 284 |
The first number is closer to the desired value of 47.49, so that is the |
| 285 |
one that gets used. But it is not exact. Most decimal values work this |
| 286 |
way in IEEE 754. Remember the key point we made above: |
| 287 |
|
| 288 |
</p><blockquote><b> |
| 289 |
Floating point values are approximate. |
| 290 |
</b></blockquote> |
| 291 |
|
| 292 |
<p>If you remember nothing else about floating-point values, |
| 293 |
please don't forget this one key idea. |
| 294 |
|
| 295 |
</p><h3 id="is_it_close_enough_"><span>1.2.2. </span>Is it close enough?</h3> |
| 296 |
|
| 297 |
<p>The precision provided by IEEE 754 Binary-64 is sufficient for most computations. |
| 298 |
For example, if "47.49" represents a price and inflation is running |
| 299 |
at 2% per year, then the price is going up by about 0.0000000301 dollars per |
| 300 |
second. The error in the recorded value of 47.49 represents about 66 nanoseconds |
| 301 |
worth of inflation. So if the 47.49 price is exact |
| 302 |
when you enter it, then the effects of inflation will cause the true value to |
| 303 |
exactly equal the value actually stored |
| 304 |
(47.4900000000000019895196601282805204391479492187) in less than |
| 305 |
one ten-millionth of a second. |
| 306 |
Surely that level of precision is sufficient for most purposes? |
| 307 |
|
| 308 |
<a name="*rounding"></a> |
| 309 |
|
| 310 |
</p><h2 id="rounding_floating_point_numbers_for_display"><span>1.3. </span>Rounding Floating-Point Numbers For Display</h2> |
| 311 |
|
| 312 |
<p> |
| 313 |
When converting a binary-64 number into text for display purposes |
| 314 |
(for example by calling <a href="c3ref/column_blob.html">sqlite3_column_text()</a> or <a href="c3ref/value_blob.html">sqlite3_value_text()</a> |
| 315 |
or by using the <a href="lang_expr.html#castexpr">CAST operator</a> in SQL), the value |
| 316 |
is normally rounded to a reasonable number of significant digits. One |
| 317 |
rarely wants to see all 49 signficant digits of |
| 318 |
6683623321994527×pow(2,-47). The first four digits, "47.49" in this case, |
| 319 |
is usually a better display option. |
| 320 |
|
| 321 |
</p><p> |
| 322 |
There are also performance reasons for rounding. |
| 323 |
6683623321994527×pow(2,-47) can be converted into "47.49" relatively |
| 324 |
quickly, but generating the full 49-digit expansion requires a lot more |
| 325 |
computation. |
| 326 |
|
| 327 |
</p><p>At what point should rounding occur? |
| 328 |
Rounding to 17 significant digits or more results in text that, when coverted |
| 329 |
back into binary-64, gives the exact same value that we started with. |
| 330 |
Rounding to 15 significant digits or less can often gives this exact round-trip result, |
| 331 |
but not always. |
| 332 |
On the other hand, rounding to 15 digits tends to give |
| 333 |
results that are more pleasing to human readers, whereas rounding to 17 |
| 334 |
digits can result in long numbers with a lot of '0's or '9's in the middle. |
| 335 |
|
| 336 |
</p><p>Prior to SQLite 3.52.0 (2026-03-06), if you tried to round |
| 337 |
6683623321994527×pow(2,-47) to 17 digits, SQLite would look at the first |
| 338 |
18 digits ("47.4900000000000019"), see that the last digit was 5 or more |
| 339 |
and hence round up to "47.490000000000002". For that reason, in earlier versions |
| 340 |
of SQLite, rounding occurred at 15 digits. It would look at the 16th digit, |
| 341 |
see that it was zero and hence just truncate, then remove trailing zeros, |
| 342 |
and you are left with "47.49", which is the answer most people want. |
| 343 |
And it turns out that converting text "47.49" back to binary-64 results |
| 344 |
in 6683623321994527×pow(2,-47), so the value does not change if you |
| 345 |
round-trip binary-64→text→binary-64. All is well. |
| 346 |
|
| 347 |
</p><p>But this does not work for every case. |
| 348 |
Consider, for example, the product of 1.23 and 2.34. |
| 349 |
The number 1.23 is: |
| 350 |
|
| 351 |
</p><blockquote> |
| 352 |
2769713770832855 × 2<sup><small>-51</small></sup><br> |
| 353 |
   = 1.229999999999999982236431605997495353221893310546875 |
| 354 |
</blockquote> |
| 355 |
|
| 356 |
<p>And 2.34 is: |
| 357 |
|
| 358 |
</p><blockquote> |
| 359 |
5269211564023480 × 2<sup><small>-51</small></sup><br> |
| 360 |
   = 2.339999999999999857891452847979962825775146484375 |
| 361 |
</blockquote> |
| 362 |
|
| 363 |
<p>If you do an exact multiplication of those two values you get: |
| 364 |
|
| 365 |
</p><blockquote> |
| 366 |
2.878199999999999783639736961049495926597557229698714817531408904915934954260592348873615264892578125 |
| 367 |
</blockquote> |
| 368 |
|
| 369 |
<p>Which cannot be represented in binary-64. The floating point hardware |
| 370 |
in your CPU truncates this to: |
| 371 |
|
| 372 |
</p><blockquote> |
| 373 |
6481130223748880 × 2<sup><small>-51</small></sup><br> |
| 374 |
   = 2.87819999999999964757080306299030780792236328125 |
| 375 |
</blockquote> |
| 376 |
|
| 377 |
<p>If you round that last value to 15 significant digits, you get 2.8782, which is |
| 378 |
the answer most people expect. If you round to 17 digits, you get |
| 379 |
2.8781999999999997 instead. However, 2.8782 and 2.8781999999999997 are not |
| 380 |
the same number in binary-64. |
| 381 |
|
| 382 |
</p><blockquote> |
| 383 |
2.8782 → 6481130223748881 × 2<sup><small>-51</small></sup><br> |
| 384 |
2.8781999999999997 → 6481130223748880 × 2<sup><small>-51</small></sup> |
| 385 |
</blockquote> |
| 386 |
|
| 387 |
<p>The mantissa is different by one. So converting the text "2.8782" back to |
| 388 |
binary-64 will <u>not</u> go back to the original product of 1.23×2.34. |
| 389 |
It will be very close, but not exact. Converting the text "2.8781999999999997" |
| 390 |
back to binary-64 <u>does</u> go back to original value though. And so in |
| 391 |
that sense, 2.8781999999999997 is a more accurate answer. |
| 392 |
|
| 393 |
<a name="*fpdigits"></a> |
| 394 |
|
| 395 |
</p><h3 id="choosing_the_number_of_floating_point_digits_to_display"><span>1.3.1. </span>Choosing The Number Of Floating-Point Digits To Display</h3> |
| 396 |
|
| 397 |
<p>So there is a trade-off. Do you (A) round to 15 digits, and provide answers |
| 398 |
that are closer to what humans expect to see, or do you (B) round to 17 digits and |
| 399 |
provide answers that sometimes have lots '0's or '9's after the decimal point |
| 400 |
but which will round-trip back to the original binary-64? |
| 401 |
|
| 402 |
</p><p>With SQLite 3.51.2 and earlier, solution (A) was the only option. Beginning |
| 403 |
with SQLite 3.52.0 (2026-03-06), solution (B) is available and is the default, |
| 404 |
though applications can choose to do back to solution (A). In C-code, one can |
| 405 |
choose option (A) by running code like this: |
| 406 |
|
| 407 |
</p><blockquote> |
| 408 |
sqlite3_db_config(db, <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigfpdigits">SQLITE_DBCONFIG_FP_DIGITS</a>, 15, 0); |
| 409 |
</blockquote> |
| 410 |
|
| 411 |
<p>Or in the <a href="cli.html">CLI</a>, one can do: |
| 412 |
|
| 413 |
</p><blockquote> |
| 414 |
.dbconfig fp_digits 15 |
| 415 |
</blockquote> |
| 416 |
|
| 417 |
<p>This change occurred because in version 3.52.0, the rounding algorithm |
| 418 |
was enhanced to work something like this: |
| 419 |
|
| 420 |
</p><ol type="a"> |
| 421 |
<li> Round to 15 digits. |
| 422 |
</li><li> Convert the text result back to binary-64. |
| 423 |
</li><li> If the binary-64 value from the previous step is different, then:<br> |
| 424 |
  • Redo step (a) with 17-digit rounding. |
| 425 |
</li></ol> |
| 426 |
|
| 427 |
<p> Rounding to 15 digits is usually enough. |
| 428 |
Step (c) seldom occurs. |
| 429 |
The text renderings with lots of '0's and '9's only appear |
| 430 |
in the unusual cases where they make a difference. The reason this enhancement |
| 431 |
was made in version 3.52.0 was that the binary-64→text→binary-64 conversion |
| 432 |
routines were rewritten to use 64-bit integer arithmetic instead of floating-point |
| 433 |
computations, and so step (b) in the algorithm above became computationally |
| 434 |
reasonable to perform. The binary-64→text conversion in 3.52.0 is still |
| 435 |
faster than before, even with the addition of steps (b) and (c). |
| 436 |
If you do not want the (negligible) overhead of step (b) and (c), just |
| 437 |
set <a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigfpdigits">SQLITE_DBCONFIG_FP_DIGITS</a> to 15. |
| 438 |
|
| 439 |
</p><h1 id="extensions_for_dealing_with_floating_point_numbers"><span>2. </span>Extensions For Dealing With Floating Point Numbers</h1> |
| 440 |
|
| 441 |
<a name="ieee754ext"></a> |
| 442 |
|
| 443 |
<h2 id="the_ieee754_c_extension"><span>2.1. </span>The ieee754.c Extension</h2> |
| 444 |
|
| 445 |
<p>The ieee754 extension converts a floating point number between its |
| 446 |
binary-64 representation and the M×2<sup><small>E</small></sup> format. |
| 447 |
In other words in the expression: |
| 448 |
|
| 449 |
</p><blockquote> |
| 450 |
<big>F = M × 2<sup><small>E</small></sup></big> |
| 451 |
</blockquote> |
| 452 |
|
| 453 |
<p>The ieee754 extension converts between F and (M,E) and back again. |
| 454 |
|
| 455 |
</p><p>The ieee754 extension is not part of the <a href="amalgamation.html">amalgamation</a>, but it is included |
| 456 |
by default in the <a href="cli.html">CLI</a>. If you want to include the ieee754 extension in your |
| 457 |
application, you will need to compile and load it separately. |
| 458 |
|
| 459 |
<a name="ieee754"></a> |
| 460 |
|
| 461 |
</p><h3 id="the_ieee754_function"><span>2.1.1. </span>The ieee754() function</h3> |
| 462 |
|
| 463 |
<p>The ieee754(F) SQL function takes a single floating-point argument |
| 464 |
as its input and returns a string that looks like this: |
| 465 |
|
| 466 |
</p><blockquote> |
| 467 |
'ieee754(M,E)' |
| 468 |
</blockquote> |
| 469 |
|
| 470 |
<p>Except that the M and E are replaced by the mantissa and exponent of the |
| 471 |
floating point number. For example: |
| 472 |
|
| 473 |
</p><div class="codeblock"><pre>sqlite> .mode box |
| 474 |
sqlite> SELECT ieee754(47.49) AS x; |
| 475 |
┌───────────────────────────────┐ |
| 476 |
│ x │ |
| 477 |
├───────────────────────────────┤ |
| 478 |
│ ieee754(6683623321994527,-47) │ |
| 479 |
└───────────────────────────────┘ |
| 480 |
</pre></div> |
| 481 |
|
| 482 |
<p> |
| 483 |
Going in the other direction, the 2-argument version of ieee754() takes |
| 484 |
the M and E values and converts them into the corresponding F value: |
| 485 |
|
| 486 |
</p><div class="codeblock"><pre>sqlite> select ieee754(6683623321994527,-47) as x; |
| 487 |
┌───────┐ |
| 488 |
│ x │ |
| 489 |
├───────┤ |
| 490 |
│ 47.49 │ |
| 491 |
└───────┘ |
| 492 |
</pre></div> |
| 493 |
|
| 494 |
<a name="ieee754m"></a> |
| 495 |
|
| 496 |
<h3 id="the_ieee754_mantissa_and_ieee754_exponent_functions"><span>2.1.2. </span>The ieee754_mantissa() and ieee754_exponent() functions</h3> |
| 497 |
|
| 498 |
<p>The text output of the one-argument form of ieee754() is great for human |
| 499 |
readability, but it is awkward to use as part of a larger expression. Hence |
| 500 |
the ieee754_mantissa() and ieee754_exponent() routines were added to return |
| 501 |
the M and E values corresponding to their single argument F |
| 502 |
value. |
| 503 |
For example: |
| 504 |
|
| 505 |
</p><div class="codeblock"><pre>sqlite> .mode box |
| 506 |
sqlite> SELECT ieee754_mantissa(47.49) AS M, ieee754_exponent(47.49) AS E; |
| 507 |
┌──────────────────┬─────┐ |
| 508 |
│ M │ E │ |
| 509 |
├──────────────────┼─────┤ |
| 510 |
│ 6683623321994527 │ -47 │ |
| 511 |
└──────────────────┴─────┘ |
| 512 |
</pre></div> |
| 513 |
|
| 514 |
<a name="ieee754b"></a> |
| 515 |
|
| 516 |
<h3 id="the_ieee754_from_blob_and_ieee754_to_blob_functions"><span>2.1.3. </span>The ieee754_from_blob() and ieee754_to_blob() functions</h3> |
| 517 |
|
| 518 |
<p>The ieee754_to_blob(F) SQL function converts the floating point number F |
| 519 |
into an 8-byte BLOB that is the big-endian binary-64 encoding of that number. |
| 520 |
The ieee754_from_blob(B) function goes the other way, converting an 8-byte |
| 521 |
blob into the floating-point value that the binary-64 encoding represents. |
| 522 |
|
| 523 |
</p><p>So, for example, if you read |
| 524 |
<a href="https://en.wikipedia.org/wiki/Double-precision_floating-point_format">on |
| 525 |
Wikipedia</a> that the encoding for the minimum positive binary-64 value is |
| 526 |
0x0000000000000001, then you can find the corresponding floating point value |
| 527 |
like this: |
| 528 |
|
| 529 |
</p><div class="codeblock"><pre>sqlite> .mode box |
| 530 |
sqlite> SELECT ieee754_from_blob(x'0000000000000001') AS F; |
| 531 |
┌───────────────────────┐ |
| 532 |
│ F │ |
| 533 |
├───────────────────────┤ |
| 534 |
│ 4.94065645841247e-324 │ |
| 535 |
└───────────────────────┘ |
| 536 |
</pre></div> |
| 537 |
|
| 538 |
<p>Or go the other way: |
| 539 |
|
| 540 |
</p><div class="codeblock"><pre>sqlite> .mode box |
| 541 |
sqlite> SELECT quote(ieee754_to_blob(4.94065645841247e-324)) AS binary-64; |
| 542 |
┌─────────────────────┐ |
| 543 |
│ binary-64 │ |
| 544 |
├─────────────────────┤ |
| 545 |
│ X'0000000000000001' │ |
| 546 |
└─────────────────────┘ |
| 547 |
</pre></div> |
| 548 |
|
| 549 |
<a name="decext"></a> |
| 550 |
|
| 551 |
<h2 id="the_decimal_c_extension"><span>2.2. </span>The decimal.c Extension</h2> |
| 552 |
|
| 553 |
<p>The decimal extension provides arbitrary-precision decimal arithmetic on |
| 554 |
numbers stored as text strings. Because the numbers are stored to arbitrary |
| 555 |
precision and as text, no approximations are needed. Computations can be |
| 556 |
done exactly. |
| 557 |
|
| 558 |
</p><p>The decimal extension is not (currently) part of the SQLite <a href="amalgamation.html">amalgamation</a>. |
| 559 |
However, it is included in the <a href="cli.html">CLI</a>. The source code to this extension |
| 560 |
can be found at |
| 561 |
<a href="https://sqlite.org/src/file/ext/misc/decimal.c?ci=trunk">ext/misc/decimal.c</a>. |
| 562 |
|
| 563 |
</p><p>The decimal extension supports the following SQL functions and collating sequences: |
| 564 |
|
| 565 |
<a name="*decmth"></a> |
| 566 |
|
| 567 |
</p><h3 id="the_decimal_add_a_b_decimal_sub_a_b_and_decimal_mul_a_b_functions"><span>2.2.1. </span>The decimal_add(A,B), decimal_sub(A,B), and decimal_mul(A,B) functions</h3> |
| 568 |
|
| 569 |
<p>These functions compute the sum, difference, and product, respectively, of |
| 570 |
the two inputs A and B and return the result as text. The inputs can be either |
| 571 |
decimal text or numeric values. Numeric inputs are converted into decimal |
| 572 |
text before the computation is performed. |
| 573 |
|
| 574 |
</p><p>There is no "decimal_div(A,B)" function because division does not always |
| 575 |
have a finite-length decimal result. |
| 576 |
|
| 577 |
<a name="decimal_pow2"></a> |
| 578 |
|
| 579 |
</p><h3 id="the_decimal_pow2_n_function"><span>2.2.2. </span>The decimal_pow2(N) function</h3> |
| 580 |
|
| 581 |
<p>The decimal_pow2(N) function computes the exact decimal representation of |
| 582 |
the N-th power of 2. N must be an integer between -20000 and +20000. |
| 583 |
|
| 584 |
</p><p>This function can be slow and can use a lot of memory for large values of N. |
| 585 |
|
| 586 |
<a name="decimal"></a> |
| 587 |
|
| 588 |
</p><h3 id="the_decimal_x_and_decimal_exp_x_functions"><span>2.2.3. </span>The decimal(X) and decimal_exp(X) functions</h3> |
| 589 |
|
| 590 |
<p>The decimal(X) and decimal_exp(X) generate a decimal representation for input X. |
| 591 |
The input X can be an integer or a floating point number, or a text decimal. |
| 592 |
The decimal_exp(X) function returns the result in exponential notation (with a "e+NN" |
| 593 |
at the end) and decimal(X) returns a pure decimal (without the "e+NN"). |
| 594 |
|
| 595 |
</p><p>If the input |
| 596 |
X is a floating point value, it is expanded to its exact decimal equivalent. For |
| 597 |
example: |
| 598 |
|
| 599 |
</p><div class="codeblock"><pre>sqlite> .mode qbox |
| 600 |
sqlite> select decimal(47.49); |
| 601 |
┌──────────────────────────────────────────────────────┐ |
| 602 |
│ decimal(47.49) │ |
| 603 |
├──────────────────────────────────────────────────────┤ |
| 604 |
│ '47.49000000000000198951966012828052043914794921875' │ |
| 605 |
└──────────────────────────────────────────────────────┘ |
| 606 |
</pre></div> |
| 607 |
|
| 608 |
<a name="decimal_cmp"></a> |
| 609 |
|
| 610 |
<h3 id="the_decimal_cmp_x_function"><span>2.2.4. </span>The decimal_cmp(X) function</h3> |
| 611 |
|
| 612 |
<p>The decimal_cmp(A,B) function compares two decimal values A and B. The result will |
| 613 |
be negative, zero, or positive if A is less than, equal to, or greater than B, |
| 614 |
respectively. |
| 615 |
|
| 616 |
<a name="decimal_sum"></a> |
| 617 |
|
| 618 |
</p><h3 id="the_decimal_sum_x_aggregate_function"><span>2.2.5. </span>The decimal_sum(X) aggregate function</h3> |
| 619 |
|
| 620 |
<p>The decimal_sum(X) function is an aggregate, like the built-in |
| 621 |
<a href="lang_aggfunc.html#sumunc">total() aggregate function</a>, except that decimal_sum() computes its result |
| 622 |
to arbitrary precision and is therefore precise. |
| 623 |
|
| 624 |
<a name="*deccseq"></a> |
| 625 |
|
| 626 |
</p><h3 id="the_decimal_collating_sequence"><span>2.2.6. </span>The decimal collating sequence</h3> |
| 627 |
|
| 628 |
<p>The decimal extension provides the "decimal" collating sequence |
| 629 |
that compares decimal text strings in numeric order. |
| 630 |
</p><p align="center"><small><i>This page was last updated on 2026-05-20 11:35:23Z </small></i></p> |
| 631 |
|