How can I convert a CQL bigint type to date?-CodePudding

I am using Cassandra and I have a column of data that represents epoch time milliseconds stored as a bigint type:

request_time bigint

I would like to convert this to a date time object in order to work with certain days. How can I achieve that conversion?

I have tried:

select todate(request_time)

# InvalidRequest: Error from server: code=2200 [Invalid query] message="Input type Int64 is not supported in ToDate."

and also:

select dateof(mintimeuuid(request_time))

# InvalidRequest: Error from server: code=2200 [Invalid query] message="Unable to coerce Int64 into type CassandraTimestamp"

My Cassandra version is:

[cqlsh 5.0.1 | Cassandra 3.11.0 | CQL spec 3.4.4 | Native protocol v4]

CodePudding user response：

Use function -

todate()

Example below -

select request_time from testkspc.test;

 request_time
---------------
 123344345
 123344687
 123456329
 223344687
 1613808000000
 323344687
 123456789
 123344329
 423344687
 523344687

select todate(request_time)  from testkspc.test ;

 system.todate(request_time)
-----------------------------
                  1970-01-02
                  1970-01-02
                  1970-01-02
                  1970-01-03
                  2021-02-20
                  1970-01-04
                  1970-01-02
                  1970-01-02
                  1970-01-05
                  1970-01-07

Further you can use totimestamp function. Directly using this function on bigint type gives error -

select totimestamp(request_time)  from testkspc.test ;
InvalidRequest: Error from server: code=2200 [Invalid query] message="Invalid call to function totimestamp, none of its type signatures match (known type signatures: system.totimestamp : (date) -> timestamp, system.totimestamp : (timeuuid) -> timestamp)"

We can wrap todate inside totimestamp.

select totimestamp(todate(request_time))  from testkspc.test ;

 system.totimestamp(system.todate(request_time))
-------------------------------------------------
                 1970-01-02 00:00:00.000000 0000
                 1970-01-02 00:00:00.000000 0000
                 1970-01-02 00:00:00.000000 0000
                 1970-01-03 00:00:00.000000 0000
                 2021-02-20 00:00:00.000000 0000
                 1970-01-04 00:00:00.000000 0000
                 1970-01-02 00:00:00.000000 0000
                 1970-01-02 00:00:00.000000 0000
                 1970-01-05 00:00:00.000000 0000
                 1970-01-07 00:00:00.000000 0000

CodePudding user response：

You had the right idea by using the built-in CQL function toDate().

I've deployed a Cassandra 3.11.0 cluster (the same as the version you're running) and can confirm that it works. Here's an example output:

cqlsh:stackoverflow> SELECT request_time, todate(request_time) FROM testbigint ;

 request_time  | system.todate(request_time)
--------------- -----------------------------
 1654671885000 |                  2022-06-08
          1654 |                  1970-01-01

In your case, I believe the underlying issue is that your table contains invalid data in the request_time column. You need to check the data in your table and try again.

As a side note, Apache Cassandra 3.11.0 was released 5 years ago and you shouldn't even bother using it for testing. At the time of writing, the current supported version is C* 3.11.13. Cheers!