I know Cassandra stores data in UTC time in a timestamp column but I need it to store the timestamp like the following (UTC+2, Central European Time):
2023-10-29 00:00:00.000000+0200
2023-10-29 01:00:00.000000+0200
2023-10-29 02:00:00.000000+0200
2023-10-29 02:00:00.000000+0100
...
2023-10-29 23:00:00.000000+0200
As you can see, there are 25 hours for the 29th of October 2023 and 23 hours in March 2023.
When transferred to Cassandra the timestamps above become:
2023-10-28 22:00:00.000000+0000
2023-10-28 23:00:00.000000+0000
2023-10-29 00:00:00.000000+0000
2023-10-29 01:00:00.000000+0000
...
2023-10-29 21:00:00.000000+0000
which is not the desired result.
Because I need to do queries when selecting the data, for example, select * FROM series_op_test WHERE as_of='2022-09-30' AND name='LU_STC' AND time < '2023-10-30' AND time >= '2023-10-28';.
And, if possible, I don't want to do any programmatically changes/conversion before or/and after selecting the data and when inserting the data through the API.
Any ideas ?
The CQL timestamp data type is encoded as the number of milliseconds since Unix epoch (Jan 1, 1970 00:00 GMT) so it is incorrect to say:
... Cassandra stores data in UTC time in a timestamp column ...
For example, 2023-10-29 00:00:00.000000+0200 (in CET) is encoded as 1698530400000 milliseconds. In cqlsh, it is displayed in UTC as:
2023-10-28 22:00:00.000000+0000
because it (cqlsh) defaults to displaying timestamps in UTC. You can override the default and display timestamps in another timezone using the pytz library.
To answer your question directly, you cannot store the date/time "in CET" in a timestamp column but you can store timestamps in CET with +0200 and it will be encoded to the equivalent milliseconds since epoch. Cheers!