How can I get just a part of the current date in Cassandra? In my particular case I need to get just the year.
I got to this for the moment select dateof(now()) from system.local;
But I could not find any function to get just the year in the documentation https://docs.datastax.com/en/dse/5.1/cql/cql/cql_reference/refCqlFunction.html#refCqlFunction__toTimestamp
I'm new with Cassandra so this maybe a silly question.
The safe way, would be to return a timestamp and parse-out the year client-side.
Natively, Cassandra does not have any functions that can help with this. However, you can write a user-defined function (UDF) to accomplish this:
First, user defined functions are disabled by default. You'll need to adjust that setting in your cassandra.yaml and restart your node(s).
enable_user_defined_functions=true
NOTE: This setting is defaulted this way for a reason. Although Cassandra 3.x has some safeguards in-place to protect against malicious code, it is a good idea to leave this turned-off unless both you need it and you know what you are doing. And even then, you'll want to keep an eye on UDFs that get defined.
Now I'll create my function using Java, from within cqlsh:
cassdba@cqlsh:stackoverflow> CREATE OR REPLACE FUNCTION year (input DATE)
RETURNS NULL ON NULL INPUT RETURNS TEXT
LANGUAGE java AS 'return input.toString().substring(0,4);';
Note that there are a number of ways (and types) to query the current date/time:
cassdba@cqlsh:stackoverflow> SELECT todate(now()) as date,
totimestamp(now()) as timestamp, now() as timeuuid FROm system.local;
date | timestamp | timeuuid
------------+---------------------------------+-------------------------------------
2017-12-20 | 2017-12-20 21:18:37.708000+0000 | 58167cc1-e5cb-11e7-9765-a98c427e8248
(1 rows)
To return just only year, I can call my year function on the todate(now()) column:
SELECT stackoverflow.year(todate(now())) as year FROm system.local;
year
------
2017
(1 rows)