Functions - YugabyteDB

Overview

YCQL provides a comprehensive set of built-in functions for data manipulation, type conversion, UUID generation, and timestamp operations.

Aggregate Functions

Aggregate functions operate on a set of rows and return a single value.

COUNT

Counts the number of rows or non-null values. Syntax:

COUNT(*)
COUNT(column_name)

Example:

-- Count all rows
SELECT COUNT(*) FROM users;

-- Count non-null values in a column
SELECT COUNT(email) FROM users;

-- Count with GROUP BY
SELECT category, COUNT(*) FROM products GROUP BY category;

SUM

Calculates the sum of numeric values. Syntax:

SUM(column_name)

Example:

SELECT SUM(price) FROM orders WHERE customer_id = 1;
SELECT category, SUM(quantity) FROM inventory GROUP BY category;

MIN

Returns the minimum value. Syntax:

MIN(column_name)

Example:

SELECT MIN(price) FROM products;
SELECT MIN(created_at) FROM events WHERE user_id = 1;

MAX

Returns the maximum value. Syntax:

MAX(column_name)

Example:

SELECT MAX(price) FROM products;
SELECT MAX(event_time) FROM events WHERE user_id = 1;

AVG

Calculates the average of numeric values. Syntax:

AVG(column_name)

Example:

SELECT AVG(rating) FROM reviews WHERE product_id = 100;
SELECT category, AVG(price) FROM products GROUP BY category;

UUID and TimeUUID Functions

uuid()

Generates a random UUID (version 4). Syntax:

uuid()

Example:

INSERT INTO sessions (session_id, user_id, created_at)
VALUES (uuid(), 1, toTimestamp(now()));

SELECT uuid() as new_id;

now()

Generates a new TimeUUID (version 1) with the current timestamp. Syntax:

now()

Example:

-- Use as TimeUUID
CREATE TABLE events (id TIMEUUID PRIMARY KEY, data TEXT);
INSERT INTO events (id, data) VALUES (now(), 'Event data');

-- Multiple calls generate different UUIDs
INSERT INTO events (id, data) VALUES (now(), 'Event 1');
INSERT INTO events (id, data) VALUES (now(), 'Event 2');

-- Use in WHERE clauses for time-based queries
SELECT * FROM events WHERE id <= now();

Note: Each call to now() generates a unique TimeUUID with the current timestamp and a random component.

minTimeuuid()

Generates the minimum possible TimeUUID for a given timestamp. Syntax:

minTimeuuid(timestamp)

Parameters:

timestamp: TIMESTAMP value or milliseconds since epoch

Example:

-- Find events after a specific time
SELECT * FROM events 
WHERE id >= minTimeuuid('2024-01-15 00:00:00');

-- Using milliseconds
SELECT * FROM events 
WHERE id >= minTimeuuid(1705276800000);

-- Time range query
SELECT * FROM events
WHERE id >= minTimeuuid('2024-01-01') 
  AND id < maxTimeuuid('2024-02-01');

maxTimeuuid()

Generates the maximum possible TimeUUID for a given timestamp. Syntax:

maxTimeuuid(timestamp)

Parameters:

timestamp: TIMESTAMP value or milliseconds since epoch

Example:

-- Find events before a specific time
SELECT * FROM events 
WHERE id < maxTimeuuid('2024-01-15 23:59:59');

-- Events within a day
SELECT * FROM events
WHERE id >= minTimeuuid('2024-01-15')
  AND id <= maxTimeuuid('2024-01-15');

Timestamp Functions

toTimestamp()

Converts a TimeUUID to a TIMESTAMP. Syntax:

toTimestamp(timeuuid)

Example:

-- Extract timestamp from TimeUUID
SELECT id, toTimestamp(id) as event_time, data FROM events;

-- Use with now()
INSERT INTO logs (id, created_at, message)
VALUES (uuid(), toTimestamp(now()), 'Log message');

-- Filter by converted timestamp
SELECT * FROM events
WHERE toTimestamp(id) >= '2024-01-01'
ALLOW FILTERING;

currentTimestamp()

Returns the current timestamp with microsecond precision. Syntax:

currentTimestamp()

Example:

INSERT INTO events (id, created_at, data)
VALUES (uuid(), currentTimestamp(), 'Event data');

SELECT currentTimestamp() as now;

Note: With the flag cql_revert_to_partial_microsecond_support=true (default), timestamps include microsecond precision.

toUnixTimestamp()

Converts a TimeUUID or TIMESTAMP to Unix timestamp (milliseconds since epoch). Syntax:

toUnixTimestamp(timeuuid)
toUnixTimestamp(timestamp)

Example:

-- From TimeUUID
SELECT toUnixTimestamp(id) FROM events;

-- From TIMESTAMP
SELECT toUnixTimestamp(created_at) FROM users;

-- Example values
SELECT toUnixTimestamp(now());  -- Returns current time in milliseconds
SELECT toUnixTimestamp('2024-01-15 10:30:00');  -- Returns 1705318200000

unixTimestampOf()

Alias for toUnixTimestamp() - converts TimeUUID to Unix timestamp. Syntax:

unixTimestampOf(timeuuid)

Example:

SELECT unixTimestampOf(id) as epoch_time FROM events;
SELECT unixTimestampOf(now());

Type Conversion Functions - To Blob

Convert various data types to BLOB format.

booleanAsBlob()

Syntax: booleanAsBlob(boolean_value) Example:

INSERT INTO data (id, blob_data) VALUES (1, booleanAsBlob(true));
-- true -> 0x01, false -> 0x00

tinyIntAsBlob()

Syntax: tinyIntAsBlob(tinyint_value) Example:

INSERT INTO data (id, blob_data) VALUES (1, tinyIntAsBlob(121));
-- 121 -> 0x79

smallIntAsBlob()

Syntax: smallIntAsBlob(smallint_value) Example:

INSERT INTO data (id, blob_data) VALUES (1, smallIntAsBlob(1992));
-- 1992 -> 0x07c8

intAsBlob()

Syntax: intAsBlob(int_value) Example:

INSERT INTO data (id, blob_data) VALUES (1, intAsBlob(1992123));
-- 1992123 -> 0x001e65bb

bigIntAsBlob()

Syntax: bigIntAsBlob(bigint_value) Example:

INSERT INTO data (id, blob_data) VALUES (1, bigIntAsBlob(1992123));
-- 1992123 -> 0x00000000001e65bb

floatAsBlob()

Syntax: floatAsBlob(float_value) Example:

INSERT INTO data (id, blob_data) VALUES (1, floatAsBlob(17.31));
-- 17.31 -> 0x418a7ae1 (IEEE 754 format)

doubleAsBlob()

Syntax: doubleAsBlob(double_value) Example:

INSERT INTO data (id, blob_data) VALUES (1, doubleAsBlob(19213.223));
-- 19213.223 -> 0x40d2c34e45a1cac1 (IEEE 754 format)

textAsBlob() / varcharAsBlob()

Syntax: textAsBlob(text_value) or varcharAsBlob(varchar_value) Example:

INSERT INTO data (id, blob_data) VALUES (1, textAsBlob('Hello'));
-- 'Hello' -> 0x48656c6c6f (UTF-8 encoding)

INSERT INTO data (id, blob_data) VALUES (2, textAsBlob('Hello World!'));
-- 'Hello World!' -> 0x48656c6c6f20576f726c6421

timestampAsBlob()

Syntax: timestampAsBlob(timestamp_value) Example:

INSERT INTO data (id, blob_data) VALUES (1, timestampAsBlob(1516326741100));
-- 1516326741100 -> 0x000001610c1de46c

uuidAsBlob()

Syntax: uuidAsBlob(uuid_value) Example:

INSERT INTO data (id, blob_data) 
VALUES (1, uuidAsBlob(fa713975-2630-4e37-aaef-465ab850b986));
-- UUID -> 0xfa71397526304e37aaef465ab850b986

timeuuidAsBlob()

Syntax: timeuuidAsBlob(timeuuid_value) Example:

INSERT INTO data (id, blob_data) 
VALUES (1, timeuuidAsBlob(799cb5a0-5810-11e8-9c2d-fa7ae01bbebc));
-- TimeUUID -> 0x799cb5a0581011e89c2dfa7ae01bbebc

Type Conversion Functions - From Blob

Convert BLOB format back to various data types.

blobAsBoolean()

Syntax: blobAsBoolean(blob_value) Example:

SELECT blobAsBoolean(0x01);  -- Returns true
SELECT blobAsBoolean(0x00);  -- Returns false

blobAsTinyInt()

Syntax: blobAsTinyInt(blob_value) Example:

SELECT blobAsTinyInt(0x79);  -- Returns 121
SELECT blobAsTinyInt(0xed);  -- Returns -19

blobAsSmallInt()

Syntax: blobAsSmallInt(blob_value) Example:

SELECT blobAsSmallInt(0x07c8);  -- Returns 1992

blobAsInt()

Syntax: blobAsInt(blob_value) Example:

SELECT blobAsInt(0x001e65bb);  -- Returns 1992123

blobAsBigInt()

Syntax: blobAsBigInt(blob_value) Example:

SELECT blobAsBigInt(0x00000000001e65bb);  -- Returns 1992123

blobAsFloat()

Syntax: blobAsFloat(blob_value) Example:

SELECT blobAsFloat(0x418a7ae1);  -- Returns ~17.31

blobAsDouble()

Syntax: blobAsDouble(blob_value) Example:

SELECT blobAsDouble(0x40d2c34e45a1cac1);  -- Returns ~19213.223

blobAsText() / blobAsVarchar()

Syntax: blobAsText(blob_value) or blobAsVarchar(blob_value) Example:

SELECT blobAsText(0x48656c6c6f);  -- Returns 'Hello'
SELECT blobAsVarchar(0x48656c6c6f20576f726c6421);  -- Returns 'Hello World!'

blobAsTimestamp()

Syntax: blobAsTimestamp(blob_value) Example:

SELECT blobAsTimestamp(0x000001610c1de46c);  -- Returns timestamp

blobAsUuid()

Syntax: blobAsUuid(blob_value) Example:

SELECT blobAsUuid(0xfa71397526304e37aaef465ab850b986);
-- Returns: fa713975-2630-4e37-aaef-465ab850b986

blobAsTimeuuid()

Syntax: blobAsTimeuuid(blob_value) Example:

SELECT blobAsTimeuuid(0x799cb5a0581011e89c2dfa7ae01bbebc);
-- Returns: 799cb5a0-5810-11e8-9c2d-fa7ae01bbebc

Practical Examples

Working with Blobs in Collections

CREATE TABLE binary_data (
  id INT PRIMARY KEY,
  values MAP<INT, BLOB>,
  blob_set SET<BLOB>,
  blob_list LIST<BLOB>
);

-- Insert using conversion functions
INSERT INTO binary_data (id, values, blob_set, blob_list)
VALUES (
  1,
  {1: intAsBlob(42), 2: textAsBlob('hello')},
  {intAsBlob(1), intAsBlob(2)},
  [intAsBlob(10), textAsBlob('data')]
);

Time-Based Queries with TimeUUIDs

CREATE TABLE events (
  id TIMEUUID PRIMARY KEY,
  event_type TEXT,
  data JSONB
);

-- Insert events
INSERT INTO events (id, event_type, data) VALUES (now(), 'login', '{"user": 1}');
INSERT INTO events (id, event_type, data) VALUES (now(), 'logout', '{"user": 1}');

-- Query events from last hour
SELECT id, toTimestamp(id) as time, event_type 
FROM events
WHERE id >= minTimeuuid('2024-01-15 10:00:00')
  AND id < maxTimeuuid('2024-01-15 11:00:00');

-- Get Unix timestamp from TimeUUID
SELECT unixTimestampOf(id), event_type FROM events;

Timestamp Precision Control

-- Using currentTimestamp() for microsecond precision
CREATE TABLE precise_events (
  id INT PRIMARY KEY,
  created_at TIMESTAMP
);

INSERT INTO precise_events (id, created_at) 
VALUES (1, currentTimestamp());

-- With flag: cql_revert_to_partial_microsecond_support=true (default)
-- Timestamps include microseconds: 2024-08-26 09:23:38.319213+0000

-- With flag: cql_revert_to_partial_microsecond_support=false
-- Timestamps use milliseconds: 2024-08-26 09:23:38.319000+0000

Type Conversion Round-Trip

CREATE TABLE conversion_test (
  id INT PRIMARY KEY,
  original_int INT,
  as_blob BLOB,
  back_to_int INT
);

-- Convert to blob and back
INSERT INTO conversion_test (id, original_int, as_blob)
VALUES (1, 42, intAsBlob(42));

UPDATE conversion_test 
SET back_to_int = blobAsInt(as_blob)
WHERE id = 1;

SELECT original_int, back_to_int FROM conversion_test;
-- Both columns show: 42

Best Practices

Use prepared statements with functions: Better performance for repeated operations
TimeUUID for time-series data: Provides both ordering and uniqueness
Blob conversions for compatibility: Useful when interfacing with external systems
Aggregate with GROUP BY: Combine aggregate functions with grouping for analytics
minTimeuuid/maxTimeuuid for time ranges: Efficient time-based queries on TimeUUID columns
currentTimestamp() vs now(): Use currentTimestamp() for TIMESTAMP columns, now() for TIMEUUID columns

Function Compatibility Notes

All TimeUUID functions are compatible with Apache Cassandra
Blob conversion functions follow Cassandra’s binary encoding
Aggregate functions support standard CQL syntax
Timestamp functions support both millisecond and microsecond precision (configured via flags)

YSQL

YCQL

Client Drivers

CLI Tools

​Overview

​Aggregate Functions

​COUNT

​SUM

​MIN

​MAX

​AVG

​UUID and TimeUUID Functions

​uuid()

​now()

​minTimeuuid()

​maxTimeuuid()

​Timestamp Functions

​toTimestamp()

​currentTimestamp()

​toUnixTimestamp()

​unixTimestampOf()

​Type Conversion Functions - To Blob

​booleanAsBlob()

​tinyIntAsBlob()

​smallIntAsBlob()

​intAsBlob()

​bigIntAsBlob()

​floatAsBlob()

​doubleAsBlob()

​textAsBlob() / varcharAsBlob()

​timestampAsBlob()

​uuidAsBlob()

​timeuuidAsBlob()

​Type Conversion Functions - From Blob

​blobAsBoolean()

​blobAsTinyInt()

​blobAsSmallInt()

​blobAsInt()

​blobAsBigInt()

​blobAsFloat()

​blobAsDouble()

​blobAsText() / blobAsVarchar()

​blobAsTimestamp()

​blobAsUuid()

​blobAsTimeuuid()

​Practical Examples

​Working with Blobs in Collections

​Time-Based Queries with TimeUUIDs

​Timestamp Precision Control

​Type Conversion Round-Trip

​Best Practices

​Function Compatibility Notes

Build docs developers (and LLMs) love

Overview

Aggregate Functions

COUNT

SUM

MIN

MAX

AVG

UUID and TimeUUID Functions

uuid()

now()

minTimeuuid()

maxTimeuuid()

Timestamp Functions

toTimestamp()

currentTimestamp()

toUnixTimestamp()

unixTimestampOf()

Type Conversion Functions - To Blob

booleanAsBlob()

tinyIntAsBlob()

smallIntAsBlob()

intAsBlob()

bigIntAsBlob()

floatAsBlob()

doubleAsBlob()

textAsBlob() / varcharAsBlob()

timestampAsBlob()

uuidAsBlob()

timeuuidAsBlob()

Type Conversion Functions - From Blob

blobAsBoolean()

blobAsTinyInt()

blobAsSmallInt()

blobAsInt()

blobAsBigInt()

blobAsFloat()

blobAsDouble()

blobAsText() / blobAsVarchar()

blobAsTimestamp()

blobAsUuid()

blobAsTimeuuid()

Practical Examples

Working with Blobs in Collections

Time-Based Queries with TimeUUIDs

Timestamp Precision Control

Type Conversion Round-Trip

Best Practices

Function Compatibility Notes