CODEWITHSUNDEEP
cassandracqlcql3
Hitesh
Hitesh

Reputation: 3498

Get current date in cassandra cql select

In SQL, I am able to do:

select getdate(), getdate() - 7

Which returns the current date as well as current date - 7 days. I want to achieve the same in Cassandra CQL. I tried:

select dateof(now())

But that does not work. It works only on insert and not in select. How can I get the same? Any help would be appreciated.

Upvotes: 21

Views: 35692

Answers (3)

Pavel Orekhov
Pavel Orekhov

Reputation: 2177

From these docs: https://docs.datastax.com/en/cql/dse/docs/developing/inserting/upsert-date.html

You have to use this: toDate(now())

Upvotes: 1

Ian Goldby
Ian Goldby

Reputation: 6174

Another possible solution that will work on any table you have read access to regardless of whether it has one row, many rows, or even none at all:

SELECT toTimestamp(now()) AS now, count(*) FROM any_table WHERE partition_key=xyz;

The difference here is that the aggregate function COUNT(*) guarantees that the result set will always contain exactly one row.

Obviously, replace any_table with a table that you have read access to, and the WHERE clause with something that fully-specifies the table partition key. Again, there don't need to be any actual rows matching the specified value(s) for the partition key so any value(s) of the right type can be hard-coded.

Update

If the table has many rows, then count(*) will take a long time to complete and may even time out, so use this method with caution.

Aside

dateof() was deprecated in Cassandra 2.2.0-rc2. For later versions you should replace its use with toTimestamp().

Upvotes: 0

Aaron
Aaron

Reputation: 57758

select dateof(now())

On its own, you are correct, that does not work. But if you have a table that you know only has one row (like system.local):

aploetz@cqlsh:stackoverflow> SELECT dateof(now()) FROM system.local ;

 dateof(now())
--------------------------
 2015-03-26 03:18:39-0500

(1 rows)

Unfortunately, Cassandra CQL does not (yet? CASSANDRA-5505) include support for arithmetic operations, let alone date arithmetic. So subtracting 7 days from that value is something that you would have to do in your application level.

Edit 20200422

The newer syntax uses the toTimestamp() function instead:

aploetz@cqlsh> SELECT toTimestamp(now()) FROM system.local;

 system.totimestamp(system.now())
----------------------------------
  2020-04-22 13:22:04.752000+0000

(1 rows)

Both syntaxes work as of 20200422.

Upvotes: 43

Related Questions