CQL native functions and aggregates

CQL supports built-in scalar functions that transform a column value into a new value, and aggregate functions that compute a single result across a set of rows.

You can also create your own user-defined functions (UDFs) and aggregate functions (UDAs).

Functions

CQL supports the following native functions.

cast()

CAST( <selector> AS <to_type> )

Converts the data returned by the selector to a native CQL data type. The cast function is a strict conversion and relies on Java semantics. See CQL to Java type mapping. The cast function does not support casting a column into its own data type.

selector column type Output data type (to_type)

ascii

text, varchar

bigint

tinyint, smallint, int, varint, float, double, decimal, text, varchar

boolean

text, varchar

counter

tinyint, smallint, int, bigint, float, double, decimal, varint, text, varchar

date

timestamp

decimal

tinyint, smallint, int, bigint, float, double, varint, text, varchar

double

tinyint, smallint, int, bigint, float, decimal, varint, text, varchar

float

tinyint, smallint, int, bigint, double, decimal, varint, text, varchar

inet

text, varchar

int

tinyint, smallint, bigint, float, double, decimal, varint, text, varchar

smallint

tinyint, int, bigint, float, double, decimal, varint, text, varchar

time

text, varchar

timestamp

date, text, varchar

timeuuid

timestamp, date, text, varchar

tinyint

tinyint, smallint, int, bigint, float, double, decimal, varint, text, varchar

uuid

text, varchar

varint

tinyint, smallint, int, bigint, float, double, decimal, text, varchar

token()

TOKEN(<column_name>)

Computes a token for values in the specified column.

The exact signature of the token function depends on the table and the partitioner used by the cluster:

  • Murmur3Partitioner type is bigint.

  • RandomPartitioner type is varint.

  • ByteOrderedPartitioner type is blob.

Use token() in a SELECT …​ <selector> clause to return values in the target column as tokens. Useful for manually paging through the data, or to determine the physical location of a row or set of rows.

Use token() in a SELECT …​ WHERE clause to return a set of records in a given token range. This allows comparison (greater than, less than) in the WHERE clause without enabling ALLOW FILTERING. Useful when dividing query workloads between clients, such as multiple Apache Spark™ clients for analytics.

UPDATE does not support token() in the WHERE clause.

ttl()

TTL(<column_name>)

Time in seconds until the value in the field expires and the data is deleted. A null TTL indicates that the TTL isn’t set or the TTL has expired. Cannot be used with primary key columns, counter columns, or non-frozen collection columns.

Use only in the selectors clause of SELECT statements on non-partition key columns.

For more information, see Data with TTL.

uuid()

UUID()

Generates a random type 4 UUID for INSERT or UPDATE.

In UPDATE statements, the SET clause cannot be used to change primary key fields; therefore, you can only set a new UUID if the target field isn’t part of the primary key definition.

Takes no parameters.

writeTime()

WRITETIME(<column_name>)

Shows the unix timestamp (number of seconds since epoch) for when the data was written. Cannot be used with primary key columns, counter columns, or non-frozen collection columns.

Use only in the selectors clause of SELECT statements on non-partition key columns.

Current date and time

Return the current system time of the coordinator node in a CQL type format. These functions take no arguments.

currentDate

Returns the system time in date format.

currentTime

Returns the system time in time format.

currentTimestamp

Returns the system time in timestamp format.

currentTimeUuid or now

now() is an alias for currentTimeUuid().

Returns the system time in timeuuid format.

Use in INSERT or UPDATE statements to generate a new timeuuid value.

In UPDATE statements, the SET clause cannot be used to change primary key fields; therefore, you can only set a new timeuuid if the target field isn’t part of the primary key definition.

Although it is allowed in WHERE clauses, no values would match a given SELECT or UPDATE statement because now() is guaranteed to be unique.

floor

Rounds a date or time type to the nearest value:

  • timestamp

    floor(timestamp, duration [,  <start_timestamp>])

    If the <start_timestamp> is not used, then the start timestamp is January 1, 1970 00:00:00.000 GMT.

  • timeuuid

    floor(timeuuid, duration [, <start_timestamp>])

    If the <start_timestamp> is not used, then the start timestamp is January 1, 1970 00:00:00.000 GMT.

  • date

    floor(date, duration [, <start_date>])

    If the <start_date> is not used, then the start date is January 1, 1970 GMT.

  • time

    floor(time, duration[, <start_time>])

    If the <start_time> is not used, then the start time is 00:00:00[000000000].

Use the duration syntax as follows:

  • <N> y - Number of years

  • <N> mo - Number of months

  • <N> w - Number of weeks

  • <N> d - Number of days

  • <N> h - Number of hours

  • <N> m - Number of minutes

  • <N> s - Number of seconds

  • <N> ms - Number of milliseconds

  • <N> us or µs - Number of microseconds

  • <N> ns - Number of nanoseconds

TimeUuid calculations

Returns a UUID-like result given a conditional time component as an argument.

  • minTimeUuid:

    MINTIMEUUID( <date_string> or <timestamp_string> )

    Computes the smallest fake timeuuid from the specified date ('<yyyy>-<mm>-<dd>') or timestamp ('<yyyy>-<mm>-<dd> [<hh>:<mm>:<ss>[.<fff>][+/-<NNNN>]]') formatted string.

    Use in a SELECT statement WHERE clause to return rows in a date range from a timeuuid column, for example WHERE id >= minTimeuuid('2017-05-01').

  • maxTimeUuid:

    MAXTIMEUUID( <date_string> or <timestamp_string> )

    Computes the largest fake timeuuid from the specified date ('<yyyy>-<mm>-<dd>') or timestamp ('<yyyy>-<mm>-<dd> [<hh>:<mm>:<ss>[.<fff>][+/-<NNNN>]]') formatted string.

    Use in a SELECT statement WHERE clause to return rows in a date range from a timeuuid column, for example WHERE id <= maxTimeuuid('2017-05-01').

The following min/maxTimeuuid example selects all rows where the timeuuid column, t, is strictly later than 2013-01-01 00:01+0000 but strictly earlier than 2013-01-02 10:00+0000. The condition t >= maxTimeuuid('2013-01-01 00:01+0000') doesn’t select a timeuuid generated exactly at 2013-01-01 00:01+0000, and it is essentially equivalent to t > maxTimeuuid('2013-01-01 00:01+0000').

SELECT a, b, toDate(c), toDate(d) FROM cycling.sample_times
  WHERE c > maxTimeuuid('2013-01-01 00:01+0000')
    AND c < minTimeuuid('2013-01-02 10:00+0000')
ALLOW FILTERING;

The values returned by minTimeuuid and maxTimeuuid functions aren’t true UUIDs because the values don’t conform to the Time-Based UUID generation process. The results of these functions are deterministic, unlike the now() function.

Date and time conversion

Converts the data in the defined argument from a timestamp, date, and timeuuid to another type.

toDate

TODATE(<column_name>)

Converts a timestamp value from milliseconds to days (since epoch), and for timeuuid extracts the timestamp and converts it to days since epoch.

Dates and times are printed in string format but are stored and compared as integers.

For example:

SELECT a, b, toDate(c), toDate(d) FROM cycling.sample_times
  WHERE c > maxTimeuuid('2013-01-01 00:01+0000')
    AND c < minTimeuuid('2013-01-02 10:00+0000')
ALLOW FILTERING;

toTimestamp

TOTIMESTAMP(<column_name>)

Converts a date value from days into milliseconds since epoch, and for timeuuid extracts the timestamp.

Time (hh:mm:ss.ffffff+NNNN) of a date are all set to zero in the resulting timestamp.

toUnixTimestamp

TOUNIXTIMESTAMP(<column_name>)

Converts the timeuuid, timestamp, or date column into the number of seconds since epoch.

For example:

INSERT INTO cycling.sample_times (
  a, b, c, d
) VALUES (
  1, toUnixTimestamp(now()), now(), toTimestamp(now())
);

INSERT INTO cycling.sample_times (
  a, b, c, d
) VALUES (
  2, toUnixTimestamp(now()), maxTimeuuid('2013-01-02 00:01+0000'), toTimestamp(now())
);

INSERT INTO cycling.sample_times (
  a, b, c, d
) VALUES (
  3, toUnixTimestamp(now()), maxTimeuuid('2013-01-03 00:03+0000'), toTimestamp(now())
);

INSERT INTO cycling.sample_times (
  a, b, c, d
) VALUES (
  4, toUnixTimestamp(now()), maxTimeuuid('2013-01-04 00:05+0000'), toTimestamp(now())
);

Blob conversion

Convert CQL native types to or from binary (blob):

blobAs
blobAs<type>(<column_name> or <literal>)

Converts the target column or literal (enclose strings in single quotes) from a BLOB to the specified type.

Use to:

  • Output blob columns as another data type in results from SELECT statements.

  • Convert raw BLOB data into another type for writes with INSERT and UPDATE statements.

AsBlob
<type>AsBlob(<column_name> or <literal>)

Converts the target column or literal (enclose strings in single quotes) to a BLOB from the specified type.

Use to:

  • Output a value stored as another CQL data type as a BLOB in results from SELECT statements with a <selectors> clause.

  • Convert another CQL data type into a BLOB for writes with INSERT and UPDATE.

For example:

  1. Create a table with a blob column and a column of another type:

    CREATE TABLE IF NOT EXISTS cycling.lastname_bio (
  lastname varchar PRIMARY KEY,
  bio blob,
  id bigint
);

  2. Insert data with bigintAsBlob() and blobAsBigint():

    INSERT INTO cycling.lastname_bio ( lastname, bio ) VALUES ( 'TSATEVICH', bigintAsBlob(3) );
INSERT INTO cycling.lastname_bio ( lastname, id ) VALUES ( 'DUVAL', blobAsBigint(0x0000000000000003) );

  3. Query the table:

    SELECT * FROM cycling.lastname_bio;

    bigintAsBlob() converted 3 into 0x0000000000000003, and blobAsBigint() converted 0x0000000000000003 into 3.

     lastname  | bio                | id
-----------+--------------------+------
     DUVAL |               null |    3
 TSATEVICH | 0x0000000000000003 | null

(2 rows)

Deprecated functions

The following functions are supported for backward compatibility only:

Aggregates

Aggregate functions work on a set of rows matching a SELECT statement to return a single value. An aggregate is specified by the aggregate name and the target column on which the aggregation is performed:

<aggregate_name>(<column_name>)

By default, null values in the dataset are ignored. When other columns, columns with user-defined types, or functions, are also specified in the selector clause of a SELECT statement with an aggregate function, the values in the first row matching the query are returned.

Aggregating data across partitions may cause performance issues. The recommendation is to use aggregates on one partition, to get a list of partition keys use SELECT DISTINCT partition_key to get a list of unique keys (for compound keys use a comma separated list of column names).

The following built-in system aggregates are supported:

Aggregate name Supported data types Description

AVG

tinyint, smallint, int, bigint, decimal, float, double

Provides the average value of the target column using the data type of target column. Null values are ignored.

COUNT

Works on all CQL data types

Provides the number of rows in the result set that do not have null values. For a complete row count use partition key column, since partition keys cannot contain nulls.

MIN

tinyint, smallint, int, bigint, decimal, float, double

Provides the smallest value. Null values are ignored.

MAX

tinyint, smallint, int, bigint, decimal, float, double

Provides the largest value. Null values are ignored.

SUM

tinyint, smallint, int, bigint, decimal, float, double

Provides the total of the target column; nulls are ignored.

Aggregate examples

Find Average (AVG)

Get average time in seconds for the team:

SELECT AVG(cyclist_time_sec) AS Average FROM cycling.team_average
  WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';
 average
---------
   11474

(1 rows)
Find Count (COUNT)

Find the number rows for the United Health Care Pro Cycling Women’s Team:

SELECT COUNT(cyclist_name) AS Row_Count FROM cycling.team_average
  WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';
 row_count
-----------
         3

(1 rows)
Find lowest value (MIN)

Find the slowest time recorded for the United Health Care Pro Cycling Women’s Team:

SELECT MIN(cyclist_time_sec) AS Fastest FROM cycling.team_average
  WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';
 fastest
---------
   11449

(1 rows)
Find highest value (MAX)

Find the fastest time recorded for the United Health Care Pro Cycling Women’s Team:

SELECT MAX(cyclist_time_sec) AS Slowest FROM cycling.team_average
  WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';
 slowest
---------
   11490

(1 rows)
Find total (SUM)

Find the total of all times recorded for the United Health Care Pro Cycling Women’s Team:

SELECT SUM(cyclist_time_sec) AS Total_Time FROM cycling.team_average
  WHERE team_name = 'UnitedHealthCare Pro Cycling Womens Team';
 total_time
------------
      34424

(1 rows)

Was this helpful?

Give Feedback

How can we improve the documentation?

© Copyright IBM Corporation 2026 | Privacy policy | Terms of use |  Manage Privacy Choices

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: Contact IBM