SQLite has no explicit DATE type, and the programmer has to rely
on convention, as previously mentioned. But that does not diminish the
value of the various date functions that SQLite provides.
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> create table d1(a DATE, b DATETIME);
sqlite> insert into d1 values(date('now'), datetime('now'));
sqlite> select typeof(a), typeof(b) from d1;
text|text
** Substring | Affinity
** --------------------------------
** 'INT' | SQLITE_AFF_INTEGER
** 'CHAR' | SQLITE_AFF_TEXT
** 'CLOB' | SQLITE_AFF_TEXT
** 'TEXT' | SQLITE_AFF_TEXT
** 'BLOB' | SQLITE_AFF_NONE
** 'REAL' | SQLITE_AFF_REAL
** 'FLOA' | SQLITE_AFF_REAL
** 'DOUB' | SQLITE_AFF_REAL
**
** If none of the substrings in the above table are found,
** SQLITE_AFF_NUMERIC is returned.
*/
char sqlite3AffinityType(const Token *pType){
But it's unfortunate that SQLite dates use so many bytes of text in
its "natural" text form, though.
Consider '2004-08-19 14:51:06' versus its unixepoch equivalent
number 1092941466. The former takes 19 bytes and the latter could
be stored as 4 bytes in an integer or as 8 bytes as a double if
you want sub-second resolution.
If you stored dates in the natural readable text format then the
database would more than 2 times the file size to accomodate these
dates.
By convention you could do the extra work of storing the dates as
unixepoch ints to save space (as I do in my databases) and whenever
you wish to manipulate it you could convert it to its text form, but
it requires extra work on the part of the programmer and is not
as friendly as simply reading the expanded text data. For reasons
of space-efficiency and programmer simplicity it would be an extremely
useful feature if SQLite added "DATE" to its list of built-in types.
Just as the SQLite authors saw the merit of adding integers, doubles
and blobs to SQLite 3 from the text-only SQLite 2, I believe there
is a compelling case for adding a proper DATE type in a future version
of SQLite.
--- John Stanton <[EMAIL PROTECTED]> wrote:
> You can declare your date columns as type DATE and Sqlite will store
> that as a declared type. It stores the actual type according to how you
> use the column, according to its manifest typing rules. You could use
> the Sqlite built in date functions to store a date as a floating point
> number based on an epoch or you could use an ISO8601 ASCII string for
> the date and it will be stored as TEXT.
>
> If you use a declared type of DATE your schemas become self explanatory
> and if you use the builtin functions to insert and extract dates from
> the DB should find that you can do pretty much what you set out to do
> with dates. You must be aware that under the current Sqlite date system
> the programmer enforces data integrity. If you mix epoch based dates
> and ISO8601 text you will get problems.
>
> If you do have a mixed date format you can sort it out at the
> application programming level by getting the declared and actual types
> and treat TEXT as ISO8601 (or whatever) and floating point as epoch based.
>
> In our applications we use a DATE declared type and have a few extra
> user defined date manipulation functions for SQL plus a small library of
> application support date functions which enforce date integrity, make
> the Sqlite date epoch easily usable in applications and interface into
> OS date functions like current date, age of files, HTTP header date
> formats etc. The support functions are simple and basically use code
> lifted from Sqlite's date functions plus the underlyng OS API. Open
> source software is a delight.
>
> I should point out that we use Sqlite as a library which embeds a small
> footprint, simple to manage SQL database inside applications, we do not
> expect it to be an enterprise database on its own. That is handled by
> PostgreSQL.
>
> Joe Wilson wrote:
> > DATE is more of a psuedo-type in SQLite that relies on programmer
> > convention. It is usually represented as text or as a number of
> > seconds since the epoch.
> >
> > sqlite> select typeof(date('now'));
> > text
> >
> > sqlite> select typeof(datetime('now'));
> > text
> >
> > I think a proper date/time type in SQLite would be a good feature.
> >
> > --- John Stanton <[EMAIL PROTECTED]> wrote:
> >
> >>Everybody? Most Sqlite users know that Sqlite implements a DATE type
> >>and has date support functions.
> >>
> >>Karsten Koschinski wrote:
> >>
> >>>Hey,
> >>>
> >>>I have a database with a field where dates are stored! As everybody knows
> >>>SQLite has no
> >>
> >>special date support such as a special datatype for date or something else.
> >>Now I want to
> select
> >>only those records, where the year ist e.g. 2005. How can I do this?
> >>
> >>>"SELECT * FROM database WHERE (EXTRACT DAY FROM date) = 2005" does not
> >>>work.
> >>>
> >>>The database was created with SQLite Analyzer 3.0.8. The datatyp of the
> >>>field date ist Text.
> >>>
> >>>Thanks
> >>>
> >>>Karsten
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
