Specify time ranges in InfluxQL queries - Time Series Database

Use the WHERE clause in SELECT statements to filter data by time range. TSDB for InfluxDB® supports two approaches: absolute time (a fixed timestamp) and relative time (an offset from now()).

By default, most SELECT statements return data with timestamps between 1677-09-21 00:12:43.145224194 UTC and 2262-04-11T23:47:16.854775806Z UTC. When you add a GROUP BY time() clause, the default range becomes 1677-09-21 00:12:43.145224194 UTC to now().

Absolute time

Specify a fixed point or range in time using a date-time string or an epoch timestamp.

Syntax

SELECT_clause FROM_clause WHERE time <operator> ['<rfc3339_date_time_string>' | '<rfc3339_like_date_time_string>' | <epoch_time>] [AND ['<rfc3339_date_time_string>' | '<rfc3339_like_date_time_string>' | <epoch_time>] [...]]

Supported operators

Operator	Meaning
`=`	Equal to
`<>`	Not equal to
`!=`	Not equal to
`>`	Greater than
`>=`	Greater than or equal to
`<`	Less than
`<=`	Less than or equal to

The OR operator is not supported for time ranges in the WHERE clause. Time ranges must be continuous intervals — InfluxQL has no syntax for querying two disjoint time windows in a single query. See Can I use OR to specify multiple time ranges? for details.

Time formats

RFC 3339

YYYY-MM-DDTHH:MM:SS.nnnnnnnnnZ

The .nnnnnnnnn element is optional and defaults to .000000000. Enclose RFC 3339 date-time strings in single quotation marks (').

RFC 3339-like

YYYY-MM-DD HH:MM:SS.nnnnnnnnn

The HH:MM:SS.nnnnnnnnn element is optional and defaults to 00:00:00.000000000. Enclose RFC 3339-like date-time strings in single quotation marks (').

Epoch time

Epoch time is the number of seconds that have elapsed since 00:00:00 UTC, Thursday, January 1, 1970. By default, TSDB for InfluxDB® treats epoch timestamps as nanoseconds. Append a suffix to specify a different unit:

Suffix	Unit
`u` or `µ`	Microsecond
`ms`	Millisecond
`s`	Second
`m`	Minute
`h`	Hour
`d`	Day
`w`	Week

Arithmetic operations

All timestamp formats support + (add) and - (subtract) to offset a timestamp by a duration. Add a space before and after the operator.

Examples

Specify a time range with RFC 3339 date-time strings

> SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18T00:00:00.000000000Z' AND time <= '2015-08-18T00:12:00Z'

name: h2o_feet
time                   water_level
----                   -----------
2015-08-18T00:00:00Z   2.064
2015-08-18T00:06:00Z   2.116
2015-08-18T00:12:00Z   2.028

Returns data from 00:00:00.000000000 to 00:12:00 on August 18, 2015. When the nanosecond element is omitted from the start timestamp, it defaults to .000000000.

Specify a time range with RFC 3339-like date-time strings

> SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18' AND time <= '2015-08-18 00:12:00'

name: h2o_feet
time                   water_level
----                   -----------
2015-08-18T00:00:00Z   2.064
2015-08-18T00:06:00Z   2.116
2015-08-18T00:12:00Z   2.028

Returns data from 00:00:00 to 00:12:00 on August 18, 2015. A date-only string (no time component) defaults to 00:00:00.

Specify a time range with epoch timestamps

> SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= 1439856000000000000 AND time <= 1439856720000000000

name: h2o_feet
time                   water_level
----                   -----------
2015-08-18T00:00:00Z   2.064
2015-08-18T00:06:00Z   2.116
2015-08-18T00:12:00Z   2.028

Returns data from 00:00:00 to 00:12:00 on August 18, 2015. The timestamps are in nanoseconds (the default unit).

Specify a time range with second-precision epoch timestamps

> SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= 1439856000s AND time <= 1439856720s

name: h2o_feet
time                   water_level
----                   -----------
2015-08-18T00:00:00Z   2.064
2015-08-18T00:06:00Z   2.116
2015-08-18T00:12:00Z   2.028

Returns data from 00:00:00 to 00:12:00 on August 18, 2015. The s suffix indicates the timestamps are in seconds.

Perform arithmetic on an RFC 3339 date-time string

> SELECT "water_level" FROM "h2o_feet" WHERE time > '2015-09-18T21:24:00Z' + 6m

name: h2o_feet
time                   water_level
----                   -----------
2015-09-18T21:36:00Z   5.066
2015-09-18T21:42:00Z   4.938

Returns data after 21:30:00 on September 18, 2015 — six minutes added to 21:24:00. Spaces around + are required.

Perform arithmetic on an epoch timestamp

> SELECT "water_level" FROM "h2o_feet" WHERE time > 24043524m - 6m

name: h2o_feet
time                   water_level
----                   -----------
2015-09-18T21:24:00Z   5.013
2015-09-18T21:30:00Z   5.01
2015-09-18T21:36:00Z   5.066
2015-09-18T21:42:00Z   4.938

Returns data after 21:18:00 on September 18, 2015 — six minutes subtracted from 21:24:00. Spaces around - are required.

Relative time

Use now() to query data relative to the server's current Unix time.

Syntax

SELECT_clause FROM_clause WHERE time <operator> now() [[ - | + ] <duration_literal>] [(AND|OR) now() [...]]

Description

now() returns the Unix time of the server at query execution. Add a space before and after - or + when appending a duration literal.

Supported operators: same as absolute time.

Duration literal suffixes:

Suffix	Unit
`u` or `µ`	Microsecond
`ms`	Millisecond
`s`	Second
`m`	Minute
`h`	Hour
`d`	Day
`w`	Week

Examples

Query data from the last hour

> SELECT "water_level" FROM "h2o_feet" WHERE time > now() - 1h

Returns all points with timestamps in the past hour. Spaces around - are required.

Query data between an absolute start time and a relative end time

> SELECT "level description" FROM "h2o_feet" WHERE time > '2015-09-18T21:18:00Z' AND time < now() + 1000d

name: h2o_feet
time                   level description
----                   -----------------
2015-09-18T21:24:00Z   between 3 and 6 feet
2015-09-18T21:30:00Z   between 3 and 6 feet
2015-09-18T21:36:00Z   between 3 and 6 feet
2015-09-18T21:42:00Z   between 3 and 6 feet

Returns data from 21:18:00 on September 18, 2015 through 1,000 days from now. Spaces around + are required.

FAQ about time syntax

Can I use OR to specify multiple time ranges?

No. InfluxQL does not support OR in the WHERE clause for time ranges. Time ranges must be continuous intervals — there is no syntax for querying two disjoint time windows in a single query.

How do I query data with timestamps after now() using GROUP BY time()?

By default, SELECT statements with GROUP BY time() use now() as the end of the time range. To include points with timestamps in the future (relative to query time), explicitly set the end time in the WHERE clause.

Example

Write a point with a future timestamp to the NOAA_water_database database:

> INSERT h2o_feet,location=santa_monica water_level=3.1 1587074400000000000

Query with a GROUP BY time() clause and an explicit future end time (now() + 180w):

> SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='santa_monica' AND time >= '2015-09-18T21:30:00Z' AND time <= now() + 180w GROUP BY time(12m) fill(none)

name: h2o_feet
time                   mean
----                   ----
2015-09-18T21:24:00Z   5.01
2015-09-18T21:36:00Z   5.002
2020-04-16T22:00:00Z   3.1

If you omit the end time, both the start and end default to now(), so the query returns no data:

> SELECT MEAN("water_level") FROM "h2o_feet" WHERE "location"='santa_monica' AND time >= now() GROUP BY time(12m) fill(none)
>

How do I configure the timestamp format in query results?

Command-line interface (CLI): Returns epoch timestamps in nanoseconds by default. Run precision <format> to change the format.
HTTP API: Returns timestamps in RFC 3339 format by default. Set the epoch parameter to change the format.

InfluxDB® is a trademark registered by InfluxData, which is not affiliated with, and does not endorse, TSDB for InfluxDB®.