I was just playing around with Cassandra WRITETIME and thought it was somewhat difficult to figure out the date / timestamp of a number like this (microseconds since EPOC) 1470645914253000.
So in my example it looked like this
|
1 2 3 4 5 6 7 8 9 10 11 |
cqlsh:bth> select id, writetime(dateofbirth) from bth.employee; id | writetime(dateofbirth) ----+------------------------ 1 | 1470645914253000 2 | 1470645977177000 7 | 1470948508799001 3 | 1470645977178000 (4 rows) cqlsh:bth> |
So I figured why not create a UDF that would solve this for me
That turned out to be a little bit of a challenge …
I thought that I could do like this
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE FUNCTION bth.ts2date ( input bigint ) RETURNS NULL ON NULL INPUT RETURNS text LANGUAGE java AS $$ if( input > 0L ) { long ms = input / 1000L; Date date=new Date(ms); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss,SSS"); return sdf.format(date); } else return null; $$; |
BUT NO, YOU CAN NOT!!!
There are several WRONGS in here it turns out
- First off you have to turn on
enable_user_defined_functions: true
in the conf/cassandra.yaml file - All classes has to be fully qualified, so Date would be java.util.Date, and so on…
- The division operator ‘/’ can not be used !!! however +,- and * works fine. surely this must be a bug … this called for some thinking…
The error I got when trying to use the code above without fully qualified names was
|
1 2 3 4 5 6 7 8 |
cqlsh> CREATE FUNCTION ts2date ( input bigint ) ... RETURNS NULL ON NULL INPUT ... RETURNS text ... LANGUAGE java ... AS $$ ... if( input != null ) { ... Date date=new Date(mills); InvalidRequest: code=2200 [Invalid query] message="Functions must be fully qualified with a keyspace name if a keyspace is not set for the session" |
And the reason, if I got it right, is that you can not do imports.
The error I got when trying to use the division ‘/’ operator was this:
|
1 2 3 4 5 6 7 8 9 10 11 |
cqlsh:bth> CREATE FUNCTION bth.ts2date ( input bigint ) ... RETURNS NULL ON NULL INPUT ... RETURNS text ... LANGUAGE java ... AS $$ ... if( input > 0L ) { ... java.util.Date date=new java.util.Date(input/1000); Invalid syntax at line 7, char 49 java.util.Date date=new java.util.Date(input/1000); ^ cqlsh:bth> |
The code that works looks like this, using java.math.BigDecimal to solve it was perhaps a so-so solution, but it works:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE FUNCTION bth.ts2date ( input bigint ) RETURNS NULL ON NULL INPUT RETURNS text LANGUAGE java AS $$ if( input > 0L ) { java.math.BigDecimal t = java.math.BigDecimal.valueOf(1000L); java.math.BigDecimal inp = java.math.BigDecimal.valueOf(input); java.math.BigDecimal mst = inp.divide(t); long ms = mst.longValue(); java.util.Date date=new java.util.Date(ms); java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss,SSS"); return sdf.format(date); } else return null; $$; |
So now my output in cqlsh.sh looks like this now
|
1 2 3 4 5 6 7 8 9 10 11 |
cqlsh:bth> select id, ts2date(writetime(dateofbirth)) from bth.employee; id | bth.ts2date(writetime(dateofbirth)) ----+------------------------------------- 1 | 2016-08-08 10:45:14,253 2 | 2016-08-08 10:46:17,177 7 | 2016-08-11 22:48:28,799 3 | 2016-08-08 10:46:17,178 (4 rows) cqlsh:bth> |
That is a lot better !
Thanks for each and every post this year. While I rarely comment, yours is one blog that I always read. until when will you become a writer?
It is a pleasure to read this weblog, thanks to its up-to-date information and interesting posts. Look into my web page YH9 for some really good points and find out more about Thai-Massage.