question

DavidN avatar image
DavidN asked DavidN commented

What is the correct syntax for the currentDate() function?

Good Day,

I am having a look at the Cassandra documentation at this link and wanted some clarity on the currentDate function and the example used Untitled | Apache Cassandra Documentation

For example the last two days of data can be retrieved using:

SELECT * FROM myTable WHERE date >= currentDate() - 2d;

I have the following questions based on the above synatx:

1. I am trying to populate a table with lots of data via a pipeline and wanted to query the last 3 days of data from the table using the following syntax example

SELECT * FROM myTable WHERE date >= currentDate() - 3d;

Would the above syntax be correct?

2. Supposing I wanted to query data over a period of time in hours, is there any function I could use to do this? Based on the documentation from the same link above, there is a currentTime() function and I am not sure if this would be ideal to use in this context.

Any help would be greatly appreciated.

Regards,

David.

cql
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered DavidN commented

You haven't specified the table schema so its hard to say what would work in your case. Ideally when posting questions, you should provide examples of what you have already tried.

Here's an example table that has a CQL date column as the clustering key:

CREATE TABLE community.table_by_id (
    id int,
    datecol date,
    colour text,
    PRIMARY KEY (id, datecol)
)

The table contains one partition with multiple rows:

 id | datecol    | colour
----+------------+--------
  1 | 2021-09-01 | violet
  1 | 2021-09-02 | indigo
  1 | 2021-09-03 |   blue
  1 | 2021-09-04 |  green
  1 | 2021-09-05 | yellow
  1 | 2021-09-06 | orange
  1 | 2021-09-07 |    red

Here's a sample range filter on the clustering column:

cqlsh:getvaxxed> SELECT * FROM table_by_id WHERE id = 1 AND datecol >= currentDate() - 2d;

 id | datecol    | colour
----+------------+--------
  1 | 2021-09-05 | yellow
  1 | 2021-09-06 | orange
  1 | 2021-09-07 |    red

You can use the equivalent time function on columns which have CQL type time. Cheers!

1 comment Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

DavidN avatar image DavidN commented ·

Hi Erick,

Thanks for the response, I think that answered my questions. Sorry I didn't include the sample table I was working on initially, but your example clarified my questions.

I will try using your sample code as a basis to my queries.

Kind Regards,

David.

0 Likes 0 ·