cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Subodh Nijsure <subodh.nijs...@gmail.com>
Subject Re: Strange select result when using date grater than query
Date Sun, 17 Aug 2014 20:43:33 GMT
I am running csql on same machine as my cassandra server.

I am observing really strange behavior if I do this query all  3 rows show up.

 SELECT asset_id,event_time,sensor_type, temperature,humidity from
temp_humidity_data   ALLOW FILTERING;

 asset_id | event_time               | sensor_type | temperature | humidity
----------+--------------------------+-------------+-------------+----------
        2 | 2014-08-17 13:36:00-0700 |           1 |      75.147 |   91.147
        2 | 2014-08-17 13:36:02-0700 |           1 |      66.308 |   72.308
        2 | 2014-08-17 13:36:49-0700 |           1 |      65.397 |   77.397

Now if I do following only one record shows up correctly.

SELECT asset_id,event_time,sensor_type, temperature,humidity from
temp_humidity_data where event_time > '2014-08-17 13:36:02-0700'
ALLOW FILTERING;

 asset_id | event_time               | sensor_type | temperature | humidity
----------+--------------------------+-------------+-------------+----------
        2 | 2014-08-17 13:36:49-0700 |           1 |      65.397 |   77.397


However if I add additional column asset_id='2' to where condition all
three rows show up? So this is not timezone issue, but may be I don't
understand how csql does AND in its queries?

SELECT asset_id,event_time,sensor_type, temperature,humidity from
temp_humidity_data where event_time > '2014-08-17 13:36:02-0700' AND
asset_id='2';

 asset_id | event_time               | sensor_type | temperature | humidity
----------+--------------------------+-------------+-------------+----------
        2 | 2014-08-17 13:36:00-0700 |           1 |      75.147 |   91.147
        2 | 2014-08-17 13:36:02-0700 |           1 |      66.308 |   72.308
        2 | 2014-08-17 13:36:49-0700 |           1 |      65.397 |   77.397

help!

-Subodh

On Sun, Aug 17, 2014 at 8:09 AM, Jack Krupansky <jack@basetechnology.com> wrote:
> I should have asked where your coordinator node is located. Check its time
> zone, relative to GMT.
>
> cqlsh is simply formatting the time stamp for your local display. That is
> separate from the actual query execution on the server coordinator node.
> cqlsh is merely a "client", not the "server". And separate from the actual
> data, which is stored in GMT.
>
>
> -- Jack Krupansky
>
> -----Original Message----- From: Subodh Nijsure
> Sent: Sunday, August 17, 2014 10:04 AM
> To: user@cassandra.apache.org
> Subject: Re: Strange select result when using date grater than query
>
>
> I am in PST ( Oakland ).
>
> I am storing the timestamp in UTC in my insert code, and I see that
> cqlsh converts the timestamp to local timezone? i.e.  if I set TZ=EST
> cqlsh shows me time stamps in EST like this for the same data set.
>
> SELECT asset_id,event_time,sensor_type, temperature,humidity from
> temp_humidity_data where asset_id='2';
> asset_id | event_time               | sensor_type | temperature | humidity
> ----------+--------------------------+-------------+-------------+----------
>        2 | 2014-08-17 05:33:16-0500 |           1 |      74.768 |   65.768
>        2 | 2014-08-17 05:33:17-0500 |           1 |      67.228 |   91.228
>        2 | 2014-08-17 05:33:19-0500 |           1 |       61.97 |    73.97
>
>
> So for query i though I should be giving time strings in local timezone too,
> no?
>
> -Subodh
>
> On Sun, Aug 17, 2014 at 5:17 AM, Jack Krupansky <jack@basetechnology.com>
> wrote:
>>
>> Are you more than 7 time zones behind GMT? If so, that would make 03:33
>> your
>> query less than 03:33-0700  Your query is using the default time zone,
>> which
>> will be the time zone configured for the coordinator node executing the
>> query.
>>
>> IOW, where are you?
>>
>> -- Jack Krupansky
>>
>> -----Original Message----- From: Subodh Nijsure
>> Sent: Sunday, August 17, 2014 6:45 AM
>> To: user@cassandra.apache.org
>> Subject: Strange select result when using date grater than query
>>
>>
>> Hello,
>>
>> I am fairly new to cassandra so this might be naieve question:
>>
>> I have table that currently has following entries:
>>
>> SELECT asset_id,event_time,sensor_type, temperature,humidity from
>> temp_humidity_data where asset_id='2';
>>
>> asset_id | event_time               | sensor_type | temperature | humidity
>>
>> ----------+--------------------------+-------------+-------------+----------
>>        2 | 2014-08-17 03:33:16-0700 |           1 |      74.768 |   65.768
>>        2 | 2014-08-17 03:33:17-0700 |           1 |      67.228 |   91.228
>>        2 | 2014-08-17 03:33:19-0700 |           1 |       61.97 |    73.97
>>
>> Now if I execute a query :
>>
>> SELECT asset_id,event_time,sensor_type, temperature,humidity from
>> temp_humidity_data where asset_id='2' and event_time > '2014-08-17
>> 03:33:20'  ALLOW FILTERING;
>>
>> it gives me back same results (!), I expected it to give me 0 results.
>>
>> asset_id | event_time               | sensor_type | temperature | humidity
>>
>> ----------+--------------------------+-------------+-------------+----------
>>        2 | 2014-08-17 03:33:16-0700 |           1 |      74.768 |   65.768
>>        2 | 2014-08-17 03:33:17-0700 |           1 |      67.228 |   91.228
>>        2 | 2014-08-17 03:33:19-0700 |           1 |       61.97 |    73.97
>>
>> am I doing something wrong?
>>
>> Note I have created table   with following options.
>>
>>            CREATE TABLE temp_humidity_data (
>>                asset_id text,
>>                event_time timestamp,
>>                sensor_serial_number text,
>>                sensor_type int,
>>                temperature float,
>>                humidity float,
>>                polling_freq int,
>>                PRIMARY KEY(asset_id ,event_time)
>>            ) WITH CLUSTERING ORDER BY (event_time ASC)
>>            AND caching = '{"keys":"ALL", "rows_per_partition":"ALL"}'
>>
>> I have also created following indexes:
>>
>> CREATE INDEX event_time_index ON temp_humidity_data (event_time);
>>
>> Also of note is, since actual installation I will be running against
>> large time series data I have configured  'row_cache_size_in_mb: 20'
>>
>> I am running cqlsh 5.0.1 , and cassandra version 2.1.0-rc3
>>
>> Would appreciate any suggestion on why the date grater-than query is
>> returning all the results?
>>
>> -Subodh
>
>

Mime
View raw message