That's what I call "clarification" !!! =)
Thank you!!
Roger.
----- Original Message -----
From: "Doug Currie" <[EMAIL PROTECTED]>
To: "Roger Reghin" <[EMAIL PROTECTED]>
Cc: "W Allan Edwards" <[EMAIL PROTECTED]>;
<[EMAIL PROTECTED]>
Sent: Monday, January 05, 2004 7:40 PM
Subject: Re: [sqlite] Date and Time functions are experimental
Here are some clarifications about date and time functions.
SQLite stores everything in the database as a string. You may choose
to represent your datetimes in the database as Julian dates or as
datetime formatted strings. Julian dates are doubles, but they are
converted to and from string when written to and read from the
database.
Representing your datetimes as Julian dates has the advantages that
1. you get the most time resolution for the fewest bytes
2. you can compute datetime differences with simple aritmetic
3. it is the "native" format inside the SQLite date and time functions
4. it is the format returned from the internal now() function
and the disadvantages that
1. conversion to datetime formatted strings needed for human consumption
2. queries against hand coded dates also need conversion to be readable
Representing your datetimes as datetime formatted strings has the
converse advantages and disadvantages.
Either format may be passed directly as the first argument to the date
and time functions: julianday, date, time, datetime, and the 2nd
argument to strftime. Of course passing a Julian date to julianday (or
a datetime formatted string to datetime) is redundant unless you are
also passing modifiers.
Now, the query
SELECT DateTimeField FROM Allan
WHERE datetime(DateTimeField) = datetime('2004-01-04 12:00:00');
does some extra work. Since datetime returns a datetime formatted
string, a simpler query with the same effect is:
SELECT DateTimeField FROM Allan
WHERE datetime(DateTimeField) = '2004-01-04 12:00:00';
This query makes sense if your DateTimeField is stored as a Julian
date. If DateTimeField is stored as a datetime formatted string, you
might as well just say:
SELECT DateTimeField FROM Allan
WHERE DateTimeField = '2004-01-04 12:00:00';
The alternative, when DateTimeField is stored as a Julian date, is:
SELECT DateTimeField FROM Allan
WHERE DateTimeField = julianday('2004-01-04 12:00:00');
See transcript below.
e
sqlite> insert into Allan values ('2004-01-04 12:00:00');
sqlite> insert into Allan values (julianday('2004-01-04 12:00:00'));
sqlite> select * from Allan;
2004-01-04 12:00:00
2453009
sqlite> SELECT DateTimeField FROM Allan
...> WHERE datetime(DateTimeField) = '2004-01-04 12:00:00';
2004-01-04 12:00:00
2453009
sqlite> SELECT DateTimeField FROM Allan
...> WHERE datetime(DateTimeField) = datetime('2004-01-04 12:00:00');
2004-01-04 12:00:00
2453009
sqlite> SELECT DateTimeField FROM Allan
...> WHERE DateTimeField = '2004-01-04 12:00:00';
2004-01-04 12:00:00
sqlite> SELECT DateTimeField FROM Allan
...> WHERE DateTimeField = julianday('2004-01-04 12:00:00');
2453009
sqlite>
-=-
Monday, January 5, 2004, 11:56:13 AM, you wrote:
> Got it!! =)
> Thank you so much again!!
> Roger.
> ----- Original Message -----
> From: W Allan Edwards
> To: [EMAIL PROTECTED]
> Sent: Monday, January 05, 2004 12:53 PM
> Subject: Re: [sqlite] Date and Time functions are experimental
> By my understanding of the code, it is turing into into a number that
represents a Julian date. This number is what is compared. Although it
translates all the strings, it turns them into
> numbers because comparisons are much faster.
> Basically, I think that is what the datetime and other functions are
doing. The statement evaluates the functiosn for each row which translates
the datetime strings into numbers, then the
> compareter is run.
> Previous to the Dr. adding these routines, you had to store a number
period and do your own date translations. That is nasty if you manually do
things with the database. It is much more
> convenient to have these routines. As well, perf ormance in much better
on date time queries.
> Thanks,
> Allan
> >From: "Roger Reghin"
> >To: "'SQLite Mailingliste'"
> >Subject: Re: [sqlite] Date and Time functions are experimental
> >Date: Mon, 5 Jan 2004 13:37:37 -0300
> >
> >Understood!!
> >
> >Now, let me ask you something else about this subject...
> >
> >When comparing values, does SQLite transform the DateTime value to a
string
> >before comparing?
> >
> >For example, as Allan wrote:
> >
> > SELECT DateTimeField FROM Allan WHERE datetime(DateTimeField) =
> >datetime('2004-01-04 12:00:00');
> >
> >So, before comparing "datetime(DateTimeField) = datetime('2004-01-04
> >12:00:00')", does SQLite transform both to string and then do the
compare,
> >or does it transform both to floating point and then compare them?
> >
> >I ask that because I know there´s an overhead doing the typecast, so I
would
> >like to choose what costs less, in my case.
> >
> >Thanx!!
> >
> >Roger.
> >
> >
> >
> >----- Original Message -----
> >From: "D. Richard Hipp"
> >To: "'SQLite Mailingliste'"
> >Sent: Monday, January 05, 2004 11:37 AM
> >Subject: [sqlite] Date and Time functions are experimental
> >
> >
> > > Allan Edwards wrote:
> > > > I looked through the source code and those functions "WERE"
> >experimental.
> > > > They appear to be a full non experimental in the sqlite source
code at
> >this
> > > > point. Thus far I have not found any problems with them. You can
> >#define
> > > > them out on a compilation if you don't want date routines, but
they look
> >to
> > > > be fully integrated! : - )
> > > >
> > >
> > > The date and time functions are fully integrated, but they are also
still
> > > "experimental". They are fully tested with 100% code coverage and
will
> >probably
> > > work fine. But they are still "experimental".
> > >
> > > "Experimental" means that I reserve the right to change the API in
an
> > > incompatible way in a future release. The date and time functions
will
> > > stop being experimental once they are in the official documentation
and
> > > we decide to support them in all future releases.
> > >
> > >
> > > --
> > > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
> > >
> > >
> >
> ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > > For additional commands, e-mail: [EMAIL PROTECTED]
> > >
> >
> >
> >
> >---------------------------------------------------------------------
> >To unsubscribe, e-mail: [EMAIL PROTECTED]
> >For additional commands, e-mail: [EMAIL PROTECTED]
> >
> --------------------------------------------------------------------------
----
> Make your home warm and cozy this winter with tips from MSN House &
Home.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
