ScyllaDB - [Invalid query] message="marshaling error: Milliseconds length exceeds expected (6)"

Question

I have a table with a column of type timestamp and when I insert a value, Scylla saves it with 3 zeros more.

According to Scylla Documentation (https://docs.scylladb.com/getting-started/types/#timestamps):

Timestamps can be input in CQL either using their value as an integer, or using a string that represents an ISO 8601 date. For instance, all of the values below are valid timestamp values for Mar 2, 2011, at 04:05:00 AM, GMT:

• 1299038700000
• '2011-02-03 04:05+0000'
• '2011-02-03 04:05:00+0000'
• '2011-02-03 04:05:00.000+0000'
• '2011-02-03T04:05+0000'
• '2011-02-03T04:05:00+0000'
• '2011-02-03T04:05:00.000+0000'

So when I create a table for example:

CREATE TABLE callers (phone text, timestamp timestamp, callID text, PRIMARY KEY(phone, timestamp));

And insert values into it:

INSERT INTO callers (phone, timestamp, callID) VALUES ('6978311845', 1299038700000, '123');
INSERT INTO callers (phone, timestamp, callID) VALUES ('6978311846', '2011-02-03 04:05+0000', '456');
INSERT INTO callers (phone, timestamp, callID) VALUES ('6978311847', '2011-02-03 04:05:00.000+0000', '789');

The SELECT * FROM callers; will show all timestamps with 3 zeros more after the dot:

 phone      | timestamp                       | callid
------------+---------------------------------+--------
 6978311847 | 2011-02-03 04:05:00.000000+0000 |    789
 6978311845 | 2011-03-02 04:05:00.000000+0000 |    123
 6978311846 | 2011-02-03 04:05:00.000000+0000 |    456

As a result when I try for example to delete a row:

DELETE FROM callers WHERE phone = '6978311845' AND timestamp = '2011-03-02 04:05:00.000000+0000';

An error occurs:

InvalidRequest: Error from server: code=2200 [Invalid query] message="marshaling error: unable to parse date '2011-03-02 04:05:00.000000+0000': marshaling error: Milliseconds length exceeds expected (6)"

How can I store timestamp without getting this error?

Answer 1

You have hr:min:sec.millisec -> Millisec can be up to 999/1000 so essentially that's what the error is saying.

The 3 INSERT statement you did are correct in terms of syntax.

The DELETE statement should be in the same format as the INSERT:

• AND timestamp = '2011-03-02 04:05:00.00+0000'
• AND timestamp = '2011-03-02 04:05:00.000+0000'
• AND timestamp = '2011-03-02 04:05:00+0000'
• AND timestamp = '2011-03-02 04:05:00'

The additional 000 that appear in the table are just a display issue.