Offsetting a timestamp on a Cassandra query

Question

Probably a dumb question but I'm using toTimestamp(now()) to retrieve the timestamp. Is there any way to offset the now() by my specified timeframe.

What I have now:

> print(session.execute('SELECT toTimestamp(now()) FROM system.local').one())
2022-12-04 12:12:47.011000

My goal:

> print(session.execute('SELECT toTimestamp(now() - 1h) FROM system.local').one())
2022-12-04 11:12:47.011000

Answer 1

If you are using Cassandra 4.0

You're on the right track.

But with the current example, it looks like you're trying to subtract an hour from now(). Now is a type-1 UUID (timeUUID in Cassandra). The date arithmetic operators will only work with dates and timestamps, so just pull that - 1h out one level of parens:

> SELECT toTimestamp(now()) - 1h FROM system.local;

 system.totimestamp(system.now()) - 1h
---------------------------------------
       2022-12-04 12:38:35.747000+0000

(1 rows)

And then this works:

row = session.execute("SELECT toTimestamp(now()) - 1h FROM system.local;").one()
if row:
    print(row[0])

2022-12-04 12:52:19.187000

NOTE: The parser is a little strict on this one. Make sure that the operator and duration are appropriately spaced.

This works:

SELECT toTimestamp(now()) - 1h

This fails:

SELECT toTimestamp(now())-1h

EDIT

If you are using Cassandra 3.11

If you're on Cassandra 3.11, you'll need these instructions as date/time arithmetic is new as of 4.0. However, this is still possible using a user defined function (UDF).

First, ensure that UDFs are enabled in the cassandra.yaml file (requires restart):

enable_user_defined_functions: true

Next, with cqlsh, create a UDF to apply an hour offset to the current time. I'll name this UDF hourOffset:

> CREATE OR REPLACE FUNCTION hourOffset (input TIMESTAMP, offset INT)
     RETURNS NULL ON NULL INPUT RETURNS TIMESTAMP
     LANGUAGE java AS '
     long lTime = input.getTime() + (3600000 * offset);
     return new Date(lTime);
     ';

This will now work:

> SELECT stackoverflow.hourOffset(toTimestamp(now()),-1) FROM system.local;

 stackoverflow.houroffset(system.totimestamp(system.now()), -1)
----------------------------------------------------------------
                                2022-12-05 15:52:21.390000+0000

(1 rows)

That UDF works from within your Python code, too:

rows = session.execute("SELECT stackoverflow.hourOffset(toTimestamp(now()),-1) FROM system.local;")
for row in rows:
    print(row[0])