All Products
Search
Document Center

Time Series Database:Time Syntax

Last Updated:Sep 27, 2023

By default, most SELECT statements query the data whose timestamps range from 1677-09-21 00:12:43.145224194 UTC to 2262-04-11T23:47:16.854775806Z UTC. If your SELECT statements include GROUP BY time() clauses, the query returns only the points whose timestamps fall in a specific time range. By default, the start time of the time range is specified by the 1677-09-21 00:12:43.145224194 UTC timestamp. The end time of the time range is the time that is returned by the now() function. This section describes how to specify time ranges in the WHERE clauses of the SELECT statements.

Absolute time

Specify an absolute time by using a date-time string or specifying the epoch_time parameter.

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>] [...]]

Description

The following table lists the supported operators.

=

Equal to

<>

Not equal to

! =

Not equal to

>

Greater than

>=

Greater than or equal to

<

Less than

<=

Less than or equal to

TSDB for InfluxDB® does not allow you to use the OR operator to specify the absolute time in the WHERE clause. For more information, see the “FAQ” topic.

rfc3339_date_time_string

YYYY-MM-DDTHH:MM:SS.nnnnnnnnnZ

The .nnnnnnnnn element is optional. If you do not specify this element, the default value .000000000 is used. You must use single quotation marks (‘) to enclose date-time strings that use the RFC 3339 format.

rfc3339_like_date_time_string

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

The HH:MM:SS.nnnnnnnnn.nnnnnnnnn element is optional. If you do not specify this element, the default value 00:00:00.000000000 is used. You must use single quotation marks (‘) to enclose date-time strings that use the formats similar to the RFC 3339 format.

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® assumes that epoch timestamps are measured in nanosecond. You can add a character to the end of an epoch timestamp to indicate a non-nanosecond time granularity.

Basic arithmetic operations

All timestamp formats support the basic arithmetic operations. You can use the plus sign (+) to add a time interval or the minus sign (-) to subtract a time interval from a timestamp for which a time granularity is specified. Note that you must add a space between the plus sign (+) or the minus sign (-) and the timestamp in your InfluxQL query.

Examples

Specify a time range by using date-time strings that use the RFC 3339 format

> 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

The query returns the data whose timestamps occur from 00:00:00.000000000 on August 18, 2015 to 00:12:00 on August 18, 2015. If the nanosecond time granularity is not specified for the start timestamp, the .000000000 value is used for the start timestamp.

Note that you must use single quotation marks (‘) to enclose the date-time strings that use the RFC 3339 format.

Specify a time range by using date-time strings that use the formats similar to the RFC 3339 format

> 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

The query returns the data whose timestamps occur from 00:00:00 on August 18, 2015 to 00:12:00 on August 18, 2015. The first date-time string includes only the date component and does not include the time component. TSDB for InfluxDB® assumes that the time is 00:00:00.

Note that you must use single quotation marks (‘) to enclose the date-time strings that use the formats similar to the RFC 3339 format.

Specify a time range by using 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

The query returns the data whose timestamps occur from 00:00:00 on August 18, 2015 to 00:12:00 on August 18, 2015. By default, TSDB for InfluxDB® assumes that epoch timestamps are measured in nanoseconds.

Specify a time range by using epoch timestamps for which a non-nanosecond time granularity is specified

> 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

The query returns the data whose timestamps occur from 00:00:00 on August 18, 2015 to 00:12:00 on August 18, 2015. The letter s at the end of the epoch timestamps indicates that the epoch timestamps are measured in seconds.

Perform basic arithmetic operations on date-time strings that use the formats similar to the RFC 3339 format

> 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

The query returns the data whose timestamps occur later than 21:30:00 on August 18, 2015. This time is obtained by adding a 6-minute interval to the 21:24:00 on August 18, 2015 time point. Note that you must separate the timestamp and the plus sign (+) with a space and separate the plus sign (+) and 6m with a space.

Perform basic arithmetic operations on epoch timestamps

> 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

The query returns the data whose timestamps occur later than 21:18:00 on August 18, 2015. This time is obtained by subtracting a 6-minute interval from the 21:24:00 on August 18, 2015 time point. Note that you must separate the timestamp and the plus sign (-) with a space and separate the minus sign (-) and 6m with a space.

Relative time

Use the now() function in your SELECT statement to query the data whose timestamps are relative to the local timestamp of the server.

Syntax

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

Description

The now() function returns the Unix time of a server when the query is executed on the server. You must use a space to separate the minus sign (-) or the plus sign (+) and the value of the duration_literal parameter.

  • The following table lists the supported operators.

=

Equal to

<>

Not equal to

! =

Not equal to

>

Greater than

>=

Greater than or equal to

<

Less than

<=

Less than or equal to

  • duration_literal

u or µ

Microsecond

ms

Millisecond

s

Second

m

Minute

h

Hour

d

Day

w

Week

Examples

Specify a time range by using a relative time

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

The query returns the data whose timestamps occur in the last hour. You must separate the now() function and the minus sign (-) with a space and separate the minus sign (-) and 1h with a space.

Specify a time range by using an absolute time and a relative 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

The query returns the data whose timestamps fall in the specified time range. The start time of the time range is 21:18:00 on September 18, 2015. The end time of the time range is specified by the now() + 1000d expression. This expression indicates the 1,000 days that follow the time returned by the now() function. You must separate now() and the plus sign (+) with a space and separate the plus sign (+) and 1000d with a space.

FAQ about time syntax

Can I use the OR operator to specify multiple time intervals in my query?

No, the OR operator cannot be used in the WHERE clause to specify multiple time intervals. For more information, see the “FAQ” topic.

How do I use a GROUP BY time() clause to query the data whose timestamps occur after the time returned by the now() function?

By default, most SELECT statements query the data whose timestamps range from 1677-09-21 00:12:43.145224194 UTC to 2262-04-11T23:47:16.854775806Z UTC. If your SELECT statements include GROUP BY time() clauses, the query returns only the points whose timestamps fall in a specific time range. By default, the start time of the time range is specified by the 1677-09-21 00:12:43.145224194 UTC timestamp. The end time of the time range is the time that is returned by the now() function.

To obtain the data whose timestamps occur after the time returned by the now() function, specify the end time of the time range in the WHERE clauses of the SELECT statements. The prerequisite is that the SELECT statements include GROUP BY time() clauses.

Examples

Use the command-line interface (CLI) to write points to the NOAA_water_database database. Note that the timestamps of these points occur after the time returned by the now() function.

> INSERT h2o_feet,location=santa_monica water_level=3.1 1587074400000000000

Run a GROUP BY time() query that covers the data whose timestamps fall in the specified time range. The start time of the time range is specified by the 2015-09-18T21:30:00Z timestamp. The end time of the time range is specified by the now() + 180w expression. This expression indicates the 180 weeks that follow the time returned by the now() function.

> 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

Note that you must specify the end time of the specified time range in the WHERE clause to override the default end time that is specified by the now() function. In the following query, the start time of the time range is reset to the time returned by the now() function and the end time is not specified. In this scenario, the start time of the time range is the time that is returned by the now() function. The end time of the time range is also the time that is returned by the now() function. Therefore, 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 formats for timestamps to be returned?

By default, the CLI returns the timestamps that use the epoch format and the timestamps are measured in nanoseconds. You can run the precision <format> command to specify other time formats. By default, the HTTP API returns the timestamps that use the RFC 3339 format. You can set the epoch parameter to specify other time formats.

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