Functions

CQL supports 2 main categories of functions:

  • the scalar functions, which simply take a number of values and produce an output with it.
  • the aggregate functions, which are used to aggregate multiple rows results from a SELECT statement.

Scalar functions

Native functions

Cast

Supported starting from Scylla version 2.1

The cast function can be used to converts one native datatype to another.

The following table describes the conversions supported by the cast function. Cassandra will silently ignore any cast converting a datatype into its own datatype.

From To
ascii text, varchar
bigint tinyint, smallint, int, float, double, decimal, varint, 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

The conversions rely strictly on Java’s semantics. For example, the double value 1 will be converted to the text value ‘1.0’. For instance:

SELECT avg(cast(count as double)) FROM myTable

Token

The token function allows to compute the token for a given partition key. The exact signature of the token function depends on the table concerned and of the partitioner used by the cluster.

The type of the arguments of the token depend on the type of the partition key columns. The return type depend on the partitioner in use:

  • For Murmur3Partitioner, the return type is bigint.

For instance, in a cluster using the default Murmur3Partitioner, if a table is defined by:

CREATE TABLE users (
    userid text PRIMARY KEY,
    username text,
)

Then the token function will take a single argument of type text (in that case, the partition key is userid (there is no clustering columns so the partition key is the same than the primary key)), and the return type will be bigint.

Uuid

The uuid function takes no parameters and generates a random type 4 uuid suitable for use in INSERT or UPDATE statements.

Note

We called the values generated by minTimeuuid and maxTimeuuid fake UUID because they do no respect the Time-Based UUID generation process specified by the RFC 4122. In particular, the value returned by these 2 methods will not be unique. This means you should only use those methods for querying (as in the example above). Inserting the result of those methods is almost certainly a bad idea.

Datetime functions

New in version 2.3.

Retrieving the current date/time

The following functions can be used to retrieve the date/time at the time where the function is invoked:

Function name Output type
currentTimestamp timestamp
currentDate date
currentTime time
currentTimeUUID timeUUID

For example, to retrieve the last two days of data run the following query:

SELECT * FROM myTable WHERE date >= currentDate() - 2d
Time conversion functions

A number of functions are provided to “convert” a timeuuid, a timestamp, or a date into another native type.

Function name Input type Description
toDate timeuuid Converts the timeuuid argument into a date type
toDate timestamp Converts the timestamp argument into a date type
toTimestamp timeuuid Converts the timeuuid argument into a timestamp type
toTimestamp date Converts the date argument into a timestamp type
toUnixTimestamp timeuuid Converts the timeuuid argument into a bigInt raw value
toUnixTimestamp timestamp Converts the timestamp argument into a bigInt raw value
toUnixTimestamp date Converts the date argument into a bigInt raw value
dateOf timeuuid Similar to toTimestamp(timeuuid) (DEPRECATED)
unixTimestampOf timeuuid Similar to toUnixTimestamp(timeuuid) (DEPRECATED)

Blob conversion functions

A number of functions are provided to “convert” the native types into binary data (blob). For every <native-type> type supported by CQL (a notable exceptions is blob, for obvious reasons), the function typeAsBlob takes a argument of type type and return it as a blob. Conversely, the function blobAsType takes a 64-bit blob argument and convert it to a bigint value. And so for instance, bigintAsBlob(3) is 0x0000000000000003 and blobAsBigint(0x0000000000000003) is 3.

Aggregate functions

Aggregate functions work on a set of rows. They receive values for each row and returns one value for the whole set.

If normal columns, scalar functions, UDT fields, writetime or ttl are selected together with aggregate functions, the values returned for them will be the ones of the first row matching the query.

Native aggregates

Count

The count function can be used to count the rows returned by a query. Example:

SELECT COUNT (*) FROM plays;
SELECT COUNT (1) FROM plays;

It also can be used to count the non null value of a given column:

SELECT COUNT (scores) FROM plays;

Max and Min

The max and min functions can be used to compute the maximum and the minimum value returned by a query for a given column. For instance:

SELECT MIN (players), MAX (players) FROM plays WHERE game = 'quake';

Sum

The sum function can be used to sum up all the values returned by a query for a given column. For instance:

SELECT SUM (players) FROM plays;

Avg

The avg function can be used to compute the average of all the values returned by a query for a given column. For instance:

SELECT AVG (players) FROM plays;

Apache Cassandra Query Language