Skip to main content

Data Types

Timestamp

Using TDengine, the most important aspect is the timestamp. When creating and inserting records, or querying historical records, specifying the timestamp is necessary. The rules for timestamps are as follows:

  • The time format is YYYY-MM-DD HH:mm:ss.MS, with the default time resolution being milliseconds. For example: 2017-08-12 18:25:58.128
  • The internal function NOW represents the current time of the client
  • When inserting records, if the timestamp is NOW, the current time of the client submitting the record is used
  • Epoch Time: The timestamp can also be a long integer, representing the number of milliseconds since UTC time 1970-01-01 00:00:00. Accordingly, if the time precision of the Database is set to "microseconds", the meaning of the long integer format timestamp corresponds to the number of microseconds since UTC time 1970-01-01 00:00:00; the logic for nanoseconds precision is similar.
  • Time can be added or subtracted, such as NOW-2h, which indicates pushing the query time forward by 2 hours (the last 2 hours). The time unit after the number can be b (nanoseconds), u (microseconds), a (milliseconds), s (seconds), m (minutes), h (hours), d (days), w (weeks). For example SELECT * FROM t1 WHERE ts > NOW-2w AND ts <= NOW-1w, represents querying data for a whole week two weeks ago. When specifying the time window (Interval) for Down Sampling operations, the time unit can also use n (natural month) and y (natural year).

TDengine's default timestamp precision is milliseconds, but microseconds and nanoseconds are also supported by passing the PRECISION parameter during CREATE DATABASE.

CREATE DATABASE db_name PRECISION 'ns';

Data Types

In TDengine, the following data types can be used in the data model of basic tables.

#TypeBytesDescription
1TIMESTAMP8Timestamp. Default precision is milliseconds, supports microseconds and nanoseconds, see the previous section for details.
2INT4Integer, range [-2^31, 2^31-1]
3INT UNSIGNED4Unsigned integer, [0, 2^32-1]
4BIGINT8Long integer, range [-2^63, 2^63-1]
5BIGINT UNSIGNED8Long integer, range [0, 2^64-1]
6FLOAT4Float, precision 6-7 digits, range [-3.4E38, 3.4E38]
7DOUBLE8Double precision float, precision 15-16 digits, range [-1.7E308, 1.7E308]
8BINARYCustomRecords single-byte strings, recommended for handling ASCII visible characters only, multi-byte characters such as Chinese should use NCHAR
9SMALLINT2Short integer, range [-32768, 32767]
10SMALLINT UNSIGNED2Unsigned short integer, range [0, 65535]
11TINYINT1Single-byte integer, range [-128, 127]
12TINYINT UNSIGNED1Unsigned single-byte integer, range [0, 255]
13BOOL1Boolean,
14NCHARCustomRecords strings including multi-byte characters, such as Chinese characters. Each NCHAR character occupies 4 bytes of storage space. Strings are enclosed in single quotes, and single quotes within the string are escaped with \'. NCHAR usage must specify the string size, a column of type NCHAR(10) indicates that this column can store up to 10 NCHAR characters. If the user's string length exceeds the declared length, an error will be reported.
15JSONJSON data type, only Tags can be in JSON format
16VARCHARCustomAlias for BINARY type
17GEOMETRYCustomGeometry type, supported starting from version 3.1.0.0
18VARBINARYCustomVariable-length binary data, supported starting from version 3.1.1.0
note
  • The maximum length of each row in a table cannot exceed 48KB (64KB starting from version 3.0.5.0) (Note: Each BINARY/NCHAR/GEOMETRY/VARBINARY type column will also occupy an additional 2 bytes of storage space).

  • Although the BINARY type supports byte-type binary characters at the storage level, different programming languages do not guarantee consistent handling of binary data. Therefore, it is recommended to store only ASCII visible characters in the BINARY type and avoid storing invisible characters. Multibyte data, such as Chinese characters, should be saved using the NCHAR type. If Chinese characters are forcibly saved using the BINARY type, although they can sometimes be read and written normally, they do not carry character set information, which can easily lead to data corruption or even data damage.

  • Theoretically, the BINARY type can be up to 16,374 bytes long (from version 3.0.5.0, data columns are 65,517 bytes, label columns are 16,382 bytes). BINARY only supports string input, which must be enclosed in single quotes. When used, the size must be specified, such as BINARY(20) defines a string up to 20 single-byte characters long, with each character occupying 1 byte of storage space, totaling a fixed 20 bytes of space. If the user's string exceeds 20 bytes, an error will be reported. For single quotes within the string, they can be represented by the escape character backslash followed by a single quote, i.e., \'.

  • GEOMETRY type data columns have a maximum length of 65,517 bytes, and label columns have a maximum length of 16,382 bytes. Supports 2D subtypes of POINT, LINESTRING, and POLYGON data. The length calculation method is shown in the following table:

    #SyntaxMinimum LengthMaximum LengthIncrement per Coordinate Set
    1POINT(1.0 1.0)2121None
    2LINESTRING(1.0 1.0, 2.0 2.0)9+2*169+4094*16+16
    3POLYGON((1.0 1.0, 2.0 2.0, 1.0 1.0))13+3*1613+4094*16+16
  • In SQL statements, the type of numerical values will be determined based on the presence of a decimal point or the use of scientific notation, so care must be taken to avoid type overflow. For example, 9999999999999999999 will be considered to exceed the upper boundary of long integers and overflow, while 9999999999999999999.0 will be considered a valid floating point number.

  • VARBINARY is a data type for storing binary data, with a maximum length of 65,517 bytes for data columns and 16,382 bytes for label columns. Binary data can be written via SQL or schemaless methods (needs to be converted to a string starting with \x), or through stmt methods (can use binary directly). Displayed as hexadecimal starting with \x.

Constants

TDengine supports multiple types of constants, details as shown in the table below:

#SyntaxTypeDescription
1[{+ | -}]123BIGINTThe literal type of integer values is always BIGINT. If the user input exceeds the range of BIGINT, TDengine truncates the value as BIGINT.
2123.45DOUBLEThe literal type of floating-point values is always DOUBLE. TDengine determines whether the value is an integer or floating point based on the presence of a decimal point or the use of scientific notation.
31.2E3DOUBLEThe literal type for scientific notation is DOUBLE.
4'abc'BINARYContent enclosed in single quotes is a string literal, whose type is BINARY. The size of BINARY is the actual number of characters. For single quotes within the string, they can be represented by the escape character backslash followed by a single quote, i.e., \'.
5"abc"BINARYContent enclosed in double quotes is a string literal, whose type is BINARY. The size of BINARY is the actual number of characters. For double quotes within the string, they can be represented by the escape character backslash followed by a single quote, i.e., \".
6TIMESTAMP {'literal' | "literal"}TIMESTAMPThe TIMESTAMP keyword indicates that the following string literal should be interpreted as a TIMESTAMP type. The string must meet the YYYY-MM-DD HH:mm:ss.MS format, with the time resolution being that of the current database.
7{TRUE | FALSE}BOOLBoolean type literal.
8{'' | "" | '\t' | "\t" | ' ' | " " | NULL }--Null value literal. Can be used for any type.
note
  • TDengine determines whether a numeric type is an integer or a floating-point based on the presence of a decimal point or the use of scientific notation. Therefore, be aware of potential type overflow when using it. For example, 9999999999999999999 is considered to exceed the upper boundary of a long integer and will overflow, while 9999999999999999999.0 is considered a valid floating-point number.