Functions Reference

Overview

SyndrDB provides built-in scalar functions for string manipulation, math operations, and date/time handling, as well as aggregate functions for summarizing data across documents.

F:UPPER(field_name)    -- Correct
UPPER(field_name)      -- WRONG - will not be recognized

COUNT(*)               -- Correct
F:COUNT(*)             -- WRONG

Table of Contents

• String Functions
• Math Functions
• Date/Time Functions
• Aggregate Functions
• Using Functions in Queries
• Quick Reference Table

String Functions

F:LOWER(value)

Converts a string to lowercase.

SELECT F:LOWER(name) FROM "users"
-- "ALICE" -> "alice"

F:UPPER(value)

Converts a string to uppercase.

SELECT F:UPPER(name) FROM "users"
-- "alice" -> "ALICE"

F:TRIM(value)

Removes leading and trailing whitespace from a string.

SELECT F:TRIM(name) FROM "users"
-- "  alice  " -> "alice"

F:LENGTH(value)

Returns the length (number of characters) of a string.

SELECT F:LENGTH(name) FROM "users"
-- "alice" -> 5

F:SUBSTRING(value, start, length)

Extracts a substring from a string. The start position is 1-based.

SELECT F:SUBSTRING(name, 1, 3) FROM "users"
-- "alice" -> "ali"

SELECT F:SUBSTRING(email, 1, 5) FROM "users"
-- "alice@example.com" -> "alice"

F:CONCAT(value1, value2, ...)

Concatenates two or more strings together.

SELECT F:CONCAT(first_name, " ", last_name) FROM "users"
-- "Alice" + " " + "Smith" -> "Alice Smith"

Math Functions

F:ABS(value)

Returns the absolute value of a number.

SELECT F:ABS(balance) FROM "accounts"
-- -150.50 -> 150.50

F:CEIL(value)

Rounds a number up to the nearest integer.

SELECT F:CEIL(price) FROM "products"
-- 9.2 -> 10

F:FLOOR(value)

Rounds a number down to the nearest integer.

SELECT F:FLOOR(price) FROM "products"
-- 9.8 -> 9

F:ROUND(value)

Rounds a number to the nearest integer.

SELECT F:ROUND(price) FROM "products"
-- 9.5 -> 10, 9.4 -> 9

F:POWER(base, exponent)

Raises a number to a power.

SELECT F:POWER(quantity, 2) FROM "items"
-- 5 -> 25

F:SQRT(value)

Returns the square root of a number.

SELECT F:SQRT(area) FROM "plots"
-- 144 -> 12

F:MOD(dividend, divisor)

Returns the remainder of a division.

SELECT F:MOD(order_number, 10) FROM "orders"
-- 123 -> 3

F:COALESCE(value1, value2, ...)

Returns the first non-NULL value from the argument list.

SELECT F:COALESCE(nickname, first_name, "Unknown") FROM "users"
-- Returns nickname if set, otherwise first_name, otherwise "Unknown"

Date/Time Functions

F:NOW()

Returns the current date and time.

SELECT F:NOW() FROM "users"
-- Returns current timestamp, e.g. "2026-03-07T14:30:00Z"

-- Commonly used in INSERT/UPDATE
ADD DOCUMENT TO BUNDLE "events" WITH ({"created_at" = F:NOW()})

F:EXTRACT(unit, datetime_field)

Extracts a component from a date/time value.

Supported units: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

SELECT F:EXTRACT(YEAR, created_at) FROM "orders"
-- "2026-03-07T14:30:00Z" -> 2026

SELECT F:EXTRACT(MONTH, order_date) FROM "orders"
-- "2026-03-07" -> 3

SELECT * FROM "events" WHERE F:EXTRACT(YEAR, event_date) == 2026

F:DATE_TRUNC(unit, datetime_field)

Truncates a date/time value to the specified precision.

Supported units: YEAR, MONTH, DAY, HOUR, MINUTE

SELECT F:DATE_TRUNC(MONTH, created_at) FROM "orders"
-- "2026-03-15T14:30:00Z" -> "2026-03-01T00:00:00Z"

-- Group by month
SELECT F:DATE_TRUNC(MONTH, order_date), COUNT(*) FROM "orders"
    GROUP BY F:DATE_TRUNC(MONTH, order_date)

F:DATE_ADD(datetime_field, interval, unit)

Adds an interval to a date/time value.

Supported units: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

SELECT F:DATE_ADD(created_at, 30, DAY) FROM "subscriptions"
-- Adds 30 days to created_at

SELECT * FROM "events" WHERE event_date < F:DATE_ADD(F:NOW(), 7, DAY)
-- Events within the next 7 days

F:DATE_SUB(datetime_field, interval, unit)

Subtracts an interval from a date/time value.

SELECT * FROM "logs" WHERE created_at > F:DATE_SUB(F:NOW(), 24, HOUR)
-- Logs from the last 24 hours

SELECT * FROM "orders" WHERE order_date > F:DATE_SUB(F:NOW(), 90, DAY)
-- Orders from the last 90 days

F:AGE(datetime_field)

Returns the interval between a date/time value and the current time.

SELECT name, F:AGE(created_at) FROM "users"
-- Returns the time elapsed since each user was created

Aggregate Functions

Aggregate functions summarize data across multiple documents. They do NOT use the F: prefix.

COUNT(*) / COUNT(field)

Counts documents. COUNT(*) counts all documents; COUNT(field) counts documents where the field is not NULL.

SELECT COUNT(*) FROM "users"
SELECT COUNT(email) FROM "users"
SELECT status, COUNT(*) FROM "orders" GROUP BY status

SUM(field)

Returns the sum of numeric values.

SELECT SUM(total) FROM "orders" WHERE status == "completed"
SELECT category, SUM(revenue) FROM "sales" GROUP BY category

AVG(field)

Returns the average of numeric values.

SELECT AVG(price) FROM "products"
SELECT department, AVG(salary) FROM "employees" GROUP BY department

MIN(field)

Returns the minimum value.

SELECT MIN(price) FROM "products"
SELECT category, MIN(created_at) FROM "articles" GROUP BY category

MAX(field)

Returns the maximum value.

SELECT MAX(price) FROM "products"
SELECT department, MAX(salary) FROM "employees" GROUP BY department

For detailed aggregate usage with GROUP BY, HAVING, and more, see the SELECT Command documentation.

Using Functions in Queries

In SELECT

SELECT F:UPPER(name), F:ROUND(price), F:NOW() FROM "products"

In WHERE

SELECT * FROM "users" WHERE F:LOWER(email) == "alice@example.com"
SELECT * FROM "orders" WHERE F:EXTRACT(YEAR, order_date) == 2026

In ORDER BY

SELECT * FROM "users" ORDER BY F:LOWER(last_name)

In GROUP BY

SELECT F:EXTRACT(MONTH, order_date), COUNT(*) FROM "orders"
    GROUP BY F:EXTRACT(MONTH, order_date)

In INSERT/UPDATE

ADD DOCUMENT TO BUNDLE "events" WITH ({
    "name" = "Meeting",
    "created_at" = F:NOW(),
    "title_upper" = F:UPPER("weekly standup")
})

UPDATE DOCUMENTS IN BUNDLE "users" (updated_at = F:NOW()) CONFIRMED
    WHERE user_id == 42

Quick Reference Table

Last updated: March 2026