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

Answer 2

Has abhi already stated this can be done using the milliseconds since epoch as a long value from cqlsh, now we need to make it work in the Python code.

When using the cql library this conversion (from datetime to milliseconds since epoch) is not happening so in order to make the update work and still have the precision you need to convert the datetime to milliseconds since epoch.

Source Using this useful question: Getting millis since epoch from datetime , in particular this functions(note the little change I made):

The solution

import datetime

def unix_time(dt):
    epoch = datetime.datetime.utcfromtimestamp(0)
    delta = dt - epoch
    return delta.total_seconds()

def unix_time_millis(dt):
    return long(unix_time(dt) * 1000.0)

For this example the code would be:

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

You can convert the datetime to a long value containing the number of milliseconds since epoch and that's all, the update is transformed to an equivalent form using a long value for the timestamp.

Hope it helps somebody else