Getting correct timestamp from cassandra using datastax python-driver

Question

I am retrieving timestamps from a table using the datastax python-driver. What I am trying to do is store the previously retrieved timestamp in a var and use it in the next query to retrieve a timestamp greater than the previous one. The query basically looks like this:

cqlsh> SELECT insert_time, message FROM cf WHERE message_key='q1' AND insert_time>'2013-10-30 10:32:44+0530' ORDER BY insert_time ASC LIMIT 1;


 insert_time              | message
--------------------------+----------------------------------
 2013-10-30 10:32:45+0530 | 83500612412011e3ab6c1c3e84abd9db

As you can see the timestamp from CQL is 2013-10-30 10:32:45+0530. But when i retrieve it via python-driver the results are different( I am executing the python query on a different system and not on any of the cass nodes ):

>>> from cassandra.cluster import Cluster
>>> c = Cluster( [10.60.60.2] )
>>> session = c.connect()
>>> q = "SELECT insert_time, message FROM cf WHERE message_key='q1' AND insert_time>'2013-10-30 10:32:44+0530' ORDER BY insert_time ASC LIMIT 1"
>>> rows = session.execute(q)
>>> print rows
[Row(insert_time=datetime.datetime(2013, 10, 30, 5, 2, 45, 4000), message=u'83500612412011e3ab6c1c3e84abd9db')]
>>> timestamp = rows[0][0]
>>> print t
2013-10-30 05:02:45.004000

As you can see the timestamp from python-driver is 2013-10-30 05:02:45.004000, which is different from the CQL one. Not only the time is different but the representation has changed. This cannot be used for comparing in subsequent queries.

QUESTIONS

1. What am I doing wrong while retrieving timestamps in python ?
2. Is there a way to output epoch time as int instead of the datetime format ?
3. Is this something to do with clock syncs or timezone related ?
4. Can anyone help me with this so that the python retrieved timestamps can be reused to compare against cass timestamps ?

Thanks in advance. Appreciate your help

SETUP

• single host machine running vms;
• cass sandbox- 3 headless vms running as single dc cluster;
• python code being executed from host machine;
• VMs date,time synchronized with host using ntp
• [cqlsh 4.0.0 | Cassandra 2.0.0 | CQL spec 3.1.0 | Thrift protocol 19.37.0]

Answer 1

It looks like cqlsh is displaying the timestamp in your local timezone (which is +0530). The python driver returns datetimes in UTC. For what it's worth, the data is stored in Cassandra as a unix timestamp, which doesn't have a concept of timezones.

My suggestion is that you always use UTC for datetimes until just before displaying it to the user.