Documentation

SQL time and date functions

InfluxDB’s SQL implementation supports time and date functions that are useful when working with time series data.

current_date

Returns the current UTC date.

current_date returns a DATE32 Arrow type, which isn’t supported by InfluxDB. To use with InfluxDB, cast the return value to a timestamp.

The current_date() return value is determined at query time and will return the same date, no matter when in the query plan the function executes.

current_date()

View current_date query example

current_time

Returns the current UTC time.

current_date returns a TIME64 Arrow type, which isn’t supported by InfluxDB. To use with InfluxDB, cast the return value to a string.

The current_time() return value is determined at query time and will return the same time, no matter when in the query plan the function executes.

current_time()

View current_time query example

date_bin

Calculates time intervals and returns the start of the interval nearest to the specified timestamp. Use date_bin to downsample time series data by grouping rows into time-based “bins” or “windows” and applying an aggregate or selector function to each window.

For example, if you “bin” or “window” data into 15 minute intervals, an input timestamp of 2023-01-01T18:18:18Z will be updated to the start time of the 15 minute bin it is in: 2023-01-01T18:15:00Z.

date_bin(interval, expression[, origin_timestamp])
Arguments:
  • interval: Bin interval.
  • expression: Time expression to operate on. Can be a constant, column, or function.
  • origin_timestamp: Starting point used to determine bin boundaries. Default is the Unix epoch.

The following intervals are supported:

  • nanoseconds
  • microseconds
  • milliseconds
  • seconds
  • minutes
  • hours
  • days
  • weeks
  • months
  • years
  • century

View date_bin query example

date_bin_gapfill

Calculates time intervals and returns the start of the interval nearest to the specified timestamp. If no rows exist in a time interval, a new row is inserted with a time value set to the interval start time, all columns in the GROUP BY clause populated, and null values in aggregate columns.

Use date_bin_gapfill with interpolate or locf to fill gaps in data at specified time intervals.

date_bin_gapfill(interval, expression[, origin_timestamp])

date_bin_gapfill requires time bounds in the WHERE clause.

Arguments:
  • interval: Bin interval.
  • expression: Time expression to operate on. Can be a constant, column, or function.
  • origin_timestamp: Starting point used to determine bin boundaries. Default is the Unix epoch.

The following intervals are supported:

  • nanoseconds
  • microseconds
  • milliseconds
  • seconds
  • minutes
  • hours
  • days
  • weeks
  • months
  • years
  • century

interpolate, locf

View date_bin_gapfill query examples

date_trunc

Truncates a timestamp value to a specified precision.

date_trunc(precision, expression) 
Arguments:
  • precision: Time precision to truncate to. The following precisions are supported:

    • year
    • month
    • week
    • day
    • hour
    • minute
    • second
  • expression: Time expression to operate on. Can be a constant, column, or function.

Aliases
  • datetrunc

View date_trunc query examples

datetrunc

Alias of date_trunc.

date_part

Returns the specified part of the date as an integer.

date_part(part, expression)
Arguments:
  • part: Part of the date to return. The following date parts are supported:

    • year
    • month
    • week (week of the year)
    • day (day of the month)
    • hour
    • minute
    • second
    • millisecond
    • microsecond
    • nanosecond
    • dow (day of the week)
    • doy (day of the year)
  • expression: Time expression to operate on. Can be a constant, column, or function.

Aliases
  • datepart

View date_part query examples

datepart

Alias of date_part.

extract

Returns a sub-field from a time value as an integer. Similar to date_part, but with different arguments.

extract(field FROM source)
Arguments
  • field: Part or field of the date to return. The following date fields are supported:

    • year
    • month
    • week (week of the year)
    • day (day of the month)
    • hour
    • minute
    • second
    • millisecond
    • microsecond
    • nanosecond
    • dow (day of the week)
    • doy (day of the year)
  • source: Source time expression to operate on. Can be a constant, column, or function.

View extract query example

from_unixtime

Converts an integer to RFC3339 timestamp format (YYYY-MM-DDT00:00:00.000000000Z). Input is parsed as a Unix nanosecond timestamp and returns the corresponding RFC3339 timestamp.

from_unixtime(expression)
Arguments:
  • expression: Integer expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

View from_unixtime query example

now

Returns the current UTC timestamp.

The now() return value is determined at query time and will return the same timestamp, no matter when in the query plan the function executes.

now()

View now query example

to_timestamp

Converts a value to RFC3339 nanosecond timestamp format (YYYY-MM-DDT00:00:00.000000000Z). Supports timestamp, integer, and unsigned integer types as input. Integers and unsigned integers are parsed as Unix nanosecond timestamps and return the corresponding RFC3339 nanosecond timestamp.

to_timestamp(expression)
Arguments:
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

View to_timestamp query example

to_timestamp_millis

Converts a value to RFC3339 millisecond timestamp format (YYYY-MM-DDT00:00:00.000Z). Supports timestamp, integer, and unsigned integer types as input. Integers and unsigned integers are parsed as Unix nanosecond timestamps and return the corresponding RFC3339 timestamp.

to_timestamp_millis(expression) 
Arguments:
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

View to_timestamp_millis query example

to_timestamp_micros

Converts a value to RFC3339 microsecond timestamp format (YYYY-MM-DDT00:00:00.000000Z). Supports timestamp, integer, and unsigned integer types as input. Integers and unsigned integers are parsed as Unix nanosecond timestamps and return the corresponding RFC3339 timestamp.

to_timestamp_micros(expression)
Arguments:
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

View to_timestamp_micros query example

to_timestamp_seconds

Converts a value to RFC3339 second timestamp format (YYYY-MM-DDT00:00:00Z). Supports timestamp, integer, and unsigned integer types as input. Integers and unsigned integers are parsed as Unix nanosecond timestamps and return the corresponding RFC3339 timestamp.

to_timestamp_seconds(expression) 
Arguments:
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.

View to_timestamp_seconds query example


Was this page helpful?

Thank you for your feedback!


Introducing InfluxDB Clustered

A highly available InfluxDB 3.0 cluster on your own infrastructure.

InfluxDB Clustered is a highly available InfluxDB 3.0 cluster built for high write and query workloads on your own infrastructure.

InfluxDB Clustered is currently in limited availability and is only available to a limited group of InfluxData customers. If interested in being part of the limited access group, please contact the InfluxData Sales team.

Learn more
Contact InfluxData Sales

The future of Flux

Flux is going into maintenance mode. You can continue using it as you currently are without any changes to your code.

Flux is going into maintenance mode and will not be supported in InfluxDB 3.0. This was a decision based on the broad demand for SQL and the continued growth and adoption of InfluxQL. We are continuing to support Flux for users in 1.x and 2.x so you can continue using it with no changes to your code. If you are interested in transitioning to InfluxDB 3.0 and want to future-proof your code, we suggest using InfluxQL.

For information about the future of Flux, see the following: