티스토리 뷰
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, ...)
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.
댓글