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
- current_time
- date_bin
- date_bin_gapfill
- date_bin_wallclock
- date_bin_wallclock_gapfill
- date_trunc
- datetrunc
- date_part
- datepart
- extract
- from_unixtime
- make_date
- now
- today
- to_char
- to_date
- to_local_time
- to_timestamp
- to_timestamp_micros
- to_timestamp_millis
- to_timestamp_nanos
- to_timestamp_seconds
- to_unixtime
- tz
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()
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()
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.
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.
Related functions
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 aninterval
andorigin_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 specifiedinterval
.
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 aninterval
andorigin_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 specifiedinterval
.
Related functions
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
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
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.
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.
Related functions
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
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()
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.
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.
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.
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.
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.
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.
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.
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.
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.
Related functions
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.
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 theAT 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
Was this page helpful?
Thank you for your feedback!
Support and feedback
Thank you for being part of our community! We welcome and encourage your feedback and bug reports for InfluxDB and this documentation. To find support, use the following resources:
Customers with an annual or support contract can contact InfluxData Support.