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 or string.

The current_date() return value is determined at query time and returns 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 returns 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. Supports the following interval units:

    • nanoseconds
    • microseconds
    • milliseconds
    • seconds
    • minutes
    • hours
    • days
    • weeks
    • months
    • years
    • century
  • 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.

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. Supports the following interval units:

    • nanoseconds
    • microseconds
    • milliseconds
    • seconds
    • minutes
    • hours
    • days
    • weeks
    • months
    • years
    • century
  • 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.

interpolate, locf

Use date_bin_gapfill to insert rows when no rows exists

Use date_bin_gapfill to fill gaps in data

date_bin_wallclock

Calculates time intervals using the timezone of a specified time value and returns the start of the interval nearest to the specified timestamp. Use date_bin_wallclock to downsample time series data by grouping rows into time-based “bins” or “windows” that are based off “wall clock” times in a specific timezone and applying an aggregate or selector function to each window.

Time zone shifts

Many regions use time zone shifts (such as daylight saving time (DST)). If a wall clock time bin starts at a time that does not exist in the specified time zone, the timestamp is adjusted to the time that is the same offset from the start of the day in that time zone.

If a wall clock time represents an ambiguous time in the region then the behavior depends on the size of the specified interval. If the interval is larger than the difference between the two possible timestamps, then the earlier timestamp is used. Otherwise, the function uses the timestamp that matches the UTC offset of the input timestamp.

date_bin_wallclock(interval, expression[, origin_timestamp])
Arguments:
  • interval: Bin interval. Supports the following interval units:

    • nanoseconds
    • microseconds
    • milliseconds
    • seconds
    • minutes
    • hours
    • days
    • weeks

    date_bin_wallclock does not support month-, year-, or century-based intervals.

  • expression: Time expression to operate on. Can be a constant, column, or function. The output timestamp uses the time zone from this time expression.

  • origin_timestamp: Starting point used to determine bin boundaries. This must be a “wall clock” timestamp (no time zone). Default is the Unix epoch.

    Avoid bins in time zone discontinuities

    Time zone shifts result in discontinuities–breaks in the continuity of time intervals (losing an hour or gaining an hour)–that can result in unexpected timestamps when using date_bin_wallclock. Avoid using an interval and origin_timestamp combination that results in a bin falling inside a time discontinuity.

    As a general rule, use either the default origin_timestamp or an origin timestamp with an offset relative to the Unix epoch that is equal to your specified interval.

    View time zone discontinuity example

View date_bin_wallclock query example

date_bin_wallclock_gapfill

Calculates time intervals using the timezone of a specified time value 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_wallclock_gapfill with interpolate or locf to fill gaps in data at specified time intervals in a specified time zone.

Time zone shifts

Many regions use time zone shifts (such as daylight saving time (DST)). If a wall clock time bin starts at a time that does not exist in the specified time zone, the timestamp is adjusted to the time that is the same offset from the start of the day in that time zone.

If a wall clock time represents an ambiguous time in the region then the behavior depends on the size of the specified interval. If the interval is larger than the difference between the two possible timestamps, then the earlier timestamp is used. Otherwise, the function uses the timestamp that matches the UTC offset of the input timestamp.

date_bin_wallclock_gapfill(interval, expression[, origin_timestamp])

date_bin_wallclock_gapfill requires time bounds in the WHERE clause.

Arguments:
  • interval: Bin interval. Supports the following interval units:

    • nanoseconds
    • microseconds
    • milliseconds
    • seconds
    • minutes
    • hours
    • days
    • weeks

    date_bin_wallclock_gapfill does not support month-, year-, or century-based intervals.

  • expression: Time expression to operate on. Can be a constant, column, or function. The output timestamp uses the time zone from this time expression.

  • origin_timestamp: Starting point used to determine bin boundaries. This must be a “wall clock” timestamp (no time zone). Default is the Unix epoch.

    Avoid bins in time zone discontinuities

    Time zone shifts result in discontinuities–breaks in the continuity of time intervals (losing an hour or gaining an hour)–that can result in unexpected timestamps when using date_bin_wallclock_gapfill. Avoid using an interval and origin_timestamp combination that results in a bin falling inside a time discontinuity.

    As a general rule, use either the default origin_timestamp or an origin timestamp with an offset relative to the Unix epoch that is equal to your specified interval.

    View time zone discontinuity example

interpolate, locf

Use date_bin_wallclock_gapfill to insert rows when no rows exists

Use date_bin_wallclock_gapfill to fill gaps in data

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)
    • day (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)
    • day (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.

to_unixtime

View from_unixtime query example

make_date

Returns a date using the component parts (year, month, day).

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

make_date(year, month, day)
Arguments
  • year: Year to use when making the date. Can be a constant, column or function, and any combination of arithmetic operators.
  • month: Month to use when making the date. Can be a constant, column or function, and any combination of arithmetic operators.
  • day: Day to use when making the date. Can be a constant, column or function, and any combination of arithmetic operators

View make_date query example

now

Returns the current UTC timestamp.

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

now()

View now query example

today

Alias of current_date.

to_char

Returns the string representation of a date, time, timestamp, or duration based on a Rust Chrono format string.

Unlike the PostgreSQL TO_CHAR() function, this function does not support numeric formatting.

to_char(expression, format)
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function that results in a date, time, timestamp or duration.
  • format: Rust Chrono format string to use to convert the expression.

View to_char query example

to_date

Converts a value to a date (YYYY-MM-DD). Supports strings and numeric types as input. Strings are parsed as YYYY-MM-DD unless another format is specified. Numeric values are interpreted as days since the Unix epoch.

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

to_date(expression[, ..., format_n])
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
  • format_n: Optional Rust strftime pattern to use to parse the string expression. Formats are attempted in the order that they appear. The function returns the timestamp from the first format to parse successfully. If no formats parse successfully, the function returns an error.

View to_date query example

to_local_time

Converts a timestamp with a timezone to a timestamp without a timezone (no offset or timezone information). This function accounts for time shifts like daylight saving time (DST).

Use to_local_time() with date_bin() and date_bin_gapfill to generate window boundaries based the local time zone rather than UTC.

to_local_time(expression)
Arguments
  • expression: Time expression to operate on. Can be a constant, column, or function.

View to_local_time query example

View to_local_time query example with a time zone offset

View to_local_time query example with date_bin

to_timestamp

Converts a value to RFC3339 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(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_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 microsecond timestamps and return the corresponding RFC3339 timestamp.

to_timestamp_micros(expression[, ..., format_n])
Arguments:
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
  • format_n: Optional Rust strftime pattern to use to parse the string expression. Formats are attempted in the order that they appear. The function returns the timestamp from the first format to parse successfully. If no formats parse successfully, the function returns an error.

View to_timestamp_micros query example

View to_timestamp_micros example with string format parsing

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 millisecond timestamps and return the corresponding RFC3339 timestamp.

to_timestamp_millis(expression[, ..., format_n])
Arguments:
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
  • format_n: Optional Rust strftime pattern to use to parse the string expression. Formats are attempted in the order that they appear. The function returns the timestamp from the first format to parse successfully. If no formats parse successfully, the function returns an error.

View to_timestamp_millis query example

View to_timestamp_millis example with string format parsing

to_timestamp_nanos

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

to_timestamp_nanos(expression[, ..., format_n])
Arguments:
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
  • format_n: Optional Rust strftime pattern to use to parse the string expression. Formats are attempted in the order that they appear. The function returns the timestamp from the first format to parse successfully. If no formats parse successfully, the function returns an error.

View to_timestamp_nanos query example

View to_timestamp_nanos example with string format parsing

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 second timestamps and return the corresponding RFC3339 timestamp.

to_timestamp_seconds(expression[, ..., format_n]) 
Arguments:
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
  • format_n: Optional Rust strftime pattern to use to parse the string expression. Formats are attempted in the order that they appear. The function returns the timestamp from the first format to parse successfully. If no formats parse successfully, the function returns an error.

View to_timestamp_seconds query example

View to_timestamp_seconds example with string format parsing

to_unixtime

Converts a value to seconds since the Unix epoch. Supports strings, timestamps, and floats as input. Strings are parsed as RFC3339Nano timestamps if no Rust Chrono format strings are provided.

to_unixtime(expression[, ..., format_n])
Arguments
  • expression: Expression to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
  • format_n: Optional Rust strftime pattern to use to parse the string expression. Formats are attempted in the order that they appear. The function returns the timestamp from the first format to parse successfully. If no formats parse successfully, the function returns an error.

from_unixtime

View to_unixtime query example

View to_unixtime example with string format parsing

tz

Converts a timestamp to a provided timezone. If the second argument is not provided, it defaults to UTC.

tz(time_expression[, timezone])
Arguments
  • time_expression: time to operate on. Can be a constant, column, or function, and any combination of arithmetic operators.
  • timezone: Timezone string to cast the value into. Default is 'UTC'. The function returns the timestamp cast to the specified timezone. If an incorrect timezone string is passed or the wrong datatype is provided, the function returns an error.

View tz query example

View tz query example from Getting Started data

Differences between tz and AT TIME ZONE

tz and AT TIME ZONE differ when the input timestamp does not have a timezone.

  • When using an input timestamp that does not have a timezone (the default behavior in InfluxDB) with the AT TIME ZONE operator, the operator returns the the same timestamp, but with a timezone offset (also known as the “wall clock” time)–for example:

    '2024-01-01 00:00:00'::TIMESTAMP AT TIME ZONE 'America/Los_Angeles'
    
    -- Returns
    2024-01-01T00:00:00-08:00
    
  • When using an input timestamp with a timezone, both the tz() function and the AT TIME ZONE operator return the timestamp converted to the time in the specified timezone–for example:

    '2024-01-01T00:00:00-00:00' AT TIME ZONE 'America/Los_Angeles'
    tz('2024-01-01T00:00:00-00:00', 'America/Los_Angeles')
    
    -- Both return
    2023-12-31T16:00:00-08:00
    
  • tz() always converts the input timestamp to the specified time zone. If the input timestamp does not have a timezone, the function assumes it is a UTC timestamp–for example:

    tz('2024-01-01 00:00:00'::TIMESTAMP, 'America/Los_Angeles')
    -- Returns
    2023-12-31T16:00:00-08:00
    
    tz('2024-01-01T00:00:00+1:00', 'America/Los_Angeles')
    -- Returns
    2023-12-31T15:00:00-08:00
    

View tz and ::timestamp comparison


Was this page helpful?

Thank you for your feedback!


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.

Read more

InfluxDB v3 enhancements and InfluxDB Clustered is now generally available

New capabilities, including faster query performance and management tooling advance the InfluxDB v3 product line. InfluxDB Clustered is now generally available.

InfluxDB v3 performance and features

The InfluxDB v3 product line has seen significant enhancements in query performance and has made new management tooling available. These enhancements include an operational dashboard to monitor the health of your InfluxDB cluster, single sign-on (SSO) support in InfluxDB Cloud Dedicated, and new management APIs for tokens and databases.

Learn about the new v3 enhancements


InfluxDB Clustered general availability

InfluxDB Clustered is now generally available and gives you the power of InfluxDB v3 in your self-managed stack.

Talk to us about InfluxDB Clustered

InfluxDB Cloud Serverless