I want to run an CQL query in Cassansdra DB by limiting the result by date, when i run a simple query without filtering i get the below result:
select * from bssapi.call_detail_records where subscription_id = '116377120' and year = 2020 and month = 3;
The result which i get is the below:
subscription_id | year | month | event_at | id | account_unit_type | b_number | balance_after | balance_before | balance_id | basic_service_code | billable_amount | billable_unit | billing_item__code | call_case | call_destination | call_destination_type | cell_id | created_at | customer_id | data_amount | dc_event | direction_type | duration | event_type | invoice_sequence_id | lac | mcc | msisdn | network_code | product_code | reference_price | roaming_indicator | service_category | service_type | subscription_type | technical_call_case | total_with_vat | transaction_amount
-----------------+------+-------+--------------------------+--------------------------------+-------------------+------------+---------------+----------------+------------+--------------------+-----------------+---------------+--------------------+-----------+------------------+-----------------------+----------+--------------------------+-------------+-------------+-------------+----------------+----------+------------+---------------------+------+-----+------------+--------------+--------------+-----------------+-------------------+------------------+--------------+-------------------+---------------------+----------------+--------------------
XXXXXXXX | 2020 | 3 | 2020-03-01 07:45:51+0000 | POSTPAIDÿ3489472270ÿ2020-03-01 | 0 | XXXXXXXX | 0.0 | 0.0 | null | 11 | 60 | seconds | BNATCALL | 0 | 066 | national | 2730 | 2020-03-01 07:58:48+0000 | XXXXXXXXXX| 0 | CALLNAT600R | MOC | 16 | CALL | 3626608 | 0A2A | 603 | XXXXXXXXX | DZAOT | null | 5.0 | 0 | null | 1 | XXXXXXX| 1011 | 0.0 | 0.0
What i want is to get the result only for one day, for example the result only for today "2020-03-19 07:45:51+0000", i tried several query but i am not able to get a correct result, for example i tried the below:
select * from bssapi.call_detail_records where subscription_id = '116377120' and created_at > '2020-03-19 00:00:00' allow filtering;
The error returned is:
InvalidRequest: code=2200 [Invalid query] message="Partition key part year must be restricted since preceding part is"
Hm, I think you just need to provide your requirement for the field event_at like this:
select *
from bssapi.call_detail_records
where subscription_id = '116377120'
and year = 2020
and month = 3
and event_at = '2020-03-19 07:45:51+0000';