Cassandra : CQLSH not displaying correct timezone

Question

I have a table macrecord in cassandra as follows :

 macadd            | position | record | rssi1 | timestamp
-------------------+----------+--------+-------+---------------------
 D8:C7:C8:45:52:20 |       21 |     25 |     0 | 2015-09-25 08:41:00+0000

I am inserting timestamp values using dateof(now()) however the problem is CQLSH is just displaying the default UTC time and not adding the timezone offet +0800 in my case. How can i display the correct timezone. I am on ubuntu 14.04 and my system time is correct.

Answer 1

Ok, here's the rub on this one. CASSANDRA-10000 fixed some issues with date formatting in Windows. But since each platform has its own way of keeping track of time zone offsets, it was difficult to make cqlsh work for Windows without including some additional dependencies for timezone display. Therefore the path of least resistance was chosen, and now ALL platforms show queried timestamps in UTC.

I am going to create a JIRA ticket to address this, and get proper timezone display back for both environments (hopefully).

USE THIS FIX AT YOUR OWN RISK

If you feel comfortable trying it, here is how I fixed this for myself:

In $CASSANDRA_HOME/pylib/cqlshlib/formatting.py, look for the strftime method definition. The new one (as of 2.2.1) contains only two lines:

def strftime(time_format, seconds):
    tzless_dt = datetime_from_timestamp(seconds)
    return tzless_dt.replace(tzinfo=UTC()).strftime(time_format)

You can clearly see where the UTC timezone is being forced. Essentially, just comment-out (or remove) those lines, and replace them with the strftime method definition from Cassandra <= (2.2.0 || 2.1.8). In case you don't have it, here is the code:

def strftime(time_format, seconds):
    local = time.localtime(seconds)
    formatted = time.strftime(time_format, local)
    if local.tm_isdst != 0:
        offset = -time.altzone
    else:
        offset = -time.timezone
    if formatted[-4:] != '0000' or time_format[-2:] != '%z' or offset == 0:
        return formatted
    # deal with %z on platforms where it isn't supported. see CASSANDRA-4746.
    if offset < 0:
        sign = '-'
    else:
        sign = '+'
    hours, minutes = divmod(abs(offset) / 60, 60)
    return formatted[:-5] + sign + '{0:0=2}{1:0=2}'.format(hours, minutes)

I have tested this code with the above situation, but certainly not to an exhaustive level. Like I said, use at your own risk. But this should hold you over until a patch gets written to restore this functionality.

EDIT 20161021

This was fixed in CASSANDRA-10397 for versions 2.2.6, 3.0.4, and 3.4.

Comments

Here's a link to the original answer on Stack Overflow,