Using date ranges in solr_query

CQL type DateRangeType is mapped to Solr DateRangeField.

The Solr DateRangeField is supported in DSE Search with mapping of Solr DateRangeField to the CQL type DateRangeType.

The CQL type DateRangeType is supported for use with the latest Java driver, the DSE Python driver, and cqlsh commands.

Overriding the default TimeZone (UTC) in search queries

Specify the TZ parameter to overwrite the default TimeZone (UTC) that is used for adding and rounding in date math. The local rules for the specified time zone, including the start and end of daylight saving time (DST) if any, determine when each arbitrary day starts. The time zone rules impact the rounding and adding of DAYs, but also cascades to rounding of HOUR, MIN, MONTH, and YEAR. For example, specifying a different time zone changes the result:


Date math	Result
`2016-03-10T12:34:56Z/YEAR`	Default TZ `2016-01-01T00:00:00Z`
	TZ=America/Los_Angeles `2016-01-01T08:00:00Z`
`2016-03-10T08:00:00Z+1DAY`	Default TZ `2016-03-11T08:00:00Z`
	TZ=America/Los_Angeles `2016-03-11T07:00:00Z`

The value of the TZ parameter can be any zone ID that is supported by the java TimeZone class.

Primary key or ordinary column

DateRangeType can be used as a primary key or ordinary column:

CREATE TABLE taxi_trips(id int PRIMARY KEY, pickup_dropoff_range 'DateRangeType');

CREATE TABLE weather_sensors(weatherstation_id text, event_time 'DateRangeType', temperature text, PRIMARY KEY (weatherstation_id,event_time));

CQL representation

The CQL representation uses the same syntax as Solr DateRangeField:

INSERT INTO taxi_trips(id, pickup_dropoff_range) VALUES (1, '[2017-02-02T14:57:00 TO 2017-02-02T15:10:17]');

INSERT INTO taxi_trips(id, pickup_dropoff_range) VALUES (2, '[2017-02-01T09:00:03 TO 2017-02-01T09:32:00.001]');

INSERT INTO taxi_trips(id, pickup_dropoff_range) VALUES (3, '[2017-02-03T12:10:01.358 TO 2017-02-03T12:19:57]');

dateTime precision

The dateTime precision is preserved from user input. Milliseconds are displayed only when millisecond precision is provided on input.

SELECT * FROM taxi_trips;

 id | pickup_dropoff_range
----+----------------------------------------------------
  1 | [2017-02-02T14:57:00Z TO 2017-02-02T15:10:17Z]
  2 | [2017-02-01T09:00:03Z TO 2017-02-01T09:32:00.001Z]
  3 | [2017-02-03T12:10:01.358Z TO 2017-02-03T12:19:57Z]

Create search index:

CREATE SEARCH INDEX ON taxi_trips ;

Select all trips from February 2017:

SELECT * FROM taxi_trips WHERE solr_query = 'pickup_dropoff_range:2017-02';

Select all trips started after 2017-02-01 12:00 PM (inclusive) and ended before 2017-02-02 (inclusive):

SELECT * FROM taxi_trips WHERE solr_query = 'pickup_dropoff_range:[2017-02-01T12 TO 2017-02-02]';

Select all trips started after 2017-02-01 12:00 PM (inclusive) and ended before 2017-02-01:23:59:59.999 (inclusive):

SELECT * FROM taxi_trips WHERE solr_query = 'pickup_dropoff_range:[2017-02-01T12 TO 2017-02-01]';

Single point in time

DateRangeField can represent a single point in time:

INSERT INTO weather_sensors (weatherstation_id, event_time, temperature) VALUES ('A1', '2017-10-02T00:00:05', '12C');
INSERT INTO weather_sensors (weatherstation_id, event_time, temperature) VALUES ('A1', '2017-10-02T00:00:10', '12C');
INSERT INTO weather_sensors (weatherstation_id, event_time, temperature) VALUES ('A1', '2017-10-02T00:00:15', '13C');
INSERT INTO weather_sensors (weatherstation_id, event_time, temperature) VALUES ('A1', '2017-10-02T00:00:20', '13C');
INSERT INTO weather_sensors (weatherstation_id, event_time, temperature) VALUES ('A1', '2017-10-02T00:00:25', '12C');

Select all from weather_sensors:

SELECT * FROM weather_sensors;

 weatherstation_id | event_time           | temperature
----_--------------+----------------------+-------------
                A1 | 2017-10-02T00:00:05Z |         12C
                A1 | 2017-10-02T00:00:10Z |         12C
                A1 | 2017-10-02T00:00:15Z |         13C
                A1 | 2017-10-02T00:00:20Z |         13C
                A1 | 2017-10-02T00:00:25Z |         12C

Create a search index on weather_sensors:

CREATE SEARCH INDEX ON weather_sensors ;

Select a specific point in time:

SELECT * FROM weather_sensors WHERE solr_query = 'event_time:[2017-10-02T00:00:10 TO 2017-10-02T00:00:20]';

 weatherstation_id | event_time           | solr_query | temperature
-------------------+----------------------+------------+-------------
                A1 | 2017-10-02T00:00:10Z |       null |         12C
                A1 | 2017-10-02T00:00:15Z |       null |         13C
                A1 | 2017-10-02T00:00:20Z |       null |         13C

Open bounds

DateRangeField can have open bounds.

Select from a point in time to an open bound:

SELECT * FROM weather_sensors WHERE solr_query = 'event_time:[2017-10-02T00:00:10 TO *]';

 weatherstation_id | event_time           | solr_query | temperature
-------------------+----------------------+------------+-------------
                A1 | 2017-10-02T00:00:25Z |       null |         12C
                A1 | 2017-10-02T00:00:10Z |       null |         12C
                A1 | 2017-10-02T00:00:15Z |       null |         13C
                A1 | 2017-10-02T00:00:20Z |       null |         13C

Select from an open bound up to a point in time:

SELECT * FROM weather_sensors WHERE solr_query = 'event_time:[* TO 2017-10-02T00:00:20]';

 weatherstation_id | event_time           | solr_query | temperature
-------------------+----------------------+------------+-------------
                A1 | 2017-10-02T00:00:10Z |       null |         12C
                A1 | 2017-10-02T00:00:15Z |       null |         13C
                A1 | 2017-10-02T00:00:20Z |       null |         13C
                A1 | 2017-10-02T00:00:05Z |       null |         12C

Select from all points in time:

SELECT * FROM weather_sensors WHERE solr_query = 'event_time:[* TO *]';

 weatherstation_id | event_time           | solr_query | temperature
-------------------+----------------------+------------+-------------
                A1 | 2017-10-02T00:00:25Z |       null |         12C
                A1 | 2017-10-02T00:00:10Z |       null |         12C
                A1 | 2017-10-02T00:00:15Z |       null |         13C
                A1 | 2017-10-02T00:00:20Z |       null |         13C
                A1 | 2017-10-02T00:00:05Z |       null |         12C

Insert an open-bounded range into a table:

INSERT INTO weather_sensors (weatherstation_id, event_time, temperature) VALUES ('A1', '[2017-10-02T00:00:25 TO *]', '12C');
SELECT * FROM weather_sensors WHERE solr_query = 'event_time:[* TO *]';

 weatherstation_id | event_time                  | solr_query | temperature
-------------------+-----------------------------+------------+-------------
                A1 |        2017-10-02T00:00:25Z |       null |         12C
                A1 |        2017-10-02T00:00:10Z |       null |         12C
                A1 |        2017-10-02T00:00:15Z |       null |         13C
                A1 |        2017-10-02T00:00:20Z |       null |         13C
                A1 |        2017-10-02T00:00:05Z |       null |         12C
                A1 | [2017-10-02T00:00:25Z TO *] |       null |         12C