All Products
Search
Document Center

Time Syntax

Last Updated: Aug 06, 2020

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

  1. 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

  1. 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

  1. 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

  1. > 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'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 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

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2015-08-18' AND time <= '2015-08-18 00:12:00'
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 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

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= 1439856000000000000 AND time <= 1439856720000000000
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 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

  1. > SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= 1439856000s AND time <= 1439856720s
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-08-18T00:00:00Z 2.064
  6. 2015-08-18T00:06:00Z 2.116
  7. 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

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time > '2015-09-18T21:24:00Z' + 6m
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-09-18T21:36:00Z 5.066
  6. 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

  1. > SELECT "water_level" FROM "h2o_feet" WHERE time > 24043524m - 6m
  2. name: h2o_feet
  3. time water_level
  4. ---- -----------
  5. 2015-09-18T21:24:00Z 5.013
  6. 2015-09-18T21:30:00Z 5.01
  7. 2015-09-18T21:36:00Z 5.066
  8. 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 substracting 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

  1. 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

  1. > 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

  1. > SELECT "level description" FROM "h2o_feet" WHERE time > '2015-09-18T21:18:00Z' AND time < now() + 1000d
  2. name: h2o_feet
  3. time level description
  4. ---- -----------------
  5. 2015-09-18T21:24:00Z between 3 and 6 feet
  6. 2015-09-18T21:30:00Z between 3 and 6 feet
  7. 2015-09-18T21:36:00Z between 3 and 6 feet
  8. 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.

  1. > 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.

  1. > 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)
  2. name: h2o_feet
  3. time mean
  4. ---- ----
  5. 2015-09-18T21:24:00Z 5.01
  6. 2015-09-18T21:36:00Z 5.002
  7. 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.

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

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®.