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!

score 10 · Answer 1 · answered May 14 '13 at 05:34

10

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

answered May 14 '13 at 05:34

abhi

4,762
4
29
49

1

Thanks abhi, this is exactly what I'm asking. I'll be posting a full answer in a few. – Sergio Ayestarán May 14 '13 at 15:54

score 6 · Accepted Answer · edited May 23 '17 at 12:25

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

Why not: from time import time; last_sent = time() * 1000; This seems like a roundabout way of achieving something similar. Am I missing something? — Priyeshj, May 18 '18 at 00:25

score 5 · Answer 3 · answered Sep 19 '13 at 15:15

5

Well for me it works directly with

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

No need to convert something. So in Python you can do it using the datetime value as a string:

cursor.execute("UPDATE test SET ts=:ts WHERE id=:id;",
    dict(ts=your_datetime.isoformat(), id=id))

answered Sep 19 '13 at 15:15

webjunkie

6,891
7
46
43

That wasn't working when this question was posted, and that's why I asked. Can you provide more info about the version of the cql library you are using? – Sergio Ayestarán Oct 07 '13 at 17:39
Cassandra 2 with CQL3, sorry for not mentioning this, just wanted to post this method here for others. – webjunkie Oct 08 '13 at 09:47
It's ok, the problem is that your answer is for a different question, the update you wrote on the 1st code block is meant to be used on cqlsh and is a CQL (Cassandra Query Language) statement, the question is about doing that query from python using the python cql library (not the cassandra query language). – Sergio Ayestarán Oct 24 '13 at 20:32

score 1 · Answer 4 · answered Oct 21 '15 at 08:03

1

Most of the solutions are valid, I just want to suggest a simpler one:

from datetime import datetime

my_date = int(float(datetime.now().strftime("%s.%f"))) * 1000

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

answered Oct 21 '15 at 08:03

Patrick

1,091
1
14
14

Any type of conversion of the date to long is valid yes, so it should work too. Still for the sake of readers just use the new cassandra-driver from datastax since it solves most of this common problems and don't use the old cql driver anymore, it is obsolete! – Sergio Ayestarán Oct 21 '15 at 22:13

score 1 · Answer 5 · answered Apr 25 '16 at 00:39

1

I know it is a 2 year old question, but if anyone comes looking for an answer, use a datetime instance instead of using timestamp. Python driver should smartly handle an integer/float, though.

answered Apr 25 '16 at 00:39

zonked.zonda

347
1
5
19

Yes zonked, the question is from before the new python driver from datastax even existed (that explains the import cql in the code). Still, the timestamp in the question refers to the Cassandra type timestamp, there is no datetime type in cassandra. And as you can see the python code is already using datetime. So your answer should be "use the new driver". Still a valid point tho – Sergio Ayestarán Apr 25 '16 at 17:08

How to insert a datetime into a Cassandra 1.2 timestamp column

5 Answers5