CQLSH: Converting unix timestamp to datetime

Question

I am performing a cql query on a column that stores the values as unix timestmap, but want the results to output as datetime. Is there a way to do this?

i.e. something like the following:

select convertToDateTime(column) from table;

Answer 1

I'm trying to remember if there's an easier, more direct route. But if you have a table with a UNIX timestamp and want to show it in a datetime format, you can combine the dateOf and min/maxTimeuuid functions together, like this:

aploetz@cqlsh:stackoverflow2> SELECT datetime,unixtime,dateof(mintimeuuid(unixtime)) FROM unixtime;

 datetimetext   | unixtime      | dateof(mintimeuuid(unixtime))
----------------+---------------+-------------------------------
     2015-07-08 | 1436380283051 |      2015-07-08 13:31:23-0500

(1 rows)
aploetz@cqlsh:stackoverflow2> SELECT datetime,unixtime,dateof(maxtimeuuid(unixtime)) FROM unixtime;

 datetimetext   | unixtime      | dateof(maxtimeuuid(unixtime))
----------------+---------------+-------------------------------
     2015-07-08 | 1436380283051 |      2015-07-08 13:31:23-0500

(1 rows)

Note that timeuuid stores greater precision than either a UNIX timestamp or a datetime, so you'll need to first convert it to a TimeUUID using either the min or maxtimeuuid function. Then you'll be able to use dateof to convert it to a datetime timestamp.