Re: [sqlite] date - SQL extraction from day, month and year
I don't. I suggest that each wrapper be adapted to its purpose. Light, heavy, high level, low level or whatever. In other words use the toolkit as a kit of tools. Joe Wilson wrote: How do you propose that someone adds support for an official SQLite DATE type in the 100 or so SQLite wrappers/drivers for various languages and operating systems to get them all to agree on this standard DATE type? Only by making an official SQLite DATE type in the SQLite distro can you acheive this. What code I may write in my wrapper is irrelevant if I try to open my database with another wrapper that I did not write. The truth is it's probably too late for SQLite3 to have an official DATE type since the API is more or less frozen. Such a new type could only be added in a hypothetical SQlite4 - which may never happen. I give up. --- John Stanton <[EMAIL PROTECTED]> wrote: If drivers and such concern you the solution is obvious, implement the DATE type in the driver or "wrapper". Sqlite's basic architecture makes it simple. The Sqlite library is correct in being light and biased towards a small footprint for embedded applications. Adding layers of software to implement extra functions, interfaces etc is no problem, it is the way it is intended to be used. Joe Wilson wrote: SQLite advocacy aside, I was just addressing your original statement: Most Sqlite users know that Sqlite implements a DATE type which is not strictly true. Me, you and most SQLite users have seperately come up with conventions to handle their particular date usage patterns. There is no disputing the value of the sqlite date functions. While conventions are fine for individual applications, sometimes policy is necessary for such things as database drivers (JDBC and ODBC being the most common). SQLite's manifest typing sometimes cause the various 3rd party drivers to represent date columns as different types and values. In one driver a DATE is represented as TEXT, another as some form of DATE. Each driver makes assumptions about how dates are handled and converted to/from test. Sometimes these assumptions are correct and sometimes they are wrong. There's no way a third-party driver can possibly know what is in the mind of an individual SQLite database designer and what intent they had for a particular date field. If SQLite supported an official DATE type, such inconsistancies could be avoided, and the underlying storage of DATEs could be consistant across all applications and could potentially be stored in a more space-efficient form in the database file, as an added bonus. --- John Stanton <[EMAIL PROTECTED]> wrote: 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. [...] __ 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] -
Re: [sqlite] date - SQL extraction from day, month and year
On 12/26/06, Joe Wilson <[EMAIL PROTECTED]> wrote: How do you propose that someone adds support for an official SQLite DATE type in the 100 or so SQLite wrappers/drivers for various languages and operating systems to get them all to agree on this standard DATE type? Only by making an official SQLite DATE type in the SQLite distro can you acheive this. What code I may write in my wrapper is irrelevant if I try to open my database with another wrapper that I did not write. The truth is it's probably too late for SQLite3 to have an official DATE type since the API is more or less frozen. Such a new type could only be added in a hypothetical SQlite4 - which may never happen. I give up. no, don't. You are making very logical and sensible points. I myself was under the impression that SQLite3 did start implementing DATETIME type, but now you have lifted the veil from my eyes. i have an table, a rather large table, where datetime data comes in as a text string (y'know, -mm-dd hh:mm:ss), and I was storing it thusly, even though I was converting it to unix seconds for doing some datetime math. Now, I will actually store them as unix seconds to primarily save space, make the db more efficient, and also to benefit from other qualities of integers (sorting, for example). Converting the data to human friendly string is trivial. Nevertheless, implementing a native DATETIME type would be very nice. Yes, SQLite is growing in complexity, and that is a natural growth, however, DATETIME is a very fundamental and common data type requiring manipulation in many applications. I certainly see its value. Yes, I also once read DRH say that SQLite 4 would never happen, and I wondered why such emphatic statement, esp. given the recent desire for 3.3.9 ;-). I mean, is there some legal or philosophical reason that SQLite version would be an asymptote of 4? --- John Stanton <[EMAIL PROTECTED]> wrote: > If drivers and such concern you the solution is obvious, implement the > DATE type in the driver or "wrapper". Sqlite's basic architecture makes > it simple. > > The Sqlite library is correct in being light and biased towards a small > footprint for embedded applications. Adding layers of software to > implement extra functions, interfaces etc is no problem, it is the way > it is intended to be used. > > Joe Wilson wrote: > > SQLite advocacy aside, I was just addressing your original statement: > > > > Most Sqlite users know that Sqlite implements a DATE type > > > > which is not strictly true. > > > > Me, you and most SQLite users have seperately come up with conventions > > to handle their particular date usage patterns. There is no disputing > > the value of the sqlite date functions. > > > > While conventions are fine for individual applications, sometimes policy > > is necessary for such things as database drivers (JDBC and ODBC being > > the most common). SQLite's manifest typing sometimes cause the various > > 3rd party drivers to represent date columns as different types and values. > > In one driver a DATE is represented as TEXT, another as some form of > > DATE. Each driver makes assumptions about how dates are handled > > and converted to/from test. Sometimes these assumptions are correct and > > sometimes they are wrong. There's no way a third-party driver can possibly > > know what is in the mind of an individual SQLite database designer and what > > intent they had for a particular date field. If SQLite supported an official > > DATE type, such inconsistancies could be avoided, and the underlying storage > > of DATEs could be consistant across all applications and could potentially > > be stored in a more space-efficient form in the database file, as an added > > bonus. > > > > --- John Stanton <[EMAIL PROTECTED]> wrote: > > > >> 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. > > > > [...] __ 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] - -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation https://edu.osgeo.org/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED]
Re: [sqlite] date - SQL extraction from day, month and year
How do you propose that someone adds support for an official SQLite DATE type in the 100 or so SQLite wrappers/drivers for various languages and operating systems to get them all to agree on this standard DATE type? Only by making an official SQLite DATE type in the SQLite distro can you acheive this. What code I may write in my wrapper is irrelevant if I try to open my database with another wrapper that I did not write. The truth is it's probably too late for SQLite3 to have an official DATE type since the API is more or less frozen. Such a new type could only be added in a hypothetical SQlite4 - which may never happen. I give up. --- John Stanton <[EMAIL PROTECTED]> wrote: > If drivers and such concern you the solution is obvious, implement the > DATE type in the driver or "wrapper". Sqlite's basic architecture makes > it simple. > > The Sqlite library is correct in being light and biased towards a small > footprint for embedded applications. Adding layers of software to > implement extra functions, interfaces etc is no problem, it is the way > it is intended to be used. > > Joe Wilson wrote: > > SQLite advocacy aside, I was just addressing your original statement: > > > > Most Sqlite users know that Sqlite implements a DATE type > > > > which is not strictly true. > > > > Me, you and most SQLite users have seperately come up with conventions > > to handle their particular date usage patterns. There is no disputing > > the value of the sqlite date functions. > > > > While conventions are fine for individual applications, sometimes policy > > is necessary for such things as database drivers (JDBC and ODBC being > > the most common). SQLite's manifest typing sometimes cause the various > > 3rd party drivers to represent date columns as different types and values. > > In one driver a DATE is represented as TEXT, another as some form of > > DATE. Each driver makes assumptions about how dates are handled > > and converted to/from test. Sometimes these assumptions are correct and > > sometimes they are wrong. There's no way a third-party driver can possibly > > know what is in the mind of an individual SQLite database designer and what > > intent they had for a particular date field. If SQLite supported an > > official > > DATE type, such inconsistancies could be avoided, and the underlying > > storage > > of DATEs could be consistant across all applications and could potentially > > be stored in a more space-efficient form in the database file, as an added > > bonus. > > > > --- John Stanton <[EMAIL PROTECTED]> wrote: > > > >> 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. > > > > [...] __ 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] -
Re: [sqlite] date - SQL extraction from day, month and year
If drivers and such concern you the solution is obvious, implement the DATE type in the driver or "wrapper". Sqlite's basic architecture makes it simple. The Sqlite library is correct in being light and biased towards a small footprint for embedded applications. Adding layers of software to implement extra functions, interfaces etc is no problem, it is the way it is intended to be used. Joe Wilson wrote: SQLite advocacy aside, I was just addressing your original statement: Most Sqlite users know that Sqlite implements a DATE type which is not strictly true. Me, you and most SQLite users have seperately come up with conventions to handle their particular date usage patterns. There is no disputing the value of the sqlite date functions. While conventions are fine for individual applications, sometimes policy is necessary for such things as database drivers (JDBC and ODBC being the most common). SQLite's manifest typing sometimes cause the various 3rd party drivers to represent date columns as different types and values. In one driver a DATE is represented as TEXT, another as some form of DATE. Each driver makes assumptions about how dates are handled and converted to/from test. Sometimes these assumptions are correct and sometimes they are wrong. There's no way a third-party driver can possibly know what is in the mind of an individual SQLite database designer and what intent they had for a particular date field. If SQLite supported an official DATE type, such inconsistancies could be avoided, and the underlying storage of DATEs could be consistant across all applications and could potentially be stored in a more space-efficient form in the database file, as an added bonus. --- John Stanton <[EMAIL PROTECTED]> wrote: 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. [...] __ 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] -
Re: [sqlite] date - SQL extraction from day, month and year
SQLite advocacy aside, I was just addressing your original statement: Most Sqlite users know that Sqlite implements a DATE type which is not strictly true. Me, you and most SQLite users have seperately come up with conventions to handle their particular date usage patterns. There is no disputing the value of the sqlite date functions. While conventions are fine for individual applications, sometimes policy is necessary for such things as database drivers (JDBC and ODBC being the most common). SQLite's manifest typing sometimes cause the various 3rd party drivers to represent date columns as different types and values. In one driver a DATE is represented as TEXT, another as some form of DATE. Each driver makes assumptions about how dates are handled and converted to/from test. Sometimes these assumptions are correct and sometimes they are wrong. There's no way a third-party driver can possibly know what is in the mind of an individual SQLite database designer and what intent they had for a particular date field. If SQLite supported an official DATE type, such inconsistancies could be avoided, and the underlying storage of DATEs could be consistant across all applications and could potentially be stored in a more space-efficient form in the database file, as an added bonus. --- John Stanton <[EMAIL PROTECTED]> wrote: > 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. [...] __ 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] -
Re: [sqlite] date - SQL extraction from day, month and year
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
Re: [sqlite] date - SQL extraction from day, month and year
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
Re: [sqlite] date - SQL extraction from day, month and year
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]
-
RE: [sqlite] date - SQL extraction from day, month and year
Thanks! -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 26, 2006 1:02 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] date - SQL extraction from day, month and year This might help. http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html A quick glance through the date.c file in the Sqlite distribution is all you needed to understand the extent and rationale behind the Sqlite date support. You can also use the code there as a template to create your own user functions for more detailed date manipulation. Anderson, James H (IT) wrote: > Where are the date support functions documented? > > -Original Message- > From: John Stanton [mailto:[EMAIL PROTECTED] > Sent: Tuesday, December 26, 2006 12:40 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] date - SQL extraction from day, month and year > > 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 > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] date - SQL extraction from day, month and year
On Tue, 2006-12-26 at 12:43 -0500, Anderson, James H (IT) wrote: > Where are the date support functions documented? > On the wiki > -Original Message- > From: John Stanton [mailto:[EMAIL PROTECTED] > Sent: Tuesday, December 26, 2006 12:40 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] date - SQL extraction from day, month and year > > 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 > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > NOTICE: If received in error, please destroy and notify sender. Sender does > not intend to waive confidentiality or privilege. Use of this email is > prohibited when received in error. > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > -- G. Roderick Singleton <[EMAIL PROTECTED]> PATH tech smime.p7s Description: S/MIME cryptographic signature
Re: [sqlite] date - SQL extraction from day, month and year
This might help. http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html A quick glance through the date.c file in the Sqlite distribution is all you needed to understand the extent and rationale behind the Sqlite date support. You can also use the code there as a template to create your own user functions for more detailed date manipulation. Anderson, James H (IT) wrote: Where are the date support functions documented? -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 26, 2006 12:40 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] date - SQL extraction from day, month and year 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 - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] date - SQL extraction from day, month and year
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]
-
RE: [sqlite] date - SQL extraction from day, month and year
Where are the date support functions documented? -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 26, 2006 12:40 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] date - SQL extraction from day, month and year 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 - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] date - SQL extraction from day, month and year
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 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] date - SQL extraction from day, month and year
I think if your dates are in the ISO8601 format, that is -mm-dd
then this will do it:
SELECT strftime('%Y', '2006-12-26')
More fully explainede here:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
RBS
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 26 December 2006 17:12
To: sqlite-users@sqlite.org
Subject: [sqlite] date - SQL extraction from day, month and year
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
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
