Documentation Source Text

Check-in [4737512960]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Additional refinements to the datatype3.html document.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 47375129604eb4030acc7aff6159c5fc8ac01e78
User & Date: drh 2009-12-25 02:53:10
Context
2009-12-30
14:53
Update the documentation in preparation for the release of 3.6.22. check-in: 758f10c6da user: drh tags: trunk
2009-12-25
02:53
Additional refinements to the datatype3.html document. check-in: 4737512960 user: drh tags: trunk
2009-12-24
21:37
Update and clarify the documentation on datatypes and on CAST expressions. check-in: e357f599e3 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/datatype3.in.

59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
...
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
...
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
...
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
(INTEGER and REAL) and TEXT during query execution. 
</p>

<tcl>hd_fragment boolean {boolean datatype}</tcl>
<h3>1.1 Boolean Datatype</h3>

<p>SQLite does not have a separate Boolean storage class.
^Instead, Boolean values are stored as integers 0 (false) and 1 (true).</p>

<tcl>hd_fragment datetime {date and time datatype}</tcl>
<h3>1.2 Date and Time Datatype</h3>

<p>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:)^</p>

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

<p>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.</p>


<tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl>
................................................................................
attempt is made to convert NULL or BLOB values.</p>

<p>^A string might look like a floating-point literal with
a decimal point and/or exponent notation but as long as
the value can be expressed as an integer, the NUMERIC affinity will convert
it into an integer. ^(Hence, the string '3.0e+5' is stored in a
column with NUMERIC affinity as the integer 30000, not as the floating
point value 30000.0.)^</p>

<p>^A column that uses INTEGER affinity behaves the same as a column
with NUMERIC affinity.  The difference between INTEGER and NUMERIC affinity
is only evident in a [CAST expression].</p>

<p>^A column with REAL affinity behaves like a column with NUMERIC
affinity except that it forces integer values into floating point
................................................................................
<p>The following table shows how many common data type names from
legacy SQL are converted into affinities by the five rules of the
previous section.  This table shows only a small subset of the
data type names that SQLite will accept.  Note that ^(numeric arguments
in parentheses that following the type name (ex: "VARCHAR(255)") are
ignored)^ by SQLite - SQLite does not impose any length restrictions
(other than the large global [SQLITE_MAX_LENGTH] limit) on the length of
string or BLOBs or numeric values.</p>

<blockquote>
<table border="1" cellpadding="5">
<tr><th>Example Typenames From The<br>CREATE TABLE Statement<br>
        or CAST Expression
    <th>Resulting Affinity
    <th>Rule Used To Determine Affinity

<tr><td align="center" valign="top">
................................................................................
  NUMERIC<br>
  DECIMAL(10,5)<br>
  BOOLEAN<br>
  DATE<br>
  DATETIME
<td align="center">NUMERIC
<td align="center">5
</table>
</blockquote>>

<h3>2.3 Column Affinity Behavior Example</h3>

<p>The following SQL demonstrates how SQLite uses column affinity
to do type conversions when values are inserted into a table.</p>








|







|








|







 







|







 







|

|







 







|







59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
...
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
...
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
...
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
(INTEGER and REAL) and TEXT during query execution. 
</p>

<tcl>hd_fragment boolean {boolean datatype}</tcl>
<h3>1.1 Boolean Datatype</h3>

<p>SQLite does not have a separate Boolean storage class.
Instead, ^Boolean values are stored as integers 0 (false) and 1 (true).</p>

<tcl>hd_fragment datetime {date and time datatype}</tcl>
<h3>1.2 Date and Time Datatype</h3>

<p>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:</p>

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

<p>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.</p>


<tcl>hd_fragment affinity affinity {column affinity} {type affinity} {*affinities}</tcl>
................................................................................
attempt is made to convert NULL or BLOB values.</p>

<p>^A string might look like a floating-point literal with
a decimal point and/or exponent notation but as long as
the value can be expressed as an integer, the NUMERIC affinity will convert
it into an integer. ^(Hence, the string '3.0e+5' is stored in a
column with NUMERIC affinity as the integer 30000, not as the floating
point value 300000.0.)^</p>

<p>^A column that uses INTEGER affinity behaves the same as a column
with NUMERIC affinity.  The difference between INTEGER and NUMERIC affinity
is only evident in a [CAST expression].</p>

<p>^A column with REAL affinity behaves like a column with NUMERIC
affinity except that it forces integer values into floating point
................................................................................
<p>The following table shows how many common data type names from
legacy SQL are converted into affinities by the five rules of the
previous section.  This table shows only a small subset of the
data type names that SQLite will accept.  Note that ^(numeric arguments
in parentheses that following the type name (ex: "VARCHAR(255)") are
ignored)^ by SQLite - SQLite does not impose any length restrictions
(other than the large global [SQLITE_MAX_LENGTH] limit) on the length of
strings or BLOBs or numeric values.</p>

<blockquote> ^(
<table border="1" cellpadding="5">
<tr><th>Example Typenames From The<br>CREATE TABLE Statement<br>
        or CAST Expression
    <th>Resulting Affinity
    <th>Rule Used To Determine Affinity

<tr><td align="center" valign="top">
................................................................................
  NUMERIC<br>
  DECIMAL(10,5)<br>
  BOOLEAN<br>
  DATE<br>
  DATETIME
<td align="center">NUMERIC
<td align="center">5
</table>)^
</blockquote>>

<h3>2.3 Column Affinity Behavior Example</h3>

<p>The following SQL demonstrates how SQLite uses column affinity
to do type conversions when values are inserted into a table.</p>