From my perspective Sqlite does implement a DATE type by having the
function support for an epoch based date stored in a floating point
number plus declared types. The Sqlite epoch appears to be the
theoretically correct one based on an epoch somewhere in antiquity and
compatible with all the world's significant calendars.
I like the way that Sqlite is not rigid in its rules so that the
programmer can squeeze it into embedded applications rather than having
to coerce the application to accept it.
As I mentioned earlier we store dates in the Sqlite manner and have
application programming functions which translate to the epoch of the
current underlying Unix OS or to Windows or whatever. It gives us OS
independence.
In effect we have implemented the DATE type you seek, but had the
flexibility to do it in a way which suits our application. I might add
that the Sqlite date epoch matches the way we learned to handle dates
many years ago, and which not only saved our applications from any Y2K
perturbations but gave us great flexibility in handling time zones and
Middle Eastern and Asian calendars. In keeping with the rest of Sqlite
it has been well thought through.
Real world date problems like a table of equally weighted accounting
periods, the number of working days since the start of the period, the
percantage of working days since the start of the period, the number of
months difference between dates, the number of days past due etc were
all achieved without too much difficulty.
It took me some time to appreciate the subtlety of having a declared and
an actual type. It lets the user enforce typing to user defined rules
rather than to rigid implemementaion defined rules. For example we have
added a DECIMAL(m,n) number type to permit accurate and efficient fixed
point commercial type number storage, presentation and calculations.
Initially I thought that its omission was a serious defect in Sqlite
until it dawned on me that we had the hooks supplied to implement it
just as we wanted it to function. Sqlite stores these numbers as TEXT,
but we know that they are display format fixed point decimal numbers
with a defined scale and precision. They drop straight into a web page
without editing or radix transformation.
Joe Wilson wrote:
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]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
