Date and Time Operators

This page describes the available operators to assist with performing time-based calculations.

note

If an operator's first argument is a table's timestamp, QuestDB may use an Interval Scan for optimization.

IN (timeRange)

Returns results within a defined range of time.

Arguments

• timeRange is a string type representing the desired time range.

Syntax

Examples

Results in a given year

SELECT * FROM scores WHERE ts IN '2018';

This query returns all records from the year 2018:

ts	score
2018-01-01T00:00:00.000000Z	123.4
...	...
2018-12-31T23:59:59.999999Z	115.8

Results in a given minute

SELECT * FROM scores WHERE ts IN '2018-05-23T12:15';

This query returns all records from the 15th minute of 12 PM on May 23, 2018:

ts	score
2018-05-23T12:15:00.000000Z	123.4
...	...
2018-05-23T12:15:59.999999Z	115.8

IN (timeRangeWithModifier)

You can apply a modifier to further customize the range. The modifier extends the upper bound of the original timestamp based on the modifier parameter. An optional interval with occurrence can be set, to apply the search in the given time range repeatedly, for a set number of times.

Arguments

• timeRangeWithModifier is a string in the format 'timeRange;modifier;interval;repetition'.

Syntax

• timestamp is the original time range for the query.

• modifier is a signed integer modifying the upper bound applying to the timestamp:

  • A positive value extends the selected period.
  • A negative value reduces the selected period.

• interval is an unsigned integer indicating the desired interval period for the time range.

• repetition is an unsigned integer indicating the number of times the interval should be applied.

Examples

Modifying the range:

Results in a given year and the first month of the next year

SELECT * FROM scores WHERE ts IN '2018;1M';

In this example, the range is the year 2018. The modifier 1M extends the upper bound (originally 31 Dec 2018) by one month.

ts	score
2018-01-01T00:00:00.000000Z	123.4
...	...
2019-01-31T23:59:59.999999Z	115.8

Results in a given month excluding the last 3 days

SELECT * FROM scores WHERE ts IN '2018-01;-3d';

In this example, the range is January 2018. The modifier -3d reduces the upper bound (originally 31 Jan 2018) by 3 days.

ts	score
2018-01-01T00:00:00.000000Z	123.4
...	...
2018-01-28T23:59:59.999999Z	113.8

Modifying the interval:

Results on a given date with an interval

SELECT * FROM scores WHERE ts IN '2018-01-01;1d;1y;2';

In this example, the range is extended by one day from Jan 1 2018, with a one-year interval, repeated twice. This means that the query searches for results on Jan 1-2 in 2018 and in 2019:

ts	score
2018-01-01T00:00:00.000000Z	123.4
...	...
2018-01-02T23:59:59.999999Z	110.3
2019-01-01T00:00:00.000000Z	128.7
...	...
2019-01-02T23:59:59.999999Z	103.8

IN (value1, value2, ...)

The IN operator, when used with more than one argument, behaves as the standard SQL IN. It provides a concise way to represent multiple OR-ed equality conditions.

Arguments

• value1, value2, ... are string type values representing dates or timestamps.

Examples

Consider the following query:

IN list

SELECT * FROM scores
WHERE ts IN ('2018-01-01', '2018-01-01T12:00', '2018-01-02');

This query is equivalent to:

IN list equivalent OR

SELECT * FROM scores
WHERE ts = '2018-01-01' or ts = '2018-01-01T12:00' or ts = '2018-01-02';

ts	value
2018-01-01T00:00:00.000000Z	123.4
2018-01-01T12:00:00.000000Z	589.1
2018-01-02T00:00:00.000000Z	131.5

BETWEEN value1 and value2

The BETWEEN operator allows you to specify a non-standard range. It includes both upper and lower bounds, similar to standard SQL. The order of these bounds is interchangeable, meaning BETWEEN X AND Y is equivalent to BETWEEN Y AND X.

Arguments

• value1 and value2 can be of date, timestamp, or string type.

Examples

Explicit range

SELECT * FROM scores
WHERE ts BETWEEN '2018-01-01T00:00:23.000000Z' AND '2018-01-01T00:00:23.500000Z';

This query returns all records within the specified timestamp range:

ts	value
2018-01-01T00:00:23.000000Z	123.4
...	...
2018-01-01T00:00:23.500000Z	131.5

The BETWEEN operator can also accept non-constant bounds. For instance, the following query returns all records older than one year from the current date:

One year before current date

SELECT * FROM scores
WHERE ts BETWEEN to_str(now(), 'yyyy-MM-dd')
AND dateadd('y', -1, to_str(now(), 'yyyy-MM-dd'));

The result set for this query would be:

ts	score
2018-01-01T00:00:00.000000Z	123.4
...	...
2018-12-31T23:59:59.999999Z	115.8

Results between two specific timestamps

SELECT * FROM scores WHERE ts BETWEEN '2018-05-23T12:15:00.000000Z' AND '2018-05-23T12:16:00.000000Z';

This query returns all records from the 15th minute of 12 PM on May 23, 2018:

ts	score
2018-05-23T12:15:00.000000Z	123.4
...	...
2018-05-23T12:15:59.999999Z	115.8