Time and timezone queries

InfluxQL is designed for working with time series data and includes features specifically for working with time. You can review the following ways to work with time and timestamps in your InfluxQL queries:

• Configuring returned timestamps
• Time syntax
• Absolute time
• Relative time
• The Time Zone clause
• Common issues with time syntax

Configuring returned timestamps

The InfluxQL shell returns timestamps in nanosecond UNIX epoch format by default. Specify alternative formats with the precision command.

If you are using the InfluxQL shell, use the precision helper command precision rfc3339 to view results in human readable format.

The InfluxDB API returns timestamps in RFC3339 format by default. Specify alternative formats with the epoch query string parameter.

Time syntax

For most SELECT statements, the default time range is between 1677-09-21 00:12:43.145224194 and 2262-04-11T23:47:16.854775806Z UTC. For SELECT statements with a GROUP BY time() clause, the default time range is between 1677-09-21 00:12:43.145224194 UTC and now(). The following sections detail how to specify alternative time ranges in the SELECT statement’s WHERE clause.

Other supported features include:
Absolute time
Relative time

Absolute time

Specify absolute time with date-time strings and epoch time.

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

Currently, InfluxDB does not support using OR with absolute time in the WHERE clause. See the Frequently Asked Questions document and the GitHub Issue for more information.

rfc3339_date_time_string

'YYYY-MM-DDTHH:MM:SS.nnnnnnnnnZ'

.nnnnnnnnn is optional and is set to .000000000 if not included. The RFC3339 date-time string requires single quotes.

rfc3339_like_date_time_string

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

HH:MM:SS.nnnnnnnnn.nnnnnnnnn is optional and is set to 00:00:00.000000000 if not included. The RFC3339-like date-time string requires single quotes.

epoch_time

Epoch time is the amount of time that has elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970.

By default, InfluxDB assumes that all epoch timestamps are in nanoseconds. Include a duration literal at the end of the epoch timestamp to indicate a precision other than nanoseconds.

Basic arithmetic

All timestamp formats support basic arithmetic. Add (+) or subtract (-) a time from a timestamp with a duration literal. Note that InfluxQL requires a whitespace between the + or - and the duration literal.

Examples

Specify a time range with RFC3339 date-time strings

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

Output:

Name: h2o_feet

The query returns data with timestamps between August 18, 2019 at 00:00:00.000000000 and August 18, 2019 at 00:12:00.

Note that the single quotes around the RFC3339 date-time strings are required.

Specify a time range with RFC3339-like date-time strings

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

Output:

Name: h2o_feet

The query returns data with timestamps between August 18, 2019 at 00:00:00 and August 18, 2019 at 00:12:00. The first date-time string does not include a time; InfluxDB assumes the time is 00:00:00.

Note that the single quotes around the RFC3339-like date-time strings are required.

Specify a time range with epoch timestamps

SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= 1564635600000000000 AND time <= 1566190800000000000

Output:

Name: h2o_feet

The query returns data with timestamps that occur between August 1, 2019 at 00:00:00 and August 19, 2019 at 00:12:00. By default InfluxDB assumes epoch timestamps are in nanoseconds.

Specify a time range with second-precision epoch timestamps

SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= 1566190800s AND time <= 1566191520s

Output:

The query returns data with timestamps that occur between August 19, 2019 at 00:00:00 and August 19, 2019 at 00:12:00. The s duration literal at the end of the epoch timestamps indicate that the epoch timestamps are in seconds.

Perform basic arithmetic on an RFC3339-like date-time string

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

Output:

Name: h2o_feet

The query returns data with timestamps that occur at least six minutes after September 17, 2019 at 21:24:00. Note that the whitespace between the + and 6m is required.

Perform basic arithmetic on an epoch timestamp

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

Output:

Name: h2o_feet

The query returns data with timestamps that occur at least six minutes before September 18, 2019 at 21:24:00. Note that the whitespace between the - and 6m is required. Note that the results above are partial as the dataset is large.

Relative time

Use now() to query data with timestamps relative to the server’s current timestamp.

Syntax

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

now() is the Unix time of the server at the time the query is executed on that server. The whitespace between - or + and the duration literal is required.

Supported operators

duration_literal

• microseconds: u or µ
• milliseconds: ms
• secondss
• minutesm
• hours:h
• days:d
• weeks:w

Examples

Specify a time range with relative time

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

The query returns data with timestamps that occur within the past hour. The whitespace between - and 1h is required.

Specify a time range with absolute time and relative time

Specify a time range with absolute time and relative time

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

Output:

Name: h2o_feet

The query returns data with timestamps that occur between September 17, 2019 at 21:18:00 and 1000 days from now(). The whitespace between + and 1000d is required.

The Time Zone clause

Use the tz() clause to return the UTC offset for the specified timezone.

Syntax

SELECT_clause FROM_clause [WHERE_clause] [GROUP_BY_clause] [ORDER_BY_clause] [LIMIT_clause] [OFFSET_clause] [SLIMIT_clause] [SOFFSET_clause] tz('<time_zone>')

By default, InfluxDB stores and returns timestamps in UTC. The tz() clause includes the UTC offset or, if applicable, the UTC Daylight Savings Time (DST) offset to the query’s returned timestamps. The returned timestamps must be in RFC3339 format for the UTC offset or UTC DST to appear. The time_zone parameter follows the TZ syntax in the Internet Assigned Numbers Authority time zone database and it requires single quotes.

Examples

Return the UTC offset for Chicago’s time zone

SELECT "water_level" FROM "h2o_feet" WHERE "location" = 'santa_monica' AND time >= '2019-08-18T00:00:00Z' AND time <= '2019-08-18T00:18:00Z' tz('America/Chicago')

Output:

Name: h2o_feet

The query results include the UTC offset (-05:00) for the America/Chicago time zone in the timestamps.

Common issues with time syntax

Using OR to select time multiple time intervals

InfluxDB does not support using the OR operator in the WHERE clause to specify multiple time intervals.

For more information, see Frequently asked questions.

Querying data that occur after now() with a GROUP BY time() clause

Most SELECT statements have a default time range between 1677-09-21 00:12:43.145224194 and 2262-04-11T23:47:16.854775806Z UTC. For SELECT statements with a GROUP BY time() clause, the default time range is between 1677-09-21 00:12:43.145224194 UTC and now().

To query data with timestamps that occur after now(), SELECT statements with a GROUP BY time() clause must provide an alternative upper bound in the WHERE clause.