5af066578a
The goal is to make the available defaults safe for future use, as they are often taken from existing config files or documentation verbatim. Referenced issue: #14290 Closes scylladb/scylladb#15947
78 lines
3.1 KiB
ReStructuredText
78 lines
3.1 KiB
ReStructuredText
|
||
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 :ref:`timestamp <timestamps>` (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
|
||
|
||
1. Create the table
|
||
|
||
.. code-block:: cql
|
||
|
||
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));
|
||
|
||
2. Insert data into the table where the timezone is Pacific Standard Time (-0800)
|
||
|
||
.. code-block:: cql
|
||
|
||
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);
|
||
|
||
3. Query for data within a time range without using a timezone. There are no results.
|
||
|
||
.. code-block:: cql
|
||
|
||
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)
|
||
|
||
4. Now query for data within a time range with a timezone. Notice how the time corrects itself to GMT timezone.
|
||
|
||
.. code-block:: cql
|
||
|
||
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)
|
||
|