Get current date in cassandra cql select

Question

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.

Vaudevillian

Answer 1

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.

Tooth

Thanks a lot, that worked. I did the subtraction thing on my application level. – Vaudevillian

@Tooth Huh. Apparently there's a direct Chinese ripoff of StackOverflow (presumably for the sole purpose of serving ads). That or two other people happened to have a perfectly identical Q/A exchange. The things you learn. – Monophonic

@Tooth On another note, it's been two years. Do you know if this feature has been added to Cassandra? – Monophonic

@arbitrarystringofletters It seems basic arithmetic on dates, have been added to cassandra 4.0 in CASSANDRA-11936 – Mirabelle

What is query beyound cassandra version 2.2.0 – Eau

@indrajitnarvekar This same syntax works in my 4.0-SNAPSHOT build. – Tooth

Answer 2

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

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

Memo

Answer 3

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().

Breastsummer