How to insert a datetime into a Cassandra 1.2 timestamp column

Question

IMPORTANT If you are dealing with this problem today, use the new cassandra-driver from datastax (i.e. import cassandra) since it solves most of this common problems and don't use the old cql driver anymore, it is obsolete! This question is old from before the new driver was even in development and we had to use an incomplete old library called cql (import cql <-- don't use this anymore, move to the new driver).

Intro I'm using the python library cql to access a Cassandra 1.2 database. In the database I have a table with a timestamp column and in my Python code I have a datetime to be inserted in the column. Example as follows:

Table

CREATE TABLE test (
     id text PRIMARY KEY,
     last_sent timestamp
);

The code

import cql
import datetime
...
cql_statement = "update test set last_sent = :last_sent where id =:id"
rename_dict = {}
rename_dict['id'] = 'someid'
rename_dict['last_sent'] = datetime.datetime.now()
cursor.execute (cql_statement, rename_dict)

The problem

When I execute the code the actual cql statement executed is like this:

update test set last_sent =2013-05-13 15:12:51 where id = 'someid'

Then it fails with an error

 Bad Request: line 1:XX missing EOF at '-05'

The problem seems to be that the cql library is not escaping ('') or converting the datetime before running the query.

The question What is the correct way of doing this without manually escaping the date and be able to store a full timestamp with more precision into a cassandra timestamp column?

Thanks in advance!

Answer 1

I can tell you how to do it in cqlsh. Try this

update test set last_sent =1368438171000 where id = 'someid'

Equivalent long value for date time 2013-05-13 15:12:51 is 1368438171000