Additional Functions in CQL (Cassandra Query Language)

In this article, we will discuss several functions that are supported by CQL in Cassandra which can help in converting one value type into another new value directly by using the function. In Cassandra, there are several functions that are supported by CQL which helps in many ways such that there is a scenario where we want to find the TTL value of a column with the help of TTL function we can easily do that.

1. Aggregate function
2. UUID and timeuuid function
3. TOKEN function
4. WRITETIME function
5. TTL function

Let’s discuss WRITETIME function. WRITETIME : The WRITETIME function is very useful in Cassandra query language when write occurred then we can retrieve the date/time of writes to the columns. We can use WRITETIME function in the select statement followed by the non-partitioning column in parenthesis. In Cassandra Query Language a table contains the timestamp to represents date and time that a write occurred to a column. After return the query it gives WRITETIME function value in microseconds and then we can convert it into date/time formats. Let's understand with an example.

CREATE TABLE function_test
(
Id int,
Name text,
Address text,
PRIMARY KEY(Id)
);

To insert data into the table used the following CQL query.

INSERT INTO function_test (Id, Name, Address) 
 VALUES (201, 'Ashish', 'Delhi');
INSERT INTO function_test (Id, Name, Address) 
 VALUES (202, 'Rana', 'Mumbai');
INSERT INTO function_test (Id, Name, Address) 
 VALUES (203, 'Abi', 'Noida'); 

To read the data used the following CQL query.

SELECT * 
FROM function_test; 

Output: 👁 Image
To determine the WRITETIME function Value used the following CQL query.

SELECT WRITETIME (Address) 
FROM function_test; 

Output: 👁 Image
To determine the WRITETIME function by using WHERE clause used the following CQL query.

SELECT WRITETIME (Address) 
FROM function_test where Id=201; 

Output: 👁 Image

Cassandra Query Language (CQL) is the query language used to interact with Cassandra databases. In addition to the standard SQL-like syntax for querying and modifying data in Cassandra, CQL also provides a set of built-in functions that can be used to perform various operations on data. Here are some of the additional functions in CQL:

1. now(): Returns the current timestamp in milliseconds since the Unix epoch.
2. dateof() and timeofday(): Extracts the date and time components from a timestamp. For example, dateof('2023-02-17 14:30:00+0000') returns '2023-02-17' and timeofday('2023-02-17 14:30:00+0000') returns '14:30:00'.
3. ttl(): Returns the time to live (TTL) in seconds of a column or cell.
4. writetime(): Returns the timestamp of when a column or cell was last written to in milliseconds since the Unix epoch.
5. minTimeuuid(), maxTimeuuid(), and now(): These functions can be used to generate time-based UUIDs for use in Cassandra.
6. token(): Returns the token for a partition key, which can be used for efficient distributed data querying.
7. uuid(): Generates a random UUID for use in Cassandra.
8. unixTimestampOf(): Returns the Unix timestamp in seconds for a given timestamp value.
9. toTimestamp(): Converts a date or time string to a timestamp value.
10. toUnixTimestamp(): Converts a timestamp value to a Unix timestamp in seconds.

These functions provide additional functionality beyond basic data querying and manipulation in CQL. By using these functions in conjunction with standard CQL commands, developers and administrators can create more powerful and sophisticated queries and data processing pipelines in Cassandra.