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_trunc
• datetrunc
• date_part
• datepart
• extract
• from_unixtime
• now
• to_timestamp
• to_timestamp_millis
• to_timestamp_micros
• to_timestamp_seconds

current_date

Returns the current UTC date.

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()

SELECT
  time,
  temp,
  current_date()::TIMESTAMP AS current_date
FROM home
WHERE
  time > current_date()::TIMESTAMP - INTERVAL '5 years'
LIMIT 3

current_time

Returns the current UTC time.

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()

SELECT
  time,
  temp,
  current_time()::STRING AS current_time
FROM home
LIMIT 3

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

SELECT
  date_bin(INTERVAL '1 day', time, TIMESTAMP '1970-01-01 00:00:00Z') AS time,
  avg("water_level") AS water_level_avg
FROM "h2o_feet"
WHERE
  time >= timestamp '2019-09-10T00:00:00Z'
  AND time <= timestamp '2019-09-20T00:00:00Z'
GROUP BY 1
ORDER BY time DESC

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])

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

The following intervals are not currently supported:

• months
• years
• century

Related functions

interpolate, locf

SELECT
  date_bin_gapfill(INTERVAL '30 minutes', time) as _time,
  room,
  avg(temp) as temp
FROM home
WHERE
    time >= '2022-01-01T08:00:00Z'
    AND time <= '2022-01-01T10:00:00Z'
GROUP BY _time, room

SELECT
  date_bin_gapfill(INTERVAL '30 minutes', time) as _time,
  room,
  interpolate(avg(temp))
FROM home
WHERE
    time >= '2022-01-01T08:00:00Z'
    AND time <= '2022-01-01T10:00:00Z'
GROUP BY _time, room

SELECT
  date_bin_gapfill(INTERVAL '30 minutes', time) as _time,
  room,
  locf(avg(temp))
FROM home
WHERE
    time >= '2022-01-01T08:00:00Z'
    AND time <= '2022-01-01T10:00:00Z'
GROUP BY _time, room

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

SELECT
  avg(water_level) AS level,
  date_trunc('hour', time) AS hour
FROM h2o_feet
WHERE
  time >= timestamp '2019-09-10T00:00:00Z'
  AND time <= timestamp '2019-09-12T00:00:00Z'
GROUP BY hour
ORDER BY hour

SELECT
  mean(water_level) as level,
  date_trunc('week',time) AS week
FROM h2o_feet
WHERE
  time >= timestamp '2019-08-01T00:00:00Z'
  AND time <= timestamp '2019-10-31T00:00:00Z'
GROUP BY week
ORDER BY week

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

SELECT
  date_part('hour', time) AS hour,
  time,
  "level description", 
  location
FROM h2o_feet
WHERE
  time >= timestamp '2019-08-17T02:54:00Z'
  AND time <= timestamp '2019-08-17T03:06:00Z'
ORDER BY time

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.

SELECT 
  extract(day from time) AS day
FROM 
  h2o_feet 
LIMIT 1

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.

SELECT
  from_unixtime(1672531200000000000) AS RFC3339

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()

SELECT
  "water_level",
  "time"
FROM h2o_feet
WHERE
  time <= now() - interval '12 minutes'

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.

SELECT to_timestamp(1704067200000000000)

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

SELECT to_timestamp_millis(1704067200001) AS time

SELECT to_timestamp_millis('01:01:59.123456789 01-01-2024', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y') AS millisecond

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

SELECT to_timestamp_micros(1704067200000001)

SELECT to_timestamp_micros('01:01:59.123456789 01-01-2024', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y') AS microsecond

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

SELECT to_timestamp_nanos(1704067200000000001)

SELECT to_timestamp_nanos('01:01:59.123456789 01-01-2024', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y') AS nanosecond

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

SELECT to_timestamp_seconds(1704067201)

SELECT to_timestamp_seconds('01:01:59.123456789 01-01-2024', '%c', '%+', '%H:%M:%S%.f %m-%d-%Y') AS second