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]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
