Data Sources: Edit Columns

DataLion supports multiple categories of SQL data types, such as numeric types, date and time types, and string types.

The data types can be edited in the backend under "Data Sources":

This article provides an overview and a more detailed description of the properties of the types in each category, as well as a summary of storage requirements for data types.

(1) Numeric data types

DataLion supports the standard SQL numeric data types. These types include the exact numeric data types (INTEGER, SMALLINT, TINYINT, BIGINT, and DECIMAL) and the approximate numeric data types (FLOAT and DOUBLE).

Integer Data Types (Exact Values) - INTEGER, SMALLINT, TINYINT, BIGINT

Type	Storage (Bytes)	Min. Value Signed	Min. Value Unsigned	Max. Value Signed	Max. Value Unsigned
TINYINT	1	-128	0	127	255
SMALLINT	2	-32768	0	32767	65535
INTEGER	4	-2147483648	0	2147483647	4294967295
BIGINT	8	-2^63	0	2^63 -1	2^64 -1

Fixed-Point Types (Exact Value) - DECIMAL

The DECIMAL type stores exact numeric data values. This type is used when it is important to get exact accuracy, for example with monetary data.

In this example, 10 is the column width ("Column width") and 2 is the number of decimal places ("Scale").

If the scale is 0, DECIMAL values do not contain a decimal point or a fractional part.

Floating point types (approximate value) - FLOAT, DOUBLE

The types FLOAT and DOUBLE represent approximate numeric data values. For FLOAT, the SQL standard allows an optional specification of the precision in bits.

A precision of 0 to 23 results in a 4-byte single-precision FLOAT column. A precision of 24 to 53 results in a 8-byte double-precision DOUBLE column.

(2) Date and time types

The date and time data types used to represent time values are DATE, TIME, and DATETIME. Each time type has a range of valid values, as well as a "zero" value that can be used, if you put an invalid value that MySQL cannot represent.

DATE

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in the format 'YYYY-MM-DD'. The supported range is '1000-01-01' to '9999-12-31'.

DATETIME

The DATETIME type is used for values that contain both dates and times. MySQL retrieves and displays DATETIME values in the format 'YYYY-MM-DD hh:mm:ss'. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

TIME

MySQL retrieves and displays TIME values in 'hh:mm:ss' format (or in 'hhh:mm:ss' format for large hour values). TIME values can range from '-838:59:59' to '838:59:59'. The hour portion can be so large because the TIME type can be used not only to represent a time of the day (which must be less than 24 hours) but also the elapsed time or time interval between two events (which may be much greater than 24 hours or even negative).

Be careful when assigning abbreviated values to a TIME column. MySQL interprets abbreviated TIME values with colons as the time of the day. That is, '11:12' means '11:12:00', not '00:11:12'.

(3) STRING data types

Values in STRING (VARCHAR) columns are variable-length strings. The length can be specified with a value between 0 and 65.535. The maximum effective length of a VARCHAR depends on the maximum row size (65.535 bytes shared by all columns) and the character set used.

(4) BOOLEAN data types

Interpretation of true and false values.