CODEWITHSUNDEEP
spring-bootspring-data-cassandradatastax-astra
Tuhin Subhra Mandal
Tuhin Subhra Mandal

Reputation: 523

Timestamp or date column query issue cassandra 4 (astradb)

I am facing issue with querying cassandra (astradb) from springboot aplication.

Here are the details I am using:

'org.springframework.boot' version '2.6.8'

implementation 'org.springframework.boot:spring-boot-starter-data-cassandra'



//cassandra specific driver
    implementation 'com.datastax.astra:astra-spring-boot-starter:0.3.0'
    implementation 'com.datastax.oss:java-driver-core:4.14.1'
    implementation 'com.datastax.oss:java-driver-query-builder:4.14.1'
    implementation 'com.datastax.oss:java-driver-mapper-runtime:4.14.1'

In order to retrieve data I am using Cassandra Java jpa.

Here is my Repository:

  @Query(value = "select * from engine_torque_by_last_date " +
            "where vin_number = :vinNumber " +
            "and organization_id in :organizationId and " +
            "stats_date = totimestamp(:dateString)")
    List<EngineTorqueByLastDate> findByVinNumberAndOrganizationIdAndStatsDate(String vinNumber, Integer organizationId, String dateString);

Now In Pojo I have defined Attribute as TimeStamp.

Below is the error:

{
  "title": "Internal Server Error",
  "status": 500,
  "detail": "Query; CQL [select * from engine_torque_by_last_date where vin_number = ? and organization_id in ? and stats_date = totimestamp(?)]; Ambiguous call to function totimestamp (can be matched by following signatures: system.totimestamp : (timeuuid) -> timestamp, system.totimestamp : (date) -> timestamp): use type casts to disambiguate; nested exception is com.datastax.oss.driver.api.core.servererrors.InvalidQueryException: Ambiguous call to function totimestamp (can be matched by following signatures: system.totimestamp : (timeuuid) -> timestamp, system.totimestamp : (date) -> timestamp): use type casts to disambiguate",
  "cause": {
    "title": "Internal Server Error",
    "status": 500,
    "detail": "Ambiguous call to function totimestamp (can be matched by following signatures: system.totimestamp : (timeuuid) -> timestamp, system.totimestamp : (date) -> timestamp): use type casts to disambiguate"
  }
}

CREATE TABLE IF NOT EXISTS autonostixdq.engine_torque_by_last_date (
                                                                       id UUID,
                                                                       engine_torque text,
                                                                       vin_number text,
                                                                       last_updated timestamp,
                                                                       organization_id int,
                                                                       odometer int,
                                                                       stats_date timestamp,
                                                                       miles double,
                                                                       hours double,
                                                                       engine_runtime int,
                                                                       key_starts int,
                                                                       threshold text,
                                                                       PRIMARY KEY ((vin_number, organization_id), stats_date, odometer));

api takes date as a string '2022-05-27' then tried both. Passing just a string '2022-05-27'. Tried with making it '2022-05-27 00:00:00.000000+0000'. Earlier I kept stats_date field as Date type in cassandra and tried passing LocalTime object.

How Do I resolve this problem ?

Upvotes: 0

Views: 830

Answers (1)

clunven
clunven

Reputation: 1685

Looking at the schema of the table stats_date is a timestamp.

Looking at Spring Data Cassandra Mapping here a java.util.Date is the related Java Type for this CQL Type.

I suggest to convert the dataString parameter to a java.util.Date using the good old java class SimpleDateFormat in the java code before and change the repository code with the following:

@Query(value = "select * from engine_torque_by_last_date " +
        "where vin_number = :vinNumber " +
        "and organization_id in :organizationId and " +
        "stats_date = :dateString")
List<EngineTorqueByLastDate> findByVinNumberAndOrganizationIdAndStatsDate(String vinNumber, Integer organizationId, Date dateString);

You can probably totally get rid of @Query, it is a standard query and your should use the convention in Spring Data. Try to rename the function to :

findByKeyVinNumberAndKeyOrganizationIdAndKeyStatsDate

Upvotes: 2

Related Questions