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

• Cast to a string type
• Cast to numeric types
  • Float
  • Integer
  • Unsigned integer
• Cast to a boolean type
• Cast to a timestamp type

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

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

• 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 second timestamp and returns the equivalent timestamp.

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

1704067200000000000::BIGINT UNSIGNED::TIMESTAMP

CAST(CAST(1704067200000000000 AS BIGINT UNSIGNED) AS TIMESTAMP)

to_timestamp_nanos(1704067200000000000)

Timestamp functions

You can also use the following SQL functions to cast a value to a timestamp type:

• to_timestamp
• to_timestamp_millis
• to_timestamp_micros
• to_timestamp_nanos
• to_timestamp_seconds