[SQLite] Datatypes In SQLite Version 3

Datatypes In SQLite Version 3

data type	value	특징
NULL	NULL
INTEGER	signed integer	stored in 1, 2, 3, 4, 6, or 8 bytes
REAL	floating point value	8-byte IEEE floating point number
TEXT	text string	UTF-8, UTF-16BE or UTF-16LE 사용
BLOB	blob of data

Boolean Datatype

별도의 Boolean 타입은 없다. 대신 Integers 의 0과 1 사용
SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

Date and Time Datatype

dates 나 times에 대한 타입이 없다. 대신 내장된 함수를 이용하여 TEXT, REAL, INTEGER 값을 dates와 times으로 저장한다. 

함수  date(timestring, modifier, modifier, ...) time(timestring, modifier, modifier, ...) datetime(timestring, modifier, modifier, ...) julianday(timestring, modifier, modifier, ...) strftime(format, timestring, modifier, modifier, ...)  timestring  Modifiers YYYY-MM-DD YYYY-MM-DD HH:MM YYYY-MM-DD HH:MM:SS YYYY-MM-DD HH:MM:SS.SSS YYYY-MM-DDTHH:MM YYYY-MM-DDTHH:MM:SS YYYY-MM-DDTHH:MM:SS.SSS HH:MM HH:MM:SS HH:MM:SS.SSS  now DDDDDDDDDD   T는 date와 time을 구분짓는 문자  ±NNN days ±NNN hours ±NNN minutes ±NNN.NNNN seconds ±NNN months ±NNN years ※연산전에 먼저 YYYY-MM-DD format 으로 만들어야 한다.  start of month start of year start of day ※ 현재 월, 년, 일을 사용한다.  weekday N ※ 0:일, 1:월, 2:화 .... unixepoch localtime utc Examples Compute the current date. SELECT date('now'); Compute the last day of the current month. SELECT date('now','start of month','+1 month','-1 day'); Compute the date and time given a unix timestamp 1092941466. SELECT datetime(1092941466, 'unixepoch'); Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone. SELECT datetime(1092941466, 'unixepoch', 'localtime'); Compute the current unix timestamp. SELECT strftime('%s','now'); Compute the number of days since the signing of the US Declaration of Independence. SELECT julianday('now') - julianday('1776-07-04'); Compute the number of seconds since a particular moment in 2004: SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56'); Compute the date of the first Tuesday in October for the current year. SELECT date('now','start of year','+9 months','weekday 2'); Compute the time since the unix epoch in seconds (like strftime('%s','now') except includes fractional part): SELECT (julianday('now') - 2440587.5)*86400.0;

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.