Data Type Conversion Function

The IoTDB currently supports 6 data types, including INT32, INT64 ,FLOAT, DOUBLE, BOOLEAN, TEXT. When we query or evaluate data, we may need to convert data types, such as TEXT to INT32, or improve the accuracy of the data, such as FLOAT to DOUBLE. Therefore, IoTDB supports the use of cast functions to convert data types.

Function NameRequired AttributesOutput Series Data TypeSeries Data Type Description
CASTtype: the type of the output data point, it can only be INT32 / INT64 / FLOAT / DOUBLE / BOOLEAN / TEXTDetermined by the required attribute typeConverts data to the type specified by the type argument.

Notes

  1. The value of type BOOLEAN is true, when data is converted to BOOLEAN if INT32 and INT64 are not 0, FLOAT and DOUBLE are not 0.0, TEXT is not empty string or “false”, otherwise false.
  1. IoTDB> show timeseries root.sg.d1.*;
  2. +-------------+-----+-------------+--------+--------+-----------+----+----------+
  3. |   timeseries|alias|     database|dataType|encoding|compression|tags|attributes|
  4. +-------------+-----+-------------+--------+--------+-----------+----+----------+
  5. |root.sg.d1.s3| null|      root.sg|   FLOAT|     RLE|     SNAPPY|null|      null|
  6. |root.sg.d1.s4| null|      root.sg|  DOUBLE|     RLE|     SNAPPY|null|      null|
  7. |root.sg.d1.s5| null|      root.sg|    TEXT|   PLAIN|     SNAPPY|null|      null|
  8. |root.sg.d1.s6| null|      root.sg| BOOLEAN|     RLE|     SNAPPY|null|      null|
  9. |root.sg.d1.s1| null|      root.sg|   INT32|     RLE|     SNAPPY|null|      null|
  10. |root.sg.d1.s2| null|      root.sg|   INT64|     RLE|     SNAPPY|null|      null|
  11. +-------------+-----+-------------+--------+--------+-----------+----+----------+
  12. Total line number = 6
  13. It costs 0.006s
  14. IoTDB> select * from root.sg.d1;
  15. +-----------------------------+-------------+-------------+-------------+-------------+-------------+-------------+
  16. |                         Time|root.sg.d1.s3|root.sg.d1.s4|root.sg.d1.s5|root.sg.d1.s6|root.sg.d1.s1|root.sg.d1.s2|
  17. +-----------------------------+-------------+-------------+-------------+-------------+-------------+-------------+
  18. |1970-01-01T08:00:00.001+08:00|          1.1|          1.1|         test|        false|            1|            1|
  19. |1970-01-01T08:00:00.002+08:00|         -2.2|         -2.2|        false|         true|           -2|           -2|
  20. |1970-01-01T08:00:00.003+08:00|          0.0|          0.0|         true|         true|            0|            0|
  21. +-----------------------------+-------------+-------------+-------------+-------------+-------------+-------------+
  22. Total line number = 3
  23. It costs 0.009s
  24. IoTDB> select cast(s1, 'type'='BOOLEAN'), cast(s2, 'type'='BOOLEAN'), cast(s3, 'type'='BOOLEAN'), cast(s4, 'type'='BOOLEAN'), cast(s5, 'type'='BOOLEAN') from root.sg.d1;
  25. +-----------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+
  26. |                         Time|cast(root.sg.d1.s1, "type"="BOOLEAN")|cast(root.sg.d1.s2, "type"="BOOLEAN")|cast(root.sg.d1.s3, "type"="BOOLEAN")|cast(root.sg.d1.s4, "type"="BOOLEAN")|cast(root.sg.d1.s5, "type"="BOOLEAN")|
  27. +-----------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+
  28. |1970-01-01T08:00:00.001+08:00|                                 true|                                 true|                                 true|                                 true|                                 true|
  29. |1970-01-01T08:00:00.002+08:00|                                 true|                                 true|                                 true|                                 true|                                false|
  30. |1970-01-01T08:00:00.003+08:00|                                false|                                false|                                false|                                false|                                 true|
  31. +-----------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+-------------------------------------+
  32. Total line number = 3
  33. It costs 0.012s
  1. The value of type INT32, INT64, FLOAT, DOUBLE are 1 or 1.0 and TEXT is “true”, when BOOLEAN data is true, otherwise 0, 0.0 or “false”.
  1. IoTDB> select cast(s6, 'type'='INT32'), cast(s6, 'type'='INT64'), cast(s6, 'type'='FLOAT'), cast(s6, 'type'='DOUBLE'), cast(s6, 'type'='TEXT') from root.sg.d1;
  2. +-----------------------------+-----------------------------------+-----------------------------------+-----------------------------------+------------------------------------+----------------------------------+
  3. |                         Time|cast(root.sg.d1.s6, "type"="INT32")|cast(root.sg.d1.s6, "type"="INT64")|cast(root.sg.d1.s6, "type"="FLOAT")|cast(root.sg.d1.s6, "type"="DOUBLE")|cast(root.sg.d1.s6, "type"="TEXT")|
  4. +-----------------------------+-----------------------------------+-----------------------------------+-----------------------------------+------------------------------------+----------------------------------+
  5. |1970-01-01T08:00:00.001+08:00|                                  0|                                  0|                                0.0|                                 0.0|                             false|
  6. |1970-01-01T08:00:00.002+08:00|                                  1|                                  1|                                1.0|                                 1.0|                              true|
  7. |1970-01-01T08:00:00.003+08:00|                                  1|                                  1|                                1.0|                                 1.0|                              true|
  8. +-----------------------------+-----------------------------------+-----------------------------------+-----------------------------------+------------------------------------+----------------------------------+
  9. Total line number = 3
  10. It costs 0.016s
  1. When TEXT is converted to INT32, INT64, or FLOAT, the TEXT is first converted to DOUBLE and then to the corresponding type, which may cause loss of precision. It will skip directly if the data can not be converted.
  1. IoTDB> select cast(s5, 'type'='INT32'), cast(s5, 'type'='INT64'), cast(s5, 'type'='FLOAT') from root.sg.d1;
  2. +----+-----------------------------------+-----------------------------------+-----------------------------------+
  3. |Time|cast(root.sg.d1.s5, "type"="INT32")|cast(root.sg.d1.s5, "type"="INT64")|cast(root.sg.d1.s5, "type"="FLOAT")|
  4. +----+-----------------------------------+-----------------------------------+-----------------------------------+
  5. +----+-----------------------------------+-----------------------------------+-----------------------------------+
  6. Empty set.
  7. It costs 0.005s

Syntax

Example data:

  1. IoTDB> select text from root.test;
  2. +-----------------------------+--------------+
  3. |                         Time|root.test.text|
  4. +-----------------------------+--------------+
  5. |1970-01-01T08:00:00.001+08:00|           1.1|
  6. |1970-01-01T08:00:00.002+08:00|             1|
  7. |1970-01-01T08:00:00.003+08:00|   hello world|
  8. |1970-01-01T08:00:00.004+08:00|         false|
  9. +-----------------------------+--------------+

SQL:

  1. select cast(text, 'type'='BOOLEAN'), cast(text, 'type'='INT32'), cast(text, 'type'='INT64'), cast(text, 'type'='FLOAT'), cast(text, 'type'='DOUBLE') from root.test;

Result:

  1. +-----------------------------+--------------------------------------+------------------------------------+------------------------------------+------------------------------------+-------------------------------------+
  2. |                         Time|cast(root.test.text, "type"="BOOLEAN")|cast(root.test.text, "type"="INT32")|cast(root.test.text, "type"="INT64")|cast(root.test.text, "type"="FLOAT")|cast(root.test.text, "type"="DOUBLE")|
  3. +-----------------------------+--------------------------------------+------------------------------------+------------------------------------+------------------------------------+-------------------------------------+
  4. |1970-01-01T08:00:00.001+08:00|                                  true|                                   1|                                   1|                                 1.1|                                  1.1|
  5. |1970-01-01T08:00:00.002+08:00|                                  true|                                   1|                                   1|                                 1.0|                                  1.0|
  6. |1970-01-01T08:00:00.003+08:00|                                  true|                                null|                                null|                                null|                                 null|
  7. |1970-01-01T08:00:00.004+08:00|                                 false|                                null|                                null|                                null|                                 null|
  8. +-----------------------------+--------------------------------------+------------------------------------+------------------------------------+------------------------------------+-------------------------------------+
  9. Total line number = 4
  10. It costs 0.078s