Functions¶

CQL supports two 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 of 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. Scylla will silently ignore any cast converting a cast 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 computes a token for a given partition key. The exact signature of the token function depends on the table concerned and on the partitioner used by the cluster.

The arguments of the token depend on the type of the partition key columns that are used. The return type depends 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,
)

The token function accepts single argument of type text (in that case, the partition key is userid (there are no clustering columns, so the partition key is the same as 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 two 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 exception is a blob, for obvious reasons), the function typeAsBlob takes an argument of type type and returns it as a blob. Conversely, the function blobAsType takes a 64-bit blob argument and converts it to a bigint value. For example, 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 return 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

Copyright

Apache®, Apache Cassandra®, Cassandra®, the Apache feather logo and the Apache Cassandra® Eye logo are either registered trademarks or trademarks of the Apache Software Foundation in the United States and/or other countries. No endorsement by The Apache Software Foundation is implied by the use of these marks.