Search code examples
cassandratimestamptimezonecqlsh

How can I fetch timestamp data in my timezone?

I am using Cassandra 3.11.13 and I have table with timestamp column. Where are my data stored in terms of +0 timezone, i.e. 2022-10-14 07:51:00.000000+0000, but I am hosting in Kazakhstan GMT+6 I want to export certain rows and certain period of time. When I am exporting into CSV, I am getting a file with timezone +0. I tried to query like select * from table_name where primary_key = 'smth' and timestamp > '2022-10-14T06:30:00+0600' and timestamp < '2022-10-14T23:59:59+0600', but it's changed nothing. Question is: How can I fetch timestamp with certain/correct timestamp?

Solution

  • The CQL timestamp data type is encoded as the number of milliseconds since Unix epoch (Jan 1, 1970 00:00 GMT) so its value is encoded in UTC timezone. Clients also display timestamps with a UTC timezone by default.

    If you want the data to be displayed in your timezone, you need to configure your app or client to a specific timezone. For example, you can configure cqlsh to use a different timezone by specifying it in the cqlshrc file:

    ;; Display timezone
timezone = Australia/Melbourne

    You can find a sample copy of cqlshrc here. Note that you will need to install the pytz Python library to use different timezones with cqlsh.

    For details, see Cassandra CQL shell. Cheers!