Cassandra Datetime Operators

“No matter which database paradigm you choose, you will come across an instance where you need to work with dates and date data types. It is, therefore, to be familiar with the supported date types for your database and how to use them.

This tutorial will cover the basics of date data types in Apache Cassandra, the supported format and how to insert them into a Cassandra table.”

Cassandra Date Data Types

There are five main data types when working with Cassandra date and time values. These data types include:

date
time
timestamp
duration
DateRangeType

Date Data Type

As the name suggests, the date type stores a date value. It is a 32-bit integer representing the number of days elapsed since the epoch.

The date type format follows the format as shown:

yyyy-mm-dd

Where:

Yyyy – represents the four digits of a year
Mm – two digits representing the month
dd – shows the date

For example, the following snippet shows a correct and incorrect date type in Cassandra:

# valid
2022-10-10
# invalid
10-10-2022

Time Data Type

The time data type is a 64-bit encoded signed integer that is used to represent the number of nanoseconds since midnight.

The format for a time data type is as shown:

HH:MM:SS[.fff]

Where the:

HH – are the two digits representing the 24-hour clock
MM – two numbers showing the minutes
SS – shows the number of seconds
Fff – are up to three digits representing the number of sub-seconds

The following shows valid time data types:

17:13:34:300
17:13:34

Timestamp Data Type

The timestamp data is used to represent the date and time since the epoch. This is an encoded 64-bit signed integer.

yyyy-mm-dd[(T| )HH:MM:SS[.fff]][(+|-)NNNN]

The value of a timestamp is comprised of three main parts:

Date – yyyy-mm-dd
Time – HH:MM:SS[.fff]
Timezone – (+|- NNNN)

The date section of the timestamp is required. The other two are optional.

The following example shows valid timestamp values:

2022-10-10 17:13:34:300+0300
2022-10-10 17:13:34.300
2022-10-10 17:13:34
2022-10-10

Duration Data Type

The duration data type represents the time duration encoded as a signed integer of variable length.

The integers are:

Months
Days
Nanoseconds

DateRange Type

This data type is used to store the range between two dates. This data type follows the format as shown below:

'[beginning_date TO end_date]'

Example Cassandra Date and Time Types Schema

The following code shows how to define various date and time data types in a Cassandra table.

CREATE TABLE time_testing(
id uuid,
date_type date,
date_range_type date,
duration_type duration,
time_type time,
timestamp_type timestamp,
PRIMARY KEY(id)
);

In the example above, we define the various five columns, with each representing a specific date or data type.

NOTE: If you use the DateRangeType, you need to define the schema type as a date. You can then insert a Date value as shown in the insert statement below:

INSERT INTO time_testing(id, date_type, date_range_type, duration_type, time_type, timestamp_type)
VALUES (8369ae12-a0a9-491e-bdd4-2b4ebed5d705, '2022-10-10', '2022-10-10', 24h10m38s, '05:13:34', '2022-10-10 17:13:34.300+0300');

The query above should insert the various data types into the specified table. The resulting records are as shown:

select * from time_testing;

The query above should return the table with all the time records.

Termination

This post covered the various date and time data types provided in Apache Cassandra. It is good to keep in mind that various adaptations of Cassandra may differ in the supported types. Check the documentation for your Cassandra version to learn more.

Happy coding!!