Offsetting a timestamp on a Cassandra query-CodePudding

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

CodePudding user response：

To offset the timestamp returned by the toTimestamp(now()) function in Apache Cassandra, you can use the dateOf function to subtract a specified amount of time from the current timestamp.

Here is an example of how you can use this query in your code:

result = session.execute('SELECT toTimestamp(dateOf(now()) - 1h) FROM system.local').one()

print(result)

You can use the same syntax to offset the timestamp by any amount of time (like 1d for 1 day).

CodePudding user response：

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