CQL native functions

CQL supports several functions that transform a column value into a new value.

In addition, users can define functions and aggregates.

Scalar functions

CQL supports the following native functions.

Function Description

cast

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 types Java derivation. Also note that the cast function does not support casting a column into its own data type.

CAST( <selector> AS <to_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

Computes a token for values in the target column.

TOKEN(<column_name>)

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

Murmur3Partitioner type is bigint
RandomPartitioner type is varint
ByteOrderedPartitioner type is blob Use in the following CQL requests:
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.
SELECT where clause to return a set of records in a given token range. Allows comparison (greater than and less then) in the where clause without the use of ALLOW FILTERING. Useful when dividing query workloads between clients, such as multiple Spark clients for analytics.

UPDATE does not support the token function in the where clause.

ttl

Count down in seconds until the value in the field expires and the data is automatically removed; null indicates that TTL for the column is not set (or the count down has ended). Cannot be used with primary key columns, counter columns, or non-frozen collection columns.

TTL(<column_name>)

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

The database storage engine can only encode TTL timestamps through January 19 2038 03:14:07 UTC due to the Year 2038 problem. The TTL date overflow policy determines whether requests with expiration timestamps later than the maximum date are rejected or inserted. See -Dcassandra.expiration_date_overflow_policy.

uuid

Takes no parameters and generates a random type 4 uuid.

UUID()

Use to generate a value in INSERT and UPDATE.

Restriction: UPDATE statements SET clause cannot be used to change PRIMARY KEY fields; therefore a new UUID can only be set if the target field is not part of the PRIMARY KEY field.

writeTime

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

WRITETIME(<column_name>)

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

Date and time functions

Current date and time

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

Function Description

Function	Description
`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`	Returns the system time in timeuuid format. Restriction: UPDATE statements SET clause cannot be used to change PRIMARY KEY fields; therefore a new `timeuuid` can only be set if the target field is not part of the PRIMARY KEY field. Use in INSERT or UPDATE statements to generate a new `timeuuid` value. Although allowed in `WHERE` clauses, no values would match a given SELECT or UPDATE statement because `now()` is guaranteed to be unique.
`floor`	Rounds date and time 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
`now`	Alias of currentTimeUuid.

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

Returns the system time in timeuuid format. Restriction:

UPDATE statements SET clause cannot be used to change PRIMARY KEY fields; therefore a new timeuuid can only be set if the target field is not part of the PRIMARY KEY field.
Use in INSERT or UPDATE statements to generate a new timeuuid value.
Although allowed in WHERE clauses, no values would match a given SELECT or UPDATE statement because now() is guaranteed to be unique.

floor

Rounds date and time 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

now

Alias of currentTimeUuid.

TimeUuid calculations

Function Description

Function	Description
`minTimeUuid`	Computes the smallest fake `timeuuid` from the specified date (`'<yyyy>-<mm>-<dd>'`) or timestamp (`'<yyyy>-<mm>-<dd> [<hh>:<mm>:<ss>[.<fff>][+/-<NNNN>]]'`) formatted string. `minTimeuuid( <date_string> or <timestamp_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`	Computes the largest fake `timeuuid` from the specified date (`'<yyyy>-<mm>-<dd>'`) or timestamp (`'<yyyy>-<mm>-<dd> [<hh>:<mm>:<ss>[.<fff>][+/-<NNNN>]]'`) formatted string. `MAXTIMEUUID( <date_string> or <timestamp_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')`.

minTimeUuid

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

minTimeuuid( <date_string> or <timestamp_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

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

MAXTIMEUUID( <date_string> or <timestamp_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').

Date and time conversion

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

Function Description

toDate

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

TODATE(<column_name>)

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

toTimestamp

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

TOTIMESTAMP(<column_name>)

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

toUnixTimestamp

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

TOUNIXTIMESTAMP(<column_name>)

Blob conversion

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

Function Description

Function	Description
`blobAs`	Converts the target column or literal (enclose strings in single quotes) from a blob to the specified type. `blobAs<type>(<column_name> or <literal>)` Display blob columns as another data type in results of SELECT statements. Convert raw blob data into another type for storage INSERT and UPDATE.
`AsBlob`	Converts the target column or literal (enclose strings in single quotes) to a blob from the specified <type>, where the from type corresponds to a valid CQL data type. `<type>AsBlob(<column_name> or <literal> )` Use in the following types of CQL statements: SELECT <selectors> to return a value stored in another CQL type as a blob. INSERT and UPDATE convert another CQL data type into a blob for storage.

blobAs

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

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

Display blob columns as another data type in results of SELECT statements.
Convert raw blob data into another type for storage INSERT and UPDATE.

AsBlob

Converts the target column or literal (enclose strings in single quotes) to a blob from the specified <type>, where the from type corresponds to a valid CQL data type.

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

Use in the following types of CQL statements:

SELECT <selectors> to return a value stored in another CQL type as a blob.
INSERT and UPDATE convert another CQL data type into a blob for storage.

Deprecated functions

The following functions are supported for backward compatibility only.

Function Description

Function	Description
`dateOf`	Similar to toTimestamp.
`unixTimestampOf`	Similar to toUnixTimestamp

dateOf

Similar to toTimestamp.

unixTimestampOf

Similar to toUnixTimestamp

CQL native functions

Scalar functions

Date and time functions

Current date and time

TimeUuid calculations

Date and time conversion

Blob conversion

Deprecated functions

Was this helpful?

Give Feedback