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

Cast Unix nanosecond timestamps to a timestamp type

To cast a Unix nanosecond timestamp to a timestamp type, first cast the numeric value to an unsigned integer (BIGINT UNSIGNED) and then a timestamp. You can also use the to_timestamp_nanos function.

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:


Was this page helpful?

Thank you for your feedback!


The future of Flux

Flux is going into maintenance mode. You can continue using it as you currently are without any changes to your code.

Read more

InfluxDB v3 enhancements and InfluxDB Clustered is now generally available

New capabilities, including faster query performance and management tooling advance the InfluxDB v3 product line. InfluxDB Clustered is now generally available.

InfluxDB v3 performance and features

The InfluxDB v3 product line has seen significant enhancements in query performance and has made new management tooling available. These enhancements include an operational dashboard to monitor the health of your InfluxDB cluster, single sign-on (SSO) support in InfluxDB Cloud Dedicated, and new management APIs for tokens and databases.

Learn about the new v3 enhancements


InfluxDB Clustered general availability

InfluxDB Clustered is now generally available and gives you the power of InfluxDB v3 in your self-managed stack.

Talk to us about InfluxDB Clustered