SQL time and date functions

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

• now
• date_bin
• date_trunc
• date_part
• extract
• to_timestamp
• to_timestamp_millis
• to_timestamp_micros
• to_timestamp_seconds
• from_unixtime

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'

date_bin

Updates a timestamp to the start of the nearest specified interval. 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: Column or timestamp literal to operate on.
• timestamp: Starting point used to determine bin boundaries.

The following intervals are supported:

• 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 time
ORDER BY time DESC

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: Column or timestamp literal to operate on.

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

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 follow 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: Column or timestamp literal to operate on.

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

extract

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

extract(field FROM source)

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

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: Column or literal value to operate on.

SELECT to_timestamp(time) 
FROM h2o_feet
LIMIT 1

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: Column or literal value to operate on.

SELECT
  to_timestamp_millis(time) 
FROM 
  h2o_temperature
LIMIT 1

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: Column or literal value to operate on.

SELECT
  to_timestamp_micros(time)
FROM 
  cpu
LIMIT 1

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: Column or literal value to operate on.

SELECT
  to_timestamp_seconds(time)
FROM 
  cpu
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: Column or integer literal to operate on.

SELECT
  from_unixtime(1672531200000000000) AS RFC3339