Data Types

Timestamp

When using TDengine to store and query data, the most important part of the data is timestamp. Timestamp must be specified when creating and inserting data rows. Timestamp must follow the rules below:

• The format must be YYYY-MM-DD HH:mm:ss.MS, the default time precision is millisecond (ms), for example 2017-08-12 18:25:58.128.
• Internal function NOW can be used to get the current timestamp on the client side.
• The current timestamp of the client side is applied when NOW is used to insert data.
• Epoch Time: timestamp can also be a long integer number, which means the number of seconds, milliseconds or nanoseconds, depending on the time precision, from UTC 1970-01-01 00:00:00.
• Add/subtract operations can be carried out on timestamps. For example NOW-2h means 2 hours prior to the time at which query is executed. The units of time in operations can be b(nanosecond), u(microsecond), a(millisecond), s(second), m(minute), h(hour), d(day), or w(week). So SELECT * FROM t1 WHERE ts > NOW-2w AND ts <= NOW-1w means the data between two weeks ago and one week ago. The time unit can also be n (calendar month) or y (calendar year) when specifying the time window for down sampling operations.

Time precision in TDengine can be set by the PRECISION parameter when executing CREATE DATABASE. The default time precision is millisecond. In the statement below, the precision is set to nanonseconds.

CREATE DATABASE db_name PRECISION 'ns';

Data Types

In TDengine, the data types below can be used when specifying a column or tag.

#	type	Bytes	Description
1	TIMESTAMP	8	Default precision is millisecond, microsecond and nanosecond are also supported.
2	INT	4	Integer, the value range is [-2^31, 2^31-1].
3	INT UNSIGNED	4	Unsigned integer, the value range is [0, 2^32-1].
4	BIGINT	8	Long integer, the value range is [-2^63, 2^63-1].
5	BIGINT UNSIGNED	8	unsigned long integer, the value range is [0, 2^64-1].
6	FLOAT	4	Floating point number, the effective number of digits is 6-7, the value range is [-3.4E38, 3.4E38].
7	DOUBLE	8	Double precision floating point number, the effective number of digits is 15-16, the value range is [-1.7E308, 1.7E308].
8	BINARY	User Defined	Single-byte string for ASCII visible characters. Length must be specified when defining a column or tag of binary type.
9	SMALLINT	2	Short integer, the value range is [-32768, 32767].
10	SMALLINT UNSIGNED	2	unsigned integer, the value range is [0, 65535].
11	TINYINT	1	Single-byte integer, the value range is [-128, 127].
12	TINYINT UNSIGNED	1	unsigned single-byte integer, the value range is [0, 255].
13	BOOL	1	Bool, the value range is .
14	NCHAR	User Defined	Multi-byte string that can include multi byte characters like Chinese characters. Each character of NCHAR type consumes 4 bytes storage. The string value should be quoted with single quotes. Literal single quote inside the string must be preceded with backslash, like \'. The length must be specified when defining a column or tag of NCHAR type, for example nchar(10) means it can store at most 10 characters of nchar type and will consume fixed storage of 40 bytes. An error will be reported if the string value exceeds the length defined.
15	JSON		JSON type can only be used on tags. A tag of json type is excluded with any other tags of any other type.
16	VARCHAR	User-defined	Alias of BINARY
17	GEOMETRY	User-defined	Geometry
18	VARBINARY	User-defined	Binary data with variable length

note

• Each row of the table cannot be longer than 48KB (64KB since version 3.0.5.0) (note that each BINARY/NCHAR/GEOMETRY column takes up an additional 2 bytes of storage space).

• Only ASCII visible characters are suggested to be used in a column or tag of BINARY type. Multi-byte characters must be stored in NCHAR type.

• The length of BINARY can be up to 16,374(data column is 65,517 and tag column is 16,382 since version 3.0.5.0) bytes. The string value must be quoted with single quotes. You must specify a length in bytes for a BINARY value, for example binary(20) for up to twenty single-byte characters. If the data exceeds the specified length, an error will occur. The literal single quote inside the string must be preceded with back slash like \'

• The maximum length of the GEOMETRY data column is 65,517 bytes, and the maximum length of the tag column is 16,382 bytes. Supports POINT, LINESTRING, and POLYGON subtypes of 2D. The following table describes the length calculation method:

  #	Syntax	MinLen	MaxLen	Growth of each point
  1	POINT(1.0 1.0)	21	21	NA
  2	LINESTRING(1.0 1.0, 2.0 2.0)	9+2*16	9+4094*16	+16
  3	POLYGON((1.0 1.0, 2.0 2.0, 1.0 1.0))	13+3*16	13+4094*16	+16

• Numeric values in SQL statements will be determined as integer or float type according to whether there is decimal point or whether scientific notation is used, so attention must be paid to avoid overflow. For example, 9999999999999999999 will be considered as overflow because it exceeds the upper limit of long integer, but 9999999999999999999.0 will be considered as a legal float number.

• VARBINARY is a data type that stores binary data, with a maximum length of 65517 bytes and a maximum length of 16382 bytes for tag columns. Binary data can be written through SQL or schemaless (which needs to be converted to a string starting with \x), or written through stmt (which can directly use binary). Display starting with hexadecimal starting with \x.

Constants

TDengine supports a variety of constants:

#	Syntax	Type	Description
1	[+ | -]123	BIGINT	Integer literals are of type BIGINT. Data that exceeds the length of the BIGINT type is truncated.
2	123.45	DOUBLE	Floating-point literals are of type DOUBLE. Numeric values will be determined as integer or float type according to whether there is decimal point or whether scientific notation is used.
3	1.2E3	DOUBLE	Literals in scientific notation are of type DOUBLE.
4	'abc'	BINARY	Content enclosed in single quotation marks is of type BINARY. The size of a BINARY is the size of the string in bytes. A literal single quote inside the string must be escaped with a backslash \'.
5	'abc'	BINARY	Content enclosed in double quotation marks is of type BINARY. The size of a BINARY is the size of the string in bytes. A literal double quote inside the string must be escaped with a backslash \".
6	TIMESTAMP ['literal' | "literal"]	TIMESTAMP	The TIMESTAMP keyword indicates that the following string literal is interpreted as a timestamp. The string must be in YYYY-MM-DD HH:mm:ss.MS format. The precision is inherited from the database configuration.
7	[TRUE | FALSE]	BOOL	Boolean literals are of type BOOL.
8	['' | "" | '\t' | "\t" | ' ' | " " | NULL ]	--	The preceding characters indicate null literals. These can be used with any data type.

note

Numeric values will be determined as integer or float type according to whether there is decimal point or whether scientific notation is used, so attention must be paid to avoid overflow. For example, 9999999999999999999 will be considered as overflow because it exceeds the upper limit of long integer, but 9999999999999999999.0 will be considered as a legal float number.