Caution

You're viewing documentation for an unstable version of ScyllaDB Open Source. Switch to the latest stable version.

ScyllaDB Docs ScyllaDB Open Source Troubleshooting ScyllaDB CQL Time Range Queries Do Not Return Some or All of the Data

Time Range Queries Do Not Return Some or All of the Data¶

This troubleshooting article describes what to do when time range queries do not return some of or all of the expected data.

Problem¶

In many cases, the client that performs an INSERT, is not the same client that runs a SELECT. As such, the clients may be in different time zones (TZ) or may be using client/server timestamps with different TZs.

Solution¶

It is highly recommended when submitting a SELECT query to include the client’s local TZ within the timestamp (for example: 2019-02-18 06:00:00+0000). Otherwise, the query will parse differently as a result of different TZs and may not return some / all the dataset you’re trying to fetch.

Note

The +0000 (above) is an RFC 822 4-digit time zone specification where +0000 refers to GMT. US Pacific Standard Time, for example is -0800. A timestamp using US Pacific Standard Time would be 2019-02-18 06:00:00-0800.

Example¶

This example creates a table, inserts data with a time zone timestamp, and then shows two queries: one with and one without a time zone timestamp

Create the table

CREATE KEYSPACE IF NOT EXISTS mykeyspace
   WITH REPLICATION = { 'class' : 'NetworkTopologyStrategy', 'replication_factor' : 3 };
USE mykeyspace;
CREATE TABLE heartrate (
   pet_chip_id uuid,
   time timestamp,
   heart_rate int,
   PRIMARY KEY (pet_chip_id, time));

Insert data into the table where the timezone is Pacific Standard Time (-0800)

INSERT INTO heartrate(pet_chip_id, time, heart_rate)
   VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:01:00-0800', 100);
INSERT INTO heartrate(pet_chip_id, time, heart_rate)
   VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:02:00-0800', 103);
INSERT INTO heartrate(pet_chip_id, time, heart_rate)
   VALUES (123e4567-e89b-12d3-a456-426655440b23, '2019-03-04 07:03:00-0800', 130);

Query for data within a time range without using a timezone. There are no results.

SELECT * from heartrate
   WHERE pet_chip_id = 123e4567-e89b-12d3-a456-426655440b23
   AND time>='2019-03-04 07:00:00'
   AND time <= '2019-03-04 08:00:00';

pet_chip_id  | time | heart_rate
-------------+------+------------

(0 rows)

Now query for data within a time range with a timezone. Notice how the time corrects itself to GMT timezone.

SELECT * from heartrate
   WHERE pet_chip_id = 123e4567-e89b-12d3-a456-426655440b23
   AND time>='2019-03-04 07:00:00-0800'
   AND time <= '2019-03-04 08:00:00-0800';

pet_chip_id                           | time                            | heart_rate
--------------------------------------+---------------------------------+------------
123e4567-e89b-12d3-a456-426655440b23  | 2019-03-04 15:01:00.000000+0000 | 100
123e4567-e89b-12d3-a456-426655440b23  | 2019-03-04 15:02:00.000000+0000 | 103
123e4567-e89b-12d3-a456-426655440b23  | 2019-03-04 15:03:00.000000+0000 | 130

(3 rows)

Was this page helpful?