Insertion and retrieval of timestamp into Cassandra CQL3 based table in Python

Question

I have the following table created using CQL3 in Cassandra:

    CREATE TABLE user_log (
        user_id text,
        zone_id text,
        freq int,
        time timestamp,
        PRIMARY KEY (user_id, zone_id)
    )

I insert some values into this table using datastax Cassandra-Python driver like this :

    fmt = '%m/%d/%Y %H:%M'
    t1 = datetime.strptime("01/01/2000 22:05", fmt)
    prepared = session.prepare("""
                    INSERT INTO user_log (user_id,zone_id,freq,time) VALUES (?,?,?,?)
                    """)
    session.execute_async(prepared.bind((str(Uid),str(zone_id),int(freq),t1 )))

Insertion goes fine but when I try to retrieve the values it throws an exception while retrieving the time values. Retrieval of all other columns works fine. Select * also throws exception.

Please elaborate a bit. Did you try executing `select * from user_log` in cqlsh? What exactly is the exception message? — John, Jul 30 '13 at 07:43
well, its strange. i was dumping the retrieved result into json and it would throw an exception. when i tried printing it to the console, it worked fine. any idea of what could be the reason. The retrieved timestamp is comma separated. time=datetime.datetime(2013, 2, 5, 7, 37) — Sayed Jalil Hassan, Jul 30 '13 at 08:08
it seems like the time stamp is not getting dumped properly into json. otherwise its working fine. — Sayed Jalil Hassan, Jul 30 '13 at 08:31
JSON doesn't have a date/time/timestamp type so usually JSON libraries will error when trying to convert a native date/time/timestamp. But many JSON libraries allow you to provide your custom encoder/decoder. — Alex Popescu, Aug 03 '13 at 19:57

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

If you are having trouble inserting and retrieving timestamps in cassandra, you are not alone, see this: How to insert a datetime into a Cassandra 1.2 timestamp column

The solution might be to calculate the unix timestamp (the elapsed ms since epoch) from your datetime, and insert/retrieve this instead.

In general, it would help writing a helpful answer, if you exactly stated the error message (if any)? Also, what line of code exactly is causing the error?

edited May 23 '17 at 12:06

Community

1
1

answered Jul 30 '13 at 09:02

John

1,462
10
17

Timestamp isn't Json Serializer.Thats why it would throw an exception. – Sayed Jalil Hassan Jul 31 '13 at 11:20

score 1 · Answer 2 · answered Mar 23 '14 at 01:50

import json
import uuid

class UUIDEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, uuid.UUID):
            return obj.hex
        return json.JSONEncoder.default(self, obj)

j = json.dumps( invar, indent=4, separators=(',', ': '), cls=UUIDEncoder )

Insertion and retrieval of timestamp into Cassandra CQL3 based table in Python

2 Answers2