Cassandra cqlsh – how to show microseconds/milliseconds for timestamp columns?

by

In an effort to answer your questions, I did a little digging on this one.

  1. Does Cassandra capture microseconds with timestamp data type?

Microseconds no, milliseconds yes. If I create your table, insert a row, and try to query it by the truncated time, it doesn’t work:

aploetz@cqlsh:stackoverflow> INSERT INTO data (datetime, id, type, data) 
VALUES ('2015-02-16T18:00:03.234+00:00','B26354','Blade Runner','Deckard- Filed and monitored.');
aploetz@cqlsh:stackoverflow> SELECT * FROM data 
WHERE id='B26354' AND type="Blade Runner" AND datetime="2015-02-16 12:00:03-0600";

 id | type | datetime | data
----+------+----------+------

(0 rows)

But when I query for the same id and type values while specifying milliseconds:

aploetz@cqlsh:stackoverflow> SELECT * FROM data 
WHERE id='B26354' AND type="Blade Runner" AND datetime="2015-02-16 12:00:03.234-0600";

 id     | type         | datetime                 | data
--------+--------------+--------------------------+-------------------------------
 B26354 | Blade Runner | 2015-02-16 12:00:03-0600 | Deckard- Filed and monitored.

(1 rows)

So the milliseconds are definitely there. There was a JIRA ticket created for this issue (CASSANDRA-5870), but it was resolved as “Won’t Fix.”

  1. How can I see that with cqlsh to verify?

One possible way to actually verify that the milliseconds are indeed there, is to nest the timestampAsBlob() function inside of blobAsBigint(), like this:

aploetz@cqlsh:stackoverflow> SELECT id, type, blobAsBigint(timestampAsBlob(datetime)), 
data FROM data;

 id     | type         | blobAsBigint(timestampAsBlob(datetime)) | data
--------+--------------+-----------------------------------------+-------------------------------
 B26354 | Blade Runner |                           1424109603234 | Deckard- Filed and monitored.

(1 rows)

While not optimal, here you can clearly see the millisecond value of “234” on the very end. This becomes even more apparent if I add a row for the same timestamp, but without milliseconds:

aploetz@cqlsh:stackoverflow> INSERT INTO data (id, type, datetime, data)
VALUES ('B25881','Blade Runner','2015-02-16T18:00:03+00:00','Holden- Fine as long as nobody unplugs him.');
aploetz@cqlsh:stackoverflow> SELECT id, type, blobAsBigint(timestampAsBlob(datetime)), 
                 ...     data FROM data;

 id     | type         | blobAsBigint(timestampAsBlob(datetime)) | data
--------+--------------+-----------------------------------------+---------------------------------------------
 B25881 | Blade Runner |                           1424109603000 | Holden- Fine as long as nobody unplugs him.
 B26354 | Blade Runner |                           1424109603234 |               Deckard- Filed and monitored.

(2 rows)

More Related Contents:

  1. cassandra get all records in time range
  2. Cassandra cqlsh – connection refused
  3. How do secondary indexes work in Cassandra?
  4. How to do a join queries with 2 or more tables in cassandra cql
  5. Difference between partition key, composite key and clustering key in Cassandra?
  6. cqlsh connection error: ‘ref() does not take keyword arguments’
  7. Is the IN relation in Cassandra bad for queries?
  8. MAX(), DISTINCT and group by in Cassandra
  9. Inner Join in cassandra CQL
  10. How to create auto increment IDs in Cassandra
  11. Understand cassandra replication factor versus consistency level
  12. cassandra – Saved cluster name Test Cluster != configured name
  13. Cassandra has a limit of 2 billion cells per partition, but what’s a partition?
  14. java.util.Date format SSSSSS: if not microseconds what are the last 3 digits?
  15. How to run shell script file using nodejs?
  16. Java: Convert String to TimeStamp
  17. Group mysql query by 15 min intervals
  18. iPhone: How to get current milliseconds?
  19. Convert LocalDate to LocalDateTime or java.sql.Timestamp
  20. TaskSchedulerImpl: Initial job has not accepted any resources;
  21. How to convert a 13 digit Unix Timestamp to Date and time?
  22. How can I parse/format dates with LocalDateTime? (Java 8)
  23. Oracle: how to add minutes to a timestamp?
  24. Ping with timestamp on Windows CLI
  25. How do I alter a mysql table column defaults?
  26. How to convert “1985-02-07T00:00:00.000Z” (ISO8601) to a date value in Oracle?
  27. Python: reduce precision pandas timestamp dataframe
  28. com.datastax.driver.core.exceptions.InvalidQueryException: unconfigured table schema_keyspaces
  29. DateTime precision in NHibernate and support for DateTime2 in NHibernate SchemeExport
  30. Function that creates a timestamp in c#

Leave a Comment