Is there a way to convert the value returned from writetime() into a readable date in Scylla CQL?

Question

I am looking for an easy way in CQL to show a human readable form of the timestamp returned by writetime(). I could not find anything googling, is that possible at all?

Answer 1

In theory, the timestamp used in writes in CQL (ScyllaDB and Cassandra) is just a number - with higher numbers indicating newer updates so they win in conflict resolution. Again in theory, a CQL update can specify any random and meaningless number as the timestamp by adding a "USING TIMESTAMP" option on the write.

However, as a convention, we almost always use as a timestamp the number of microseconds since the UNIX epoch (January 1st, 1970). This convention is enforced when the server is asked to pick a timestamp (this also includes the case of LWT), and also by library implementations (that use the current time to calculate a client-side timestam), so for all intents and purposes you can assume that the timetamps you will read with writetime() in your applications will be like that - number of microseconds since the epoch.

You can convert these numbers to other formats using whatever tools exist in your application language to convert date formats. For example in Python:

>>> writetime=1699782709000000
>>> from time import strftime, gmtime
>>> strftime('%Y-%m-%d %H:%M:%S', gmtime(writetime/1e6))
'2023-11-12 09:51:49'

Note how we divided writetime by 1e6 (i.e., 1 million) to convert microseconds to seconds since the epoch, then used gmtime() to say that this number uses the GMT (a.k.a. UTC) timezone, not the local one on your computer, and finally strftime() to convert this into a date string in your choice of format.