CodesJava

Easy learning with example program codes

Data Types in SQLite

SQLite Data Types

A more general dynamic type data type system is used in SQLite to represent the type of data that can be stored and processed and to specify the type of operations that can be performed. These data types are divided into various categories, where the value of a data type is not associated with its container but is associated with the value itself.

 

SQLite Storage Classes:

One of the five storage classes that SQLite supports is associated with each stored value in a SQLite database. These storage classes are listed below:

 

STORAGE CLASSUSES

NULL

Value is a null value.

INTEGER

Value is a signed integer.

Depending on the magnitude of the value, it can be stored in 1, 2, 3, 4, 6, or 8 bytes.

REAL

Value is a floating point value.

Stored as an 8-byte IEEE floating point number.

text

Value is a text string.

Stored using the database encoding (utf-8, utf-16be or utf-16le)

BLOB

Value is a blob of data.

Stored exactly as it was input.

 

SQLite Affinity Types:

The preferred storage class for a column is called its affinity. There are five types of affinity supported by SQLite3 database.

 

AFFINITYUSES

TEXT

Stores all data using storage classes NULL, TEXT or BLOB.

NUMERIC

Contain values using all five storage classes.

INTEGER

Same as numeric affinity with an exception in a cast expression.

REAL

Same as numeric affinity but forces integer values into floating point representation.

NONE

No preference for one storage class and no persuasion of data from one storage class to other.

 

SQLite Affinity and Type Names:

Various names for data types can also be used while creating SQLite tables. These names are grouped together and than listed as per their Affinity. Thus there are five different groups of SQLite Affinity and Type Names which are listed below.

 

DATA TYPEAFFINITY

INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIGINT INT2 INT8

INTEGER

CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB

TEXT

BLOB no datatype specified

NONE

REAL DOUBLE DOUBLE PRECISION FLOAT

REAL

NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME

NUMERIC

 

Boolean Data Type:

In SQLite, Boolean values are stored as integers 0 and 1, since there is no separate storage class for Boolean values in SQLite. Here, 0 represents FALSE and 1 represents TRUE.

 

Date and Time Data Type:

There is no separate class in SQLite databases to store dates and times and is thus stored as TEXT, REAL or INTEGER values.

 

STORAGE CLASS

FORMAT

TEXT

Format like “yyyy-mm-dd hh:mm:ss.sss”.

REAL

Number of days since noon in Greenwich on November 24, 4714 B.C.

INTEGER

Number of seconds since 1970-01-01 00:00:00 utc.

 

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status