Documentation

Cast values to different types

Use the CAST function or double-colon :: casting shorthand syntax to cast a value to a specific type.

-- CAST function
SELECT CAST(1234.5 AS BIGINT)

-- Double-colon casting shorthand
SELECT 1234.5::BIGINT

Casting operations can be performed on a column expression or a literal value. For example, the following query uses the get started sample data and:

  • Casts all values in the time column to integers (Unix nanosecond timestamps).
  • Casts the literal string value '1234' to a 64-bit float for each row.
SELECT
  time::BIGINT AS unix_time,
  '1234'::DOUBLE AS string_to_float
FROM home
LIMIT 5
SELECT
  CAST(time AS BIGINT) AS unix_time,
  CAST('1234' AS DOUBLE) AS string_to_float
FROM home
LIMIT 5
unix_timestring_to_float
16410240000000000001234
16410276000000000001234
16410312000000000001234
16410348000000000001234
16410384000000000001234

Cast to a string type

Use the STRING, CHAR, VARCHAR, or TEXT type in a casting operation to cast a value to a string.

value::STRING
value::CHAR
value::VARCHAR
value::TEXT
CAST(value AS STRING)
CAST(value AS CHAR)
CAST(value AS VARCHAR)
CAST(value AS TEXT)

SQL supports casting the following to a string value:

  • Floats
  • Integers
  • Unsigned integers
  • Booleans
  • Timestamps

Cast to numeric types

The InfluxDB SQL implementation supports 64-bit floats (DOUBLE), integers (BIGINT), and unsigned integers (BIGINT UNSIGNED).

Cast to a float

Use the DOUBLE type in a casting operation to cast a value to a 64-bit float.

value::DOUBLE
CAST(value AS DOUBLE)

SQL supports casting the following to a float value:

  • Strings: Returns the float equivalent of the numeric string ([0-9]). The following string patterns are also supported:

    • Scientific notation ('123.4E+10')
    • Infinity ('±Inf')
    • NaN ('NaN')
  • Integers

  • Unsigned integers

Cast to an integer

Use the BIGINT type in a casting operation to cast a value to a 64-bit signed integer.

value::BIGINT
CAST(value AS BIGINT)

SQL supports casting the following to an integer:

  • Strings: Returns the integer equivalent of the numeric string ([0-9]).
  • Floats: Truncates the float value at the decimal.
  • Unsigned integers: Returns the signed integer equivalent of the unsigned integer.
  • Booleans: Returns 1 for true and 0 for false.
  • Timestamps: Returns the equivalent nanosecond epoch timestamp.

Cast to an unsigned integer

Use the BIGINT UNSIGNED type in a casting operation to cast a value to a 64-bit unsigned integer.

value::BIGINT UNSIGNED
CAST(value AS BIGINT UNSIGNED)

SQL supports casting the following to an unsigned integer:

  • Strings: Returns the unsigned integer equivalent of the numeric string ([0-9]).
  • Floats: Truncates the float value at the decimal.
  • Integers: Returns the unsigned integer equivalent of the signed integer.
  • Booleans: Returns 1 for true and 0 for false.
  • Timestamps: Returns the equivalent nanosecond epoch timestamp.

Cast to a boolean type

Use the BOOLEAN type in a casting operation to cast a value to a boolean.

value::BOOLEAN
CAST(value AS BOOLEAN)

SQL supports casting the following to a boolean:

  • Strings
    • Return true:
      • 'true' (case-insensitive)
      • 't', (case-insensitive)
      • '1'
    • Return false:
      • 'false' (case-insensitive)
      • 'f' (case-insensitive)
      • '0'
  • Integers
    • Returns true: positive non-zero integer
    • Returns false: 0
  • Unsigned integers
    • Returns true: non-zero unsigned integer
    • Returns false: 0

Cast to a timestamp type

Use the TIMESTAMP type in a casting operation to cast a value to a timestamp.

value::TIMESTAMP
CAST(value AS TIMESTAMP)

SQL supports casting the following to a boolean:

  • Strings: Returns the timestamp equivalent of the string value. The following RFC3339 and RFC339-like string patterns are supported:

    • YYYY-MM-DDT00:00:00.000Z
    • YYYY-MM-DDT00:00:00.000-00:00
    • YYYY-MM-DD 00:00:00.000-00:00
    • YYYY-MM-DDT00:00:00Z
    • YYYY-MM-DD 00:00:00.000
    • YYYY-MM-DD 00:00:00
    • YYYY-MM-DD
  • Integers: Parses the integer as a Unix nanosecond timestamp and returns the equivalent timestamp.

  • Unsigned integers: Parses the unsigned integer as a Unix nanosecond timestamp and returns the equivalent timestamp.


Was this page helpful?

Thank you for your feedback!


Linux Package Signing Key Rotation

All signed InfluxData Linux packages have been resigned with an updated key. If using Linux, you may need to update your package configuration to continue to download and verify InfluxData software packages.

For more information, see the Linux Package Signing Key Rotation blog post.

State of the InfluxDB Cloud (IOx) documentation

The new documentation for InfluxDB Cloud backed by InfluxDB IOx is a work in progress. We are adding new information and content almost daily. Thank you for your patience!

If there is specific information you’re looking for, please submit a documentation issue.

InfluxDB Cloud powered by IOx