19

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.

Alex Ott
  • 80,552
  • 8
  • 87
  • 132
Hitesh
  • 3,449
  • 8
  • 39
  • 57

2 Answers2

41
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.

Aaron
  • 55,518
  • 11
  • 116
  • 132
  • 1
    Thanks a lot, that worked. I did the subtraction thing on my application level. – Hitesh Apr 07 '15 at 05:41
  • 1
    @Aaron Huh. Apparently there's a direct [Chinese ripoff](http://www.developersite.org/101-191317-cassandra) 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. –  Apr 11 '17 at 05:15
  • @Aaron On another note, it's been two years. Do you know if this feature has been added to Cassandra? –  Apr 11 '17 at 05:18
  • 2
    @arbitrarystringofletters It seems basic arithmetic on dates, have been added to cassandra 4.0 in [CASSANDRA-11936](https://issues.apache.org/jira/browse/CASSANDRA-11936) – Mematematica May 11 '17 at 16:11
  • What is query beyound cassandra version 2.2.0 – indrajit narvekar Aug 30 '19 at 10:09
  • 1
    @indrajitnarvekar This same syntax works in my 4.0-SNAPSHOT build. – Aaron Aug 30 '19 at 15:39
1

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

Ian Goldby
  • 5,609
  • 1
  • 45
  • 81