TimeUUID vs timestamp in Cassandra?

Question

If we can extract time from TimeUUID, does it make any sense to use timestamp as another column in Cassandra?

Also how can we extract Time from TimeUUID and make range queries (eg. Jan 2016 to May 2016) on it?

Answer 1

From https://cassandra.apache.org/doc/4.0/cassandra/cql/cql_singlefile.html#timeuuidFun:

Timeuuid functions

now

The now function takes no arguments and generates, on the coordinator node, a new unique timeuuid (at the time where the statement using it is executed). Note that this method is useful for insertion but is largely non-sensical in WHERE clauses. For instance, a query of the form

SELECT * FROM myTable WHERE t = now()

will never return any result by design, since the value returned by now() is guaranteed to be unique.

minTimeuuid and maxTimeuuid

The minTimeuuid (resp. maxTimeuuid) function takes a timestamp value t (which can be either a timestamp or a date string) and return a fake timeuuid corresponding to the smallest (resp. biggest) possible timeuuid having for timestamp t. So for instance:

SELECT * FROM myTable WHERE t > maxTimeuuid('2013-01-01 00:05+0000') AND t < minTimeuuid('2013-02-02 10:00+0000')

will select all rows where the timeuuid column t is strictly older than 2013-01-01 00:05+0000 but strictly younger than 2013-02-02 10:00+0000. Please note that t >= maxTimeuuid('2013-01-01 00:05+0000') would still not select a timeuuid generated exactly at 2013-01-01 00:05+0000 and is essentially equivalent to t > maxTimeuuid('2013-01-01 00:05+0000').

Warning: We called the values generated by minTimeuuid and maxTimeuuid fake UUID because they do no respect the Time-Based UUID generation process specified by the RFC 4122. In particular, the value returned by these 2 methods will not be unique. This means you should only use those methods for querying (as in the example above). Inserting the result of those methods is almost certainly a bad idea.

Time conversion functions

A number of functions are provided to “convert” a timeuuid, a timestamp or a date into another native type.

Answer 2

I had this problem and converted during the query.

SELECT toTimestamp(uuid_timestamp) FROM table

It was not a problem because I was inserting into a panda dataframe for analysis.

Answer 3

You don't need to store timestamp in a different column, if you have chosen to use timeuuid.

You are looking for this:
https://docs.datastax.com/en/cql/3.0/cql/cql_reference/timeuuid_functions_r.html

Please take notice about the min and max. The LSB bits determine the uniqueness. i.e last 8 bits can range from 00000000 to ffffffff.

So please prepare your range query accordingly.