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
Cassandra supports the following native functions.
- cast
-
Converts the data returned by the <selector> to a native CQL data type.
CAST( <selector> AS <to_type> )
Cast strictly relies on the Java semantics, for more details on the underlying type see CQL types Java derivation
Silently ignores 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
tonnyint, 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 Java types, from which most CQL types are derived, are obvious to Java programmers. The derivation of the following types, however, might not be obvious:
Table 1. CQL types Java derivation CQL type Java type decimal
float
double
varint
- 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).
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 |
- uuid
-
Takes no parameters and generates a random type 4 uuid.
UUID()
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.
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.
- 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 becausenow()
is guaranteed to be unique.
-
- floor
-
Rounds date and time to the nearest value.
-
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.
-
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.
-
floor(date, duration [, <start_date>])
If the <start_date> is not used, then the start date is January 1, 1970 GMT.
-
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
- 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> | <timestamp_string> )
Use in a SELECT statement WHERE clause to return rows in a date range from a
timeuuid
column, for exampleWHERE 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> | <timestamp_string> )
Use in a SELECT statement where clause to return rows in a date range from a
timeuuid
column, for exampleWHERE id <= maxTimeuuid('2017-05-01')
.
Date and time conversion
- toDate
-
Converts a
timestamp
value from milliseconds to days (since epoch) and fortimeuuid
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 fortimeuuid
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
, ordate
column into the number of seconds since epoch.TOUNIXTIMESTAMP(<column_name>)
Blob conversion
Convert CQL native types to binary (blob
).
- blobAs
-
Converts the target column or literal (enclose strings in single quotes) from a blob to the specified type.
blobAs<type>(<column_name> | <literal>)
- 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> | <literal> )
Use in the following types of CQL statements:
Deprecated functions
The following functions are supported for backward compatibility only.
- dateOf
-
Similar to toTimestamp.
- unixTimestampOf
-
Similar to toUnixTimestamp