Remove trailing zeroes of time datatype in CQL

Question

Trying to remove trailing zero of arrival_time, the column data type is Time

SELECT * FROM TABLE

And I got this:

station_name | arrival_time --------------+-------------------- Wellington | 06:05:00.000000000

and I need the result to look like this:

station_name | arrival_time --------------+-------------------- Wellington | 06:05:00

I'm new to CQL, Thanks in advance.

Answer 1

So you can't actually do that in Cassandra with the time type. You can however, do it with a timestamp.

cassdba@cqlsh:stackoverflow> CREATE TABLE arrival_time2 (station_name TEXT PRIMARY KEY, 
    arrival_time time, arrival_timestamp timestamp);
cassdba@cqlsh:stackoverflow> INSERT INTO arrival_time2 (station_name , arrival_time , arrival_timestamp)
    VALUES ('Wellington','06:05:00','2018-03-22 06:05:00');
cassdba@cqlsh:stackoverflow> SELECT * FROM arrival_time2;

 station_name | arrival_time       | arrival_timestamp
--------------+--------------------+---------------------------------
   Wellington | 06:05:00.000000000 | 2018-03-22 11:05:00.000000+0000

(1 rows)

Of course, this isn't what you want either, really. So next you need to set a time_format in the [ui] section of your ~/.cassandra/cqlshrc.

[ui]
time_format = %Y-%m-%d %H:%M:%S

Restart cqlsh, and this should work:

Connected to Test Cluster at 127.0.0.1:9042.
[cqlsh 5.0.1 | Cassandra 3.11.2 | CQL spec 3.4.4 | Native protocol v4]
Use HELP for help.
cassdba@cqlsh> SELECT station_name,arrival_timestamp
    FROm stackoverflow.arrival_time2 ;

 station_name | arrival_timestamp
--------------+---------------------
 Wellington | 2018-03-22 11:05:00

(1 rows)