🔢

SQLite Math Functions

SQLite

Complete reference for SQLite math functions covering sin, cos, sqrt, log, ceil, floor, round, pow, and more. Note that these functions require SQLite to be compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS and are available by default from SQLite 3.35.0 onwards. Updated for SQLite 3.52.

29 functions

Jump to function

acos acosh asin asinh atan atanh atan2 ceil ceiling cos cosh degrees exp floor ln log log10 log2 mod pi pow power radians sin sinh sqrt tan tanh trunc

What are SQLite Math Functions?

SQLite math functions provide trigonometric, logarithmic, rounding, and power operations for numeric calculations. They were added in SQLite 3.35.0 (2021) and require the SQLITE_ENABLE_MATH_FUNCTIONS compile-time option, which is enabled by default in most distributions. For basic numeric operations like round(), abs(), and max/min, no special compilation flag is needed — these are available in all SQLite builds.

acos

SQLite 3.35.0→ REAL

Returns the arccosine of X. The result is in radians. Returns NULL if X is outside the domain [-1.0, 1.0].

Signature

acos(X)

Parameters

Parameter	Type	Description
X	REAL	A numeric value in the range [-1.0, 1.0].

Examples

acos(1) = 0 radians

sql

SELECT acos(1.0);

→0.0

acos(0) = π/2 radians

sql

SELECT acos(0.0);

→1.5707963267948966

acos(-1) = π radians

sql

SELECT acos(-1.0);

→3.141592653589793

Domain error — value outside [-1, 1] returns NULL

sql

SELECT acos(2.0);

→NULL

Convert arccosine result to degrees for display

sql

SELECT degrees(acos(sensor_value)) AS angle_deg FROM readings WHERE sensor_value BETWEEN -1 AND 1;

→(varies)

⚠Anti-Pattern— Passing values outside [-1, 1] and expecting an error

Developers porting code from PostgreSQL expect acos() to raise an error when the argument is outside the valid domain [-1.0, 1.0]. In SQLite, acos(2.0) silently returns NULL instead of throwing. This causes silent data loss rather than a visible failure.

✓ Instead: Guard inputs with a CASE expression or WHERE clause: SELECT CASE WHEN sensor_value BETWEEN -1 AND 1 THEN acos(sensor_value) ELSE NULL END FROM readings;

Unlike some languages that throw exceptions, SQLite math functions silently return NULL for domain errors. Always validate input ranges or use CASE/BETWEEN guards before calling acos() on untrusted data.

◆SQLite Note

Requires the -DSQLITE_ENABLE_MATH_FUNCTIONS compile-time flag. In PostgreSQL these trig functions are built-in with no compile flag needed, and PostgreSQL additionally provides acosd() which accepts degrees directly.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

acosh

SQLite 3.35.0→ REAL

Returns the hyperbolic arccosine of X. The domain is X >= 1.0; values below 1.0 return NULL.

Signature

acosh(X)

Parameters

Parameter	Type	Description
X	REAL	A numeric value >= 1.0.

Examples

acosh(1) = 0

sql

SELECT acosh(1.0);

→0.0

acosh(2)

sql

SELECT acosh(2.0);

→1.3169578969248168

acosh(10)

sql

SELECT acosh(10.0);

→2.993222846126381

Domain error — argument below 1.0 returns NULL

sql

SELECT acosh(0.5);

→NULL

Inverse of cosh over a table column

sql

SELECT acosh(cosh_value) AS original FROM hyperbolic_table WHERE cosh_value >= 1;

→(varies)

⚠Anti-Pattern— Passing values less than 1 and expecting an error

acosh(X) is only defined for X >= 1. In SQLite, acosh(0.5) returns NULL without any warning, which can mask upstream data problems. PostgreSQL would raise an error in the same situation, so developers migrating from PostgreSQL may not anticipate the silent NULL.

✓ Instead: Filter or guard the input before calling: SELECT acosh(x) FROM t WHERE x >= 1.0;

acosh(cosh(X)) == X for X >= 0. Use acosh to recover an angle from a known hyperbolic cosine value, such as in catenary curve calculations.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL has acosh() as a built-in with no compile flag needed.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

asin

SQLite 3.35.0→ REAL

Returns the arcsine of X in radians. The domain is [-1.0, 1.0]; values outside that range return NULL.

Signature

asin(X)

Parameters

Parameter	Type	Description
X	REAL	A numeric value in the range [-1.0, 1.0].

Examples

asin(0) = 0 radians

sql

SELECT asin(0.0);

→0.0

asin(1) = π/2 radians

sql

SELECT asin(1.0);

→1.5707963267948966

asin(-1) = -π/2 radians

sql

SELECT asin(-1.0);

→-1.5707963267948966

Domain error — value outside [-1, 1] returns NULL

sql

SELECT asin(1.5);

→NULL

Calculate angle from opposite/hypotenuse ratio in a triangles table

sql

SELECT degrees(asin(opposite / hypotenuse)) AS angle_deg FROM triangles;

→(varies)

⚠Anti-Pattern— Passing values outside [-1, 1] and expecting an error

Like acos(), asin() silently returns NULL for out-of-domain inputs instead of raising an error. Code that relies on exception handling to detect bad sensor or input data will silently propagate NULLs through subsequent calculations.

✓ Instead: Validate inputs explicitly: SELECT CASE WHEN val BETWEEN -1.0 AND 1.0 THEN asin(val) END AS safe_asin FROM measurements;

asin() always returns a value between -π/2 and π/2 (approximately -1.5708 to 1.5708 radians). Wrap with degrees() to convert to a 0–360 scale for display.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL provides asin() built-in and also asind() (accepts/returns degrees) which has no SQLite equivalent.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

asinh

SQLite 3.35.0→ REAL

Returns the hyperbolic arcsine of X. The domain is all real numbers.

Signature

asinh(X)

Parameters

Parameter	Type	Description
X	REAL	Any numeric value.

Examples

asinh(0) = 0

sql

SELECT asinh(0.0);

→0.0

asinh(1)

sql

SELECT asinh(1.0);

→0.881373587019543

asinh is an odd function: asinh(-x) = -asinh(x)

sql

SELECT asinh(-1.0);

→-0.881373587019543

Works for large values — no domain restriction

sql

SELECT asinh(100.0);

→5.298342365610589

Variance-stabilising transform for right-skewed sensor data

sql

SELECT asinh(flow_rate) AS transformed FROM sensor_data;

→(varies)

⚠Anti-Pattern— Assuming asinh() has the same domain restriction as asin()

Developers sometimes confuse asinh() with asin() and apply an unnecessary BETWEEN -1 AND 1 guard. In reality asinh() is defined for all real numbers. Placing that guard silently discards valid large values outside [-1, 1] without any warning.

✓ Instead: Remove the range guard and call asinh() directly for any real input: SELECT asinh(large_value) FROM t;

asinh() is a smooth alternative to log() for normalising skewed data because it handles zero and negative values gracefully, where log() would return NULL or -Infinity.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL has asinh() built-in with no compile flag needed.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

atan

SQLite 3.35.0→ REAL

Returns the arctangent of X in radians. The result is always in the range (-π/2, π/2). For two-argument arctangent use atan2(Y, X).

Signature

atan(X)

Parameters

Parameter	Type	Description
X	REAL	Any numeric value.

Examples

atan(0) = 0 radians

sql

SELECT atan(0.0);

→0.0

atan(1) = π/4 radians (45°)

sql

SELECT atan(1.0);

→0.7853981633974483

atan(-1) = -π/4 radians (-45°)

sql

SELECT atan(-1.0);

→-0.7853981633974483

Approaches 90° as X → ∞

sql

SELECT degrees(atan(1e10));

→89.99999999427042

Convert a rise/run slope ratio to an angle in degrees

sql

SELECT degrees(atan(CAST(rise AS REAL) / run)) AS slope_angle FROM road_grades WHERE run != 0;

→(varies)

⚠Anti-Pattern— Using atan(Y/X) instead of atan2(Y, X) for quadrant-aware angles

atan(Y/X) loses quadrant information when X is negative because the sign of the division is ambiguous. This causes computed angles to be off by 180 degrees for points in the second and third quadrants, a subtle error easy to miss in test data that only covers positive X values.

✓ Instead: Use atan2(Y, X) whenever you need a full-circle angle: SELECT degrees(atan2(dy, dx)) AS bearing FROM vectors;

atan(Y/X) loses quadrant information and fails when X=0. Use atan2(Y, X) instead to get the correct angle for all quadrants without a division-by-zero risk.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL provides atan() built-in and also atand() / atan2d() degree variants with no SQLite equivalent.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

atanh

SQLite 3.35.0→ REAL

Returns the hyperbolic arctangent of X. The domain is the open interval (-1.0, 1.0); values at or outside ±1.0 return NULL.

Signature

atanh(X)

Parameters

Parameter	Type	Description
X	REAL	A numeric value strictly between -1.0 and 1.0.

Examples

atanh(0) = 0

sql

SELECT atanh(0.0);

→0.0

atanh(0.5)

sql

SELECT atanh(0.5);

→0.5493061443340548

Odd function: atanh(-x) = -atanh(x)

sql

SELECT atanh(-0.5);

→-0.5493061443340548

Domain boundary — 1.0 is not in the open interval, returns NULL

sql

SELECT atanh(1.0);

→NULL

Fisher r-to-z transformation for Pearson correlations

sql

SELECT atanh(correlation) AS fisher_z FROM stats WHERE correlation > -1 AND correlation < 1;

→(varies)

⚠Anti-Pattern— Passing values at the boundary (±1) and expecting a finite result

atanh() is defined only on the open interval (-1, 1). At the exact boundary atanh(1.0) and atanh(-1.0) the mathematical result is ±infinity, so SQLite returns NULL. Code expecting a finite real result — for example, when storing correlations that happen to reach ±1 — will silently receive NULL.

✓ Instead: Filter strictly inside the open interval: SELECT atanh(x) FROM t WHERE x > -1.0 AND x < 1.0;

atanh() is identical to Fisher's r-to-z transformation used in meta-analysis. If you store Pearson correlation coefficients, atanh(r) converts them to a scale where averaging and confidence intervals are valid.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL has atanh() built-in with no compile flag needed.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

atan2

SQLite 3.35.0→ REAL

Returns the arctangent of Y/X in radians, placing the result in the correct quadrant based on the signs of both arguments. The result is in the range (-π, π].

Signature

atan2(Y, X)

Parameters

Parameter	Type	Description
Y	REAL	The y-coordinate (numerator).
X	REAL	The x-coordinate (denominator). May be zero without causing an error.

Examples

45° (π/4) — first quadrant

sql

SELECT atan2(1.0, 1.0);

→0.7853981633974483

135° (3π/4) — second quadrant

sql

SELECT atan2(1.0, -1.0);

→2.356194490192345

180° (π) — negative x-axis

sql

SELECT atan2(0.0, -1.0);

→3.141592653589793

90° (π/2) — no division-by-zero even when X=0

sql

SELECT atan2(1.0, 0.0);

→1.5707963267948966

Approximate compass bearing between two lat/lon points

sql

SELECT degrees(atan2(dest_lat - origin_lat, dest_lon - origin_lon)) AS bearing FROM routes;

→(varies)

⚠Anti-Pattern— Reversing the argument order — passing (X, Y) instead of (Y, X)

atan2 takes (Y, X) — the vertical component first — mirroring the mathematical convention. Passing (X, Y) instead computes the angle of the transposed point, producing results that are wrong by up to 90 degrees. Because both orderings are syntactically valid SQL, no error is raised.

✓ Instead: Always write atan2(y_component, x_component): SELECT degrees(atan2(delta_lat, delta_lon)) AS heading FROM waypoints;

atan2(Y, X) correctly handles all four quadrants and X=0 without any special-casing. It is the standard tool for converting Cartesian (x, y) coordinates to a polar angle.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. Note the argument order: atan2(Y, X) — Y first, X second. This matches C, PostgreSQL, and most languages. SQL Server reverses the operand-and-base pattern found in its LOG(), but atan2 argument order is consistent across platforms.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

ceil

SQLite 3.35.0→ REAL

Returns the smallest integer value greater than or equal to X. For positive X, rounds away from zero; for negative X, rounds toward zero. ceil() and ceiling() are exact aliases.

Signatures

ceil(X)

ceiling(X)

Parameters

Parameter	Type	Description
X	REAL	Any numeric value.

Examples

Rounds up to nearest integer

sql

SELECT ceil(4.2);

→5.0

Negative value rounds toward zero

sql

SELECT ceil(-4.2);

→-4.0

ceiling() is an exact alias for ceil()

sql

SELECT ceiling(4.2);

→5.0

Already an integer — returns unchanged

sql

SELECT ceil(5.0);

→5.0

Round a 10% price increase up to the next whole unit

sql

SELECT item_id, ceil(price * 1.1) AS price_with_tax FROM products;

→(varies)

⚠Anti-Pattern— Expecting INTEGER output from ceil()

In SQLite, ceil() always returns a REAL value even when the input is a whole number. ceil(4.0) returns 5.0, not 5. Code that stores the result in a strict INTEGER column or compares it with an integer using strict equality (= 5) will fail or produce unexpected results.

✓ Instead: Wrap the result with CAST if you need an integer: SELECT CAST(ceil(amount) AS INTEGER) FROM orders;

SQLite's ceil() always returns a REAL (floating-point) value, not an INTEGER. If you need a true integer type for a column or expression, wrap the result: CAST(ceil(x) AS INTEGER).

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. In PostgreSQL, ceil() and ceiling() are both built-in and return the same type as the input (INTEGER input yields INTEGER output). SQLite always returns REAL.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

ceiling

SQLite 3.35.0→ REAL

Alias for ceil(X). Returns the smallest integer value greater than or equal to X. For positive X, rounds away from zero; for negative X, rounds toward zero.

Signatures

ceiling(X)

ceil(X)

Parameters

Parameter	Type	Description
X	REAL	Any numeric value.

Examples

Rounds up to nearest integer

sql

SELECT ceiling(3.1);

→4.0

Negative value rounds toward zero

sql

SELECT ceiling(-3.1);

→-3.0

Exact integer — unchanged

sql

SELECT ceiling(7.0);

→7.0

Rounds toward zero for negative fractions

sql

SELECT ceiling(-0.5);

→0.0

Calculate kilobytes, always rounding up (ceiling division)

sql

SELECT CAST(ceiling(bytes / 1024.0) AS INTEGER) AS kb_used FROM file_sizes;

→(varies)

⚠Anti-Pattern— Expecting INTEGER output from ceiling()

ceiling() is an alias for ceil() and shares the same behavior: it always returns REAL. ceiling(3.0) returns 4.0, not 4. The name sounds like it should round to a whole integer, but the return type is still floating-point in SQLite.

✓ Instead: Use CAST to coerce the result: SELECT CAST(ceiling(score) AS INTEGER) AS rounded_up FROM results;

ceiling() and ceil() are interchangeable in SQLite. The ceiling() spelling exists for SQL standard compatibility (e.g. matching PostgreSQL, MySQL usage). Use whichever is more readable in context.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. Both ceil() and ceiling() are aliases in SQLite. In PostgreSQL both are also available as built-ins with no flag needed.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

cos

SQLite 3.35.0→ REAL

Returns the cosine of X, where X is in radians. The result is in the range [-1.0, 1.0].

Signature

cos(X)

Parameters

Parameter	Type	Description
X	REAL	An angle in radians.

Examples

cos(0) = 1

sql

SELECT cos(0.0);

→1.0

cos(π) = -1

sql

SELECT cos(pi());

→-1.0

cos(π/2) ≈ 0 (floating-point near-zero)

sql

SELECT cos(pi() / 2);

→6.123233995736766e-17

cos(60°) = 0.5 — use radians() to convert degrees

sql

SELECT cos(radians(60.0));

→0.5000000000000001

Extract x-component of unit vectors stored as angles

sql

SELECT x, y, cos(angle_rad) AS x_component FROM vectors;

→(varies)

⚠Anti-Pattern— Passing degree values instead of radians

cos() expects its argument in radians, not degrees. Passing cos(90) computes the cosine of 90 radians (~5156 degrees), not 90 degrees. This is a common mistake when working with compass bearings or angles entered by users in degrees.

✓ Instead: Convert degrees to radians first: SELECT cos(radians(angle_in_degrees)) FROM measurements;

Due to IEEE 754 floating-point representation, cos(π/2) returns a very small number (~6e-17) rather than exactly 0. When comparing trig results, use ABS(result) < 1e-10 instead of = 0.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL has cos() built-in and additionally provides cosd() (argument in degrees), which has no direct SQLite equivalent.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

cosh

SQLite 3.35.0→ REAL

Returns the hyperbolic cosine of X. The result is always >= 1.0.

Signature

cosh(X)

Parameters

Parameter	Type	Description
X	REAL	Any numeric value (angle in radians for the hyperbolic function).

Examples

cosh(0) = 1, the minimum value

sql

SELECT cosh(0.0);

→1.0

cosh(1)

sql

SELECT cosh(1.0);

→1.5430806348152437

cosh is even: cosh(-x) = cosh(x)

sql

SELECT cosh(-1.0);

→1.5430806348152437

Grows rapidly for large |X|

sql

SELECT cosh(10.0);

→11013.232920103323

Catenary equation — cosh models the shape of hanging cables

sql

SELECT cable_span, cosh(cable_span / (2.0 * tension)) AS sag_factor FROM bridge_cables;

→(varies)

⚠Anti-Pattern— Confusing cosh() with cos() and wrapping the argument with radians()

Because cosh() is the hyperbolic cosine it does not operate in angular units at all — its argument is a dimensionless real number, not an angle. Wrapping it with radians() as you would for cos() produces a numerically wrong result without any error.

✓ Instead: Pass the raw dimensionless value directly: SELECT cosh(x) FROM hyperbolic_params;

The hyperbolic cosine describes the shape of a hanging chain or cable (catenary). cosh(x) = (e^x + e^-x) / 2, which grows exponentially; watch for overflow with large arguments.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL has cosh() built-in with no compile flag needed.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

degrees

SQLite 3.35.0→ REAL

Converts the value X from radians to degrees. Equivalent to X * (180 / π).

Signature

degrees(X)

Parameters

Parameter	Type	Description
X	REAL	An angle value in radians.

Examples

0 radians = 0°

sql

SELECT degrees(0.0);

→0.0

π radians = 180°

sql

SELECT degrees(pi());

→180.0

π/2 radians = 90°

sql

SELECT degrees(pi() / 2);

→90.0

2π radians = 360° (full circle)

sql

SELECT degrees(2 * pi());

→360.0

Convert atan2 result to a human-readable bearing in degrees

sql

SELECT degrees(atan2(dest_y - src_y, dest_x - src_x)) AS bearing FROM routes;

→(varies)

⚠Anti-Pattern— Calling degrees() on a value that is already in degrees

degrees() converts radians to degrees. If the value is already in degrees — for example, an angle returned by a user form or stored as degrees in the database — calling degrees() again multiplies it by 180/π, inflating a 90-degree angle to roughly 5157 degrees.

✓ Instead: Only apply degrees() to radian outputs of trig functions: SELECT degrees(acos(dot_product)) AS angle_deg FROM vectors;

All SQLite trig functions (sin, cos, tan, atan, etc.) work in radians. Apply degrees() as the final step when displaying angles to end users. For input, pair with radians() to convert user-supplied degree values.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL has degrees() built-in with no compile flag needed, and additionally offers degree-native trig variants (sind, cosd, atand, etc.) which have no SQLite equivalent.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

exp

SQLite 3.35.0→ REAL

Returns e (Euler's number, approximately 2.71828) raised to the power X. Equivalent to pow(e, X).

Signature

exp(X)

Parameters

Parameter	Type	Description
X	REAL	Any numeric value.

Examples

e^0 = 1

sql

SELECT exp(0.0);

→1.0

e^1 = e (Euler's number)

sql

SELECT exp(1.0);

→2.718281828459045

e^(-1) = 1/e

sql

SELECT exp(-1.0);

→0.36787944117144233

exp and ln are inverse functions

sql

SELECT exp(ln(5.0));

→5.0

Compute geometric mean using exp(sum(ln(x)))

sql

SELECT user_id, exp(SUM(ln(score))) AS geometric_mean FROM scores GROUP BY user_id;

→(varies)

⚠Anti-Pattern— Using exp() for power-of-10 calculations

exp(x) computes e^x (Euler's number raised to x), not 10^x. Using exp(3) to mean '10 to the power 3' returns ~20.09 instead of 1000. This mistake is common when translating formulas that use scientific notation or decibel calculations.

✓ Instead: Use pow(10, x) for powers of ten: SELECT pow(10, exponent) AS scale_factor FROM log_values;

SQLite lacks a built-in geometric mean aggregate. Compute it with exp(avg(ln(x))) — but this requires all values to be positive. For zero-safe geometric mean, use exp(avg(ln(x + 1))) - 1.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL has exp() built-in with no compile flag needed, and PostgreSQL's exp() also accepts NUMERIC type for arbitrary precision; SQLite uses IEEE 754 REAL.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

floor

SQLite 3.35.0→ REAL

Returns the largest integer value less than or equal to X. For positive numbers, rounds toward zero; for negative numbers, rounds away from zero.

Signature

floor(X)

Parameters

Parameter	Type	Description
X	REAL	Any numeric value.

Examples

Rounds down to nearest integer

sql

SELECT floor(4.9);

→4.0

Negative value rounds away from zero (down)

sql

SELECT floor(-4.1);

→-5.0

Already an integer — unchanged

sql

SELECT floor(5.0);

→5.0

Small negative fraction floors to -1

sql

SELECT floor(-0.1);

→-1.0

Truncate prices to whole dollars, cast to INTEGER for storage

sql

SELECT CAST(floor(price) AS INTEGER) AS whole_dollars FROM cart_items;

→(varies)

⚠Anti-Pattern— Expecting INTEGER output from floor()

floor() always returns REAL in SQLite, not INTEGER. floor(4.9) returns 4.0, not 4. Storing the result directly into a strict INTEGER column or using it in integer arithmetic without a cast can cause type mismatch errors or silent coercion.

✓ Instead: Cast the result explicitly: SELECT CAST(floor(price) AS INTEGER) AS floor_price FROM products;

For negative numbers, floor(-4.7) = -5.0 but trunc(-4.7) = -4.0. floor() always rounds toward -∞; trunc() always rounds toward 0. Choose based on whether you want floor division or truncation semantics.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL has floor() built-in and returns the same type as the input; SQLite always returns REAL.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

ln

SQLite 3.35.0→ REAL

Returns the natural (base-e) logarithm of X. Returns NULL if X is <= 0.

Signature

ln(X)

Parameters

Parameter	Type	Description
X	REAL	A positive numeric value.

Examples

ln(1) = 0

sql

SELECT ln(1.0);

→0.0

ln and exp are inverse functions

sql

SELECT ln(exp(1.0));

→1.0

ln(e) = 1

sql

SELECT ln(2.718281828459045);

→1.0

Domain error — ln(0) is -∞, returns NULL

sql

SELECT ln(0.0);

→NULL

Log-transform prices for regression or normalisation

sql

SELECT product_id, ln(price) AS log_price FROM products WHERE price > 0;

→(varies)

⚠Anti-Pattern— Not handling ln(0) or ln(negative) — NULL instead of error

ln(0) and ln() of any negative number return NULL in SQLite because the natural logarithm is undefined for those inputs. PostgreSQL raises an error in the same situation. Code migrated from PostgreSQL that catches exceptions to detect bad data will never trigger, silently propagating NULLs into aggregate calculations.

✓ Instead: Guard inputs explicitly: SELECT ln(x) FROM t WHERE x > 0;

In SQLite (and PostgreSQL), log(X) returns the base-10 logarithm. If you need the natural logarithm — as is common in statistics and machine learning — always use ln(X) explicitly.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. Important naming difference: in SQLite and PostgreSQL, ln() is the natural log and log() is base-10. Many other SQL databases (MySQL, SQL Server) use log() for the natural log — be careful when porting queries.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

log

SQLite 3.35.0→ REAL

One-argument form: returns the base-10 logarithm of X. Two-argument form: returns the base-B logarithm of X. Returns NULL if X <= 0 or B <= 0.

Signatures

log(X)

log(B, X)

Parameters

Parameter	Type	Description
X	REAL	A positive numeric value — the operand.
B	REAL	(Optional) A positive numeric value — the logarithm base. Omit for base-10.

Examples

log base-10 of 100 = 2

sql

SELECT log(100.0);

→2.0

log base-10 of 1000 = 3

sql

SELECT log(1000.0);

→3.0

log base-2 of 8 = 3

sql

SELECT log(2, 8.0);

→3.0

log base-3 of 81 = 4

sql

SELECT log(3, 81.0);

→4.0

Find the order of magnitude of file sizes

sql

SELECT log(10, file_size_bytes) AS order_of_magnitude FROM files WHERE file_size_bytes > 0;

→(varies)

⚠Anti-Pattern— Treating single-argument log(x) as the natural logarithm

In SQLite (matching PostgreSQL behavior), log(x) with one argument is base-10, not the natural logarithm. Developers coming from languages like Python (where math.log is base-e) or C (where log() is natural log) will get wrong results — log(10) returns 1.0, not 2.302585.

✓ Instead: Use ln(x) for the natural logarithm: SELECT ln(population) AS log_e_pop FROM cities;

SQLite (like PostgreSQL and MySQL) uses log(B, X) where the first argument is the base and the second is the operand. SQL Server reverses this order. Always double-check when porting two-argument log() queries across database platforms.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. SQLite's log() matches PostgreSQL: one-argument log(X) is base-10 (same as log10), and log(B,X) takes base first then operand. This differs from MySQL/SQL Server where log() is the natural log. The two-argument form log(B,X) is a SQLite/PostgreSQL extension not in the SQL standard.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

log10

SQLite 3.35.0→ REAL

Returns the base-10 logarithm of X. Equivalent to log(X). Returns NULL if X <= 0.

Signature

log10(X)

Parameters

Parameter	Type	Description
X	REAL	A positive numeric value.

Examples

log10(1) = 0

sql

SELECT log10(1.0);

→0.0

log10(10) = 1

sql

SELECT log10(10.0);

→1.0

log10(1 million) = 6

sql

SELECT log10(1000000.0);

→6.0

log10(0.001) = -3 — works for fractions

sql

SELECT log10(0.001);

→-3.0

Convert amplitude to decibels (dB SPL) using the standard 20·log10 formula

sql

SELECT reading_id, 20 * log10(amplitude / 0.00002) AS spl_db FROM microphone_data WHERE amplitude > 0;

→(varies)

⚠Anti-Pattern— Forgetting the domain guard and silently aggregating NULLs

log10() returns NULL for zero and negative inputs in SQLite without raising an error. Forgetting a WHERE clause when computing SUM(log10(value)) means any non-positive value silently becomes NULL and is excluded from the aggregate, skewing results without any indication something went wrong.

✓ Instead: Filter non-positive values before computing: SELECT SUM(log10(measurement)) FROM data WHERE measurement > 0;

log10(X) and log(X) produce the same result in SQLite. log10() is the unambiguous name — use it when clarity matters or when the code will be read by someone coming from a background where log() means natural log.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. In SQLite and PostgreSQL, log10(X) and log(X) are equivalent (both base-10). In contrast, MySQL's log() is the natural log — always use log10() for portability across SQL engines.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

log2

SQLite 3.35.0→ REAL

Returns the base-2 logarithm of X. Returns NULL if X <= 0.

Signature

log2(X)

Parameters

Parameter	Type	Description
X	REAL	A positive numeric value.

Examples

log2(1) = 0

sql

SELECT log2(1.0);

→0.0

log2(2) = 1

sql

SELECT log2(2.0);

→1.0

log2(1024) = 10 (2^10)

sql

SELECT log2(1024.0);

→10.0

log2(1/4) = -2 — works for fractions

sql

SELECT log2(0.25);

→-2.0

Compute the number of bits needed to represent each integer

sql

SELECT CAST(floor(log2(n)) AS INTEGER) AS bit_length FROM numbers WHERE n > 0;

→(varies)

⚠Anti-Pattern— Using log2() without verifying the math compile flag is enabled

log2() requires SQLite to be compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS. In environments where this flag is absent — older embedded systems, some mobile runtimes — calling log2() raises 'no such function'. Unlike common functions such as abs() or round(), it is not guaranteed to be available.

✓ Instead: Check availability at startup or fall back to log(x) / log(2): SELECT log(val) / log(2) AS log2_val FROM t;

log2() is essential for information theory: the entropy of a probability distribution is -SUM(p * log2(p)). It also gives the number of bits needed to represent N values: CEIL(log2(N)).

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL does not have a dedicated log2() function; the equivalent is log(2, X) or ln(X)/ln(2). SQLite's log2() is a convenient shorthand.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

mod

SQLite 3.35.0→ REAL

Returns the remainder after dividing X by Y. Unlike the % operator, mod() accepts and returns REAL values when either argument is non-integer. Returns NULL if Y is 0.

Signature

mod(X, Y)

Parameters

Parameter	Type	Description
X	REAL	The dividend — any numeric value.
Y	REAL	The divisor — any non-zero numeric value.

Examples

10 mod 3 = 1

sql

SELECT mod(10, 3);

→1.0

Works with floating-point arguments — unlike the % operator

sql

SELECT mod(10.5, 3.2);

→0.8999999999999995

Result sign matches the dividend (same as C fmod)

sql

SELECT mod(-7, 3);

→-1.0

Division by zero returns NULL

sql

SELECT mod(5, 0);

→NULL

Determine even/odd rows for alternating-row styling

sql

SELECT order_id, mod(row_number, 2) AS is_even_row FROM orders;

→(varies)

⚠Anti-Pattern— Expecting INTEGER output when either operand is REAL

When either argument to mod() is a REAL value, the result is also REAL. mod(10.0, 3) returns 1.0, not 1. Code that subsequently uses the result in integer contexts — array indexing, CASE matching against integer literals, strict column types — will fail silently or raise a type error.

✓ Instead: Cast operands or result as needed: SELECT CAST(mod(CAST(amount AS INTEGER), 7) AS INTEGER) FROM schedule;

SQLite's % operator performs integer modulo only. Use mod(X, Y) whenever either argument may be a floating-point number. For example, mod(10.5, 3.2) works correctly, but 10.5 % 3.2 truncates both to integers first.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. Key difference from PostgreSQL: PostgreSQL's % operator and mod() always return the same type as the inputs (INTEGER % INTEGER = INTEGER). SQLite's mod() always returns REAL when either argument is REAL, whereas % truncates both operands to integers before operating.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

pi

SQLite 3.35.0→ REAL

Returns an approximation of π (pi): 3.141592653589793115997963468544185161590576171875. This is the closest IEEE 754 double-precision representation of π.

Signature

pi()

Examples

Returns π as a double-precision float

sql

SELECT pi();

→3.141592653589793

2π (full circle in radians)

sql

SELECT 2 * pi();

→6.283185307179586

Area of a circle: π·r²

sql

SELECT pi() * pow(radius, 2) AS area FROM circles;

→(varies)

Circumference of a circle: 2πr

sql

SELECT 2 * pi() * radius AS circumference FROM circles;

→(varies)

π/6 radians = 30°

sql

SELECT degrees(pi() / 6);

→30.000000000000004

⚠Anti-Pattern— Hardcoding 3.14159 instead of calling pi()

Manually typed approximations of π such as 3.14159 or 3.14159265 introduce rounding errors that compound in multi-step trigonometric calculations. pi() returns the full IEEE 754 double-precision value (15-16 significant digits), so hardcoded constants are less accurate by several decimal places.

✓ Instead: Always use pi() for full precision: SELECT 2 * pi() * radius AS circumference FROM circles;

Always call pi() rather than typing 3.14159... in SQL. It gives you the full double-precision IEEE 754 value and makes the intent clear. Store computed geometry results in REAL columns to avoid silent truncation.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL does not have a pi() function; the equivalent is the constant pi() from the standard or simply the literal. Actually PostgreSQL DOES have pi() as a built-in function with no compile flag required, making this one of the most portable math functions.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

pow

SQLite 3.35.0→ REAL

Returns X raised to the power Y. pow() and power() are exact aliases. Returns NULL if the result is undefined (e.g., 0 raised to a negative power).

Signatures

pow(X, Y)

power(X, Y)

Parameters

Parameter	Type	Description
X	REAL	The base — any numeric value.
Y	REAL	The exponent — any numeric value.

Examples

2^10 = 1024

sql

SELECT pow(2, 10);

→1024.0

9^0.5 = sqrt(9) = 3

sql

SELECT pow(9, 0.5);

→3.0

2^(-3) = 1/8 = 0.125

sql

SELECT pow(2, -3);

→0.125

power() is an exact alias for pow()

sql

SELECT power(10, 6);

→1000000.0

Compound interest formula: P·(1+r)^n

sql

SELECT compound_amount * pow(1 + rate, years) AS future_value FROM investments;

→(varies)

⚠Anti-Pattern— Using pow() for small integer powers when integer precision matters

pow() converts its arguments to floating-point before computing, so pow(2, 53) may not return an exact integer due to floating-point precision limits. For small integer exponents like squaring or cubing, direct multiplication (x * x * x) stays in integer arithmetic and is both exact and faster.

✓ Instead: Prefer multiplication for small integer powers: SELECT revenue * revenue AS revenue_squared FROM sales;

For small integer exponents like x^2 or x^3, repeated multiplication (x*x, x*x*x) is faster and avoids floating-point rounding. Use pow() for fractional exponents, large exponents, or when the exponent is a column value.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. Both pow() and power() are available in SQLite as aliases. PostgreSQL also has both pow() and power() as built-ins with no compile flag needed. PostgreSQL's power() accepts NUMERIC for arbitrary precision; SQLite always uses REAL.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

power

SQLite 3.35.0→ REAL

Alias for pow(X, Y). Returns X raised to the power Y.

Signatures

power(X, Y)

pow(X, Y)

Parameters

Parameter	Type	Description
X	REAL	The base — any numeric value.
Y	REAL	The exponent — any numeric value.

Examples

3^3 = 27

sql

SELECT power(3, 3);

→27.0

2^0.5 = √2

sql

SELECT power(2, 0.5);

→1.4142135623730951

10^(-2) = 0.01

sql

SELECT power(10, -2);

→0.01

Convention: 0^0 = 1 in SQLite

sql

SELECT power(0, 0);

→1.0

Square-root scale population for map circle sizing

sql

SELECT city, power(population, 0.5) AS pop_sqrt FROM cities ORDER BY pop_sqrt DESC;

→(varies)

⚠Anti-Pattern— Assuming power() behaves differently from pow() for integer precision

power() is a direct alias for pow() in SQLite — they share identical implementation, return type (REAL), and precision characteristics. Developers sometimes reach for power() expecting it to handle integer exponents more precisely or to follow SQL-standard semantics; it does not.

✓ Instead: Use either name but be aware both return REAL; cast if integer precision is required: SELECT CAST(power(base, exp) AS INTEGER) FROM integer_exponents WHERE exp >= 0;

power() is the SQL standard spelling (ISO/IEC 9075). Use power() in code that must be readable by DBAs from a PostgreSQL or SQL Server background; use pow() where brevity matters. Both produce identical results in SQLite.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. Both power() and pow() are aliases in SQLite. PostgreSQL also provides both as built-ins. The function is defined in the SQL standard as power(), so this spelling offers the best cross-database portability.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

radians

SQLite 3.35.0→ REAL

Converts the value X from degrees to radians. Equivalent to X * (π / 180).

Signature

radians(X)

Parameters

Parameter	Type	Description
X	REAL	An angle value in degrees.

Examples

0° = 0 radians

sql

SELECT radians(0.0);

→0.0

180° = π radians

sql

SELECT radians(180.0);

→3.141592653589793

90° = π/2 radians

sql

SELECT radians(90.0);

→1.5707963267948966

360° = 2π radians (full circle)

sql

SELECT radians(360.0);

→6.283185307179586

Convert degree-based compass bearing to unit vector components

sql

SELECT sin(radians(bearing)) AS y, cos(radians(bearing)) AS x FROM compass_readings;

→(varies)

⚠Anti-Pattern— Calling radians() on a value that is already in radians

radians() converts degrees to radians. If the value is already in radians — for example, an angle returned by atan2() — wrapping it in radians() multiplies it by π/180 and produces a very small, incorrect number. The error is silent because both inputs and outputs are valid REAL values.

✓ Instead: Only apply radians() to degree values before passing them to trig functions: SELECT sin(radians(bearing_degrees)) FROM navigation;

Angles stored in your database are usually in degrees (human-readable). Wrap them with radians() before passing to sin(), cos(), tan(), etc. Forgetting this is one of the most common bugs in SQL geometry code.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL has radians() built-in with no compile flag needed, and additionally provides degree-native trig variants (sind, cosd, etc.) that bypass the need for explicit conversion.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

sin

SQLite 3.35.0→ REAL

Returns the sine of X, where X is in radians. The result is in the range [-1.0, 1.0].

Signature

sin(X)

Parameters

Parameter	Type	Description
X	REAL	An angle in radians.

Examples

sin(0) = 0

sql

SELECT sin(0.0);

→0.0

sin(π/2) = 1 (maximum)

sql

SELECT sin(pi() / 2);

→1.0

sin(π) ≈ 0 (floating-point near-zero)

sql

SELECT sin(pi());

→1.2246467991473532e-16

sin(30°) ≈ 0.5 — convert degrees with radians()

sql

SELECT sin(radians(30.0));

→0.49999999999999994

Compute the x-displacement of particles in a sine wave simulation

sql

SELECT x_pos + amplitude * sin(radians(phase)) AS new_x FROM wave_particles;

→(varies)

⚠Anti-Pattern— Passing degree values directly instead of radians

sin() expects radians. sin(90) computes the sine of 90 radians, not 90 degrees. The result (~0.894) is close enough to a plausible value that the bug is easy to overlook in tests that only check approximate ranges rather than exact outputs.

✓ Instead: Convert degrees before calling: SELECT sin(radians(angle_deg)) FROM angle_table;

The Pythagorean identity holds in floating-point: pow(sin(x),2) + pow(cos(x),2) will equal 1.0 (or very close to it) for any x. Use this as a sanity check in geometric computations.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL has sin() built-in and also provides sind() which accepts degrees directly, eliminating the need for radians(). No SQLite equivalent for degree-native variants.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

sinh

SQLite 3.35.0→ REAL

Returns the hyperbolic sine of X. Defined as (e^X - e^(-X)) / 2. The domain is all real numbers.

Signature

sinh(X)

Parameters

Parameter	Type	Description
X	REAL	Any numeric value.

Examples

sinh(0) = 0

sql

SELECT sinh(0.0);

→0.0

sinh(1)

sql

SELECT sinh(1.0);

→1.1752011936438014

sinh is odd: sinh(-x) = -sinh(x)

sql

SELECT sinh(-1.0);

→-1.1752011936438014

Grows rapidly for |X| > 3

sql

SELECT sinh(5.0);

→74.20321057778875

Compute velocity components from a parametric hyperbolic trajectory

sql

SELECT point_id, sinh(t) AS velocity_component FROM trajectory_points;

→(varies)

⚠Anti-Pattern— Confusing sinh() with sin() and applying a radians() conversion

sinh() is the hyperbolic sine, not a trigonometric function. Its argument is a dimensionless real number, not an angle. Passing radians(x) to sinh() does not make sense and produces numerically incorrect results without any warning from SQLite.

✓ Instead: Pass the raw value directly without any angle conversion: SELECT sinh(t) AS hyperbolic_sine FROM curve_params;

sinh() grows like e^X / 2 for large X. Arguments above ~710 will overflow IEEE 754 doubles and return Inf or NULL. Check input ranges if arguments can be large.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL has sinh() built-in with no compile flag needed.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

sqrt

SQLite 3.35.0→ REAL

Returns the square root of X. Returns NULL if X is negative.

Signature

sqrt(X)

Parameters

Parameter	Type	Description
X	REAL	A non-negative numeric value.

Examples

sqrt(16) = 4

sql

SELECT sqrt(16.0);

→4.0

sqrt(2) = √2 (irrational)

sql

SELECT sqrt(2.0);

→1.4142135623730951

sqrt(0) = 0

sql

SELECT sqrt(0.0);

→0.0

Negative argument returns NULL (no complex numbers)

sql

SELECT sqrt(-1.0);

→NULL

Euclidean distance between two 2D points using the Pythagorean theorem

sql

SELECT point_id, sqrt(pow(x2-x1,2) + pow(y2-y1,2)) AS distance FROM point_pairs;

→(varies)

⚠Anti-Pattern— Passing a negative value and expecting an error

In SQLite, sqrt(-1) returns NULL rather than raising an error. PostgreSQL raises 'ERROR: cannot take square root of a negative number'. Code that relies on error handling to catch invalid inputs will silently receive NULL and propagate it through subsequent calculations.

✓ Instead: Validate inputs before calling: SELECT sqrt(CASE WHEN value >= 0 THEN value ELSE NULL END) FROM measurements;

sqrt(X) and pow(X, 0.5) produce the same result, but sqrt() is faster and makes intent clearer. For Euclidean distance in 3D, use sqrt(pow(dx,2)+pow(dy,2)+pow(dz,2)).

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL has sqrt() built-in with no compile flag needed, and PostgreSQL's sqrt() raises an error for negative input rather than returning NULL — handle this differently when porting queries.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

tan

SQLite 3.35.0→ REAL

Returns the tangent of X, where X is in radians. Returns a very large number (not NULL) near the asymptotes at π/2 + nπ.

Signature

tan(X)

Parameters

Parameter	Type	Description
X	REAL	An angle in radians.

Examples

tan(0) = 0

sql

SELECT tan(0.0);

→0.0

tan(π/4) ≈ 1 (tan(45°) = 1)

sql

SELECT tan(pi() / 4);

→0.9999999999999999

Same result using radians() conversion

sql

SELECT tan(radians(45.0));

→0.9999999999999999

Near asymptote — returns a very large number, not NULL

sql

SELECT tan(pi() / 2);

→1.633123935319537e+16

Round-trip through tan to normalize angle arithmetic

sql

SELECT degrees(atan(tan(radians(heading)))) AS normalized FROM vehicle_data;

→(varies)

⚠Anti-Pattern— Passing degree values instead of radians

tan() expects its argument in radians. tan(45) computes the tangent of 45 radians (~1.6197), not tan(45°) which equals 1.0. The mistake is especially easy to make when computing slopes or angles that users enter or store in degrees.

✓ Instead: Convert to radians first: SELECT tan(radians(angle_deg)) AS slope FROM geometry;

Unlike domain errors (which return NULL), tan(π/2) returns a very large number due to floating-point imprecision — the argument never exactly equals π/2. Guard with ABS(X - pi()/2) > 1e-9 if you need to detect near-asymptote values.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL has tan() built-in and also provides tand() which accepts degrees. No degree-native equivalent in SQLite.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

tanh

SQLite 3.35.0→ REAL

Returns the hyperbolic tangent of X. The result is always in the open interval (-1.0, 1.0).

Signature

tanh(X)

Parameters

Parameter	Type	Description
X	REAL	Any numeric value.

Examples

tanh(0) = 0

sql

SELECT tanh(0.0);

→0.0

tanh(1)

sql

SELECT tanh(1.0);

→0.7615941559557649

tanh is odd: tanh(-x) = -tanh(x)

sql

SELECT tanh(-1.0);

→-0.7615941559557649

Saturates to 1.0 for large positive arguments

sql

SELECT tanh(100.0);

→1.0

tanh activation function commonly used in neural network layers

sql

SELECT neuron_id, tanh(weighted_sum) AS activation FROM neural_layer;

→(varies)

⚠Anti-Pattern— Using tanh() output directly as a probability without remapping the range

tanh() returns values in the open interval (-1, 1), not (0, 1), so its output is not a valid probability. Developers using it as a sigmoid substitute to normalize scores into a probability-like range will get negative values for negative inputs, which breaks downstream probability calculations.

✓ Instead: Use (1 + tanh(x)) / 2 to remap the output to (0, 1): SELECT (1.0 + tanh(score)) / 2.0 AS probability FROM model_scores;

tanh(X) maps all real numbers to (-1, 1) smoothly, making it a popular activation function in neural networks and a smooth step function in simulations. It is related to the logistic sigmoid by: tanh(x) = 2*sigmoid(2x) - 1.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL has tanh() built-in with no compile flag needed.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

trunc

SQLite 3.35.0→ REAL

Returns the integer part of X by rounding toward zero — equivalent to removing the fractional part. For positive X, same as floor(X). For negative X, same as ceil(X).

Signature

trunc(X)

Parameters

Parameter	Type	Description
X	REAL	Any numeric value.

Examples

Positive value — rounds toward zero (same as floor)

sql

SELECT trunc(4.9);

→4.0

Negative value — rounds toward zero (same as ceil)

sql

SELECT trunc(-4.9);

→-4.0

Fractional part stripped, leaves 0

sql

SELECT trunc(0.999);

→0.0

Small negative fraction rounds to 0

sql

SELECT trunc(-0.1);

→0.0

Separate price into integer part and fractional cents

sql

SELECT product_id, trunc(price) AS integer_part, price - trunc(price) AS cents FROM products;

→(varies)

⚠Anti-Pattern— Expecting trunc() to accept a decimal-places argument like PostgreSQL

In PostgreSQL, trunc(x, d) truncates to d decimal places. In SQLite, trunc() accepts only one argument — the value to truncate toward zero. Calling trunc(3.14159, 2) in SQLite raises 'wrong number of arguments', which can break SQL ported from PostgreSQL.

✓ Instead: Simulate decimal truncation manually: SELECT CAST(value * 100 AS INTEGER) / 100.0 AS trunc_2dp FROM measurements;

trunc() is the only rounding function that behaves symmetrically for positive and negative numbers — it always discards the fractional part toward zero. Use it when you want integer truncation without caring about the sign, e.g. extracting hours from a duration in seconds.

◆SQLite Note

Requires -DSQLITE_ENABLE_MATH_FUNCTIONS. PostgreSQL has trunc() built-in with no compile flag needed, and PostgreSQL's trunc() also accepts a second argument for decimal places (e.g. trunc(3.14159, 2) = 3.14). SQLite's trunc() only accepts one argument.

⚙ Availability: Requires SQLite compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS (standard in most distributions since 3.35.0)

Related SQLite Categories

Core Functions →Aggregate Functions →