Re: [sqlite] Precision of dates stores as Julian real
So make the wiki available for download. ;) I would like this too. ;) Often I'm working without an internet connection and a having a local copy of the Wiki would be extremely useful. Been working on this for years. Literally. I just never seem to find the time to complete the project. When we talked about Wiki vs. Source Tree documentation on this list some time ago there was a desire to have some Reference or Feature documentation bundled with each release. IMO, the wiki can add to this but should not replace it. This feature / version / reference documentation is especially usefull for working with older versions when the wiki does fails to record when new features entered the library. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Precision of dates stores as Julian real
McDermott, Andrew [EMAIL PROTECTED] wrote: Hi, [EMAIL PROTECTED] wrote: Guess you can't please everybody :-) Right now we have some documentation in the source tree and some on the wiki, which I suppose is guaranteed to please nobody. So make the wiki available for download. ;) I would like this too. ;) Often I'm working without an internet connection and a having a local copy of the Wiki would be extremely useful. Been working on this for years. Literally. I just never seem to find the time to complete the project. http://fossil-scm.hwaci.com/ -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Precision of dates stores as Julian real
[EMAIL PROTECTED] wrote:
This isn't a bug.
The magic current_timestamp keyword is really an alias for
datetime('now'). And datetime('now') returns you a text
string in the format YY-MM-DD HH:MM:SS. Yes, this means
that the seconds have been rounded to the nearest whole
second. But that is what current_timestamp keyword is
documented to do. Imagine the outcry and all the broken code
if current_timestamp suddenly started returning dates
in the format YY-MM-DD HH:MM:SS.SSS.
Would could think about adding a new magic keyword,
perhaps current_hires_timestamp, that included the fractional
seconds. Such a magic keywords would be an alias
for strftime('%Y-%m-%d %H:%M:%f','now'). But adding this
new keyword will be a hard sell since there is a lot of
emphasis on avoiding code bloat. So for now, you are better
off just using the strftime function if you need a high
resolution timestamp.
Actually, current_timestamp (and datetime) returns the full 4 digit year.
The problem is that you can't use a function like strftime as the
default value for a column when you create a tbale. It only accepts
NULL, a string constant, a number, or one of the magic current_* values.
Or (and better in my view) store
your dates as julian day numbers and convert to a ISO8601
for display only.
I thought that was what the current_timestamp did!
You are saying that SQLite really stores the 19 byte date and time
string instead of the 8 byte julian day number as a floating point
double. This seems awfully wasteful. I can see where this would be of
benefit to systems without floating point, so maybe there is a good
reason for this, but it is not what I expected.
I suspect a few others might be expecting finer resolution from the
current_timestamp when used to add a create or modified timestamp to a
record. The current behavior seems to increase the likelyhood of
timestamp conflicts in such applications.
I would suggest that adding a small amount of code bloat to accept
another magic default value that stores the high resolution julian day
number would be worth the cost for both database size reduction and
increased resolution. The user can always for this time anyway they like
for display in the select statements where arbitrary functions are
allowed. I think the best name (current_timestamp) is already taken, so
I would suggest julian_timestamp. It would have been better if the
original magic values were current_date, current_time, and
current_datetime, then the new value would logically be the
current_timestamp, but that can't be changed now.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Precision of dates stores as Julian real
Dennis Cote [EMAIL PROTECTED] wrote:
The problem is that you can't use a function like strftime as the
default value for a column when you create a tbale. It only accepts
NULL, a string constant, a number, or one of the magic current_* values.
Sure you can. You just have to put the expression in parentheses
(to avoid a parsing conflict). Try this:
CREATE TABLE test1(
date TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
dummy int
);
INSERT INTO test1(dummy) VALUES(1);
SELECT * FROM test1;
I would suggest that adding a small amount of code bloat to accept
another magic default value that stores the high resolution julian day
number
This would break backwards compatibility. It would not be a major
break but it could possible cause problems for some users.
I need to start a wiki page on proposed minor incompatibilities
so that we I go from 3.3.x to 3.4.0 I can be sure to get them all.
What was that one we discussed on this mailing earlier this week?
--
D. Richard Hipp [EMAIL PROTECTED]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Precision of dates stores as Julian real
On 2/21/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Dennis Cote [EMAIL PROTECTED] wrote:
The problem is that you can't use a function like strftime as the
default value for a column when you create a tbale. It only accepts
NULL, a string constant, a number, or one of the magic current_* values.
Sure you can. You just have to put the expression in parentheses
(to avoid a parsing conflict). Try this:
CREATE TABLE test1(
date TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
dummy int
);
INSERT INTO test1(dummy) VALUES(1);
SELECT * FROM test1;
..
I need to start a wiki page on proposed minor incompatibilities
so that we I go from 3.3.x to 3.4.0 I can be sure to get them all.
What was that one we discussed on this mailing earlier this week?
is it possible to add usage such as the above, and many, many
wonderful SQL suggestions routinely provided by Igor Tandetnik (thanks
Igor!) to the syntax docs in the form of user-submitted comments?
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Precision of dates stores as Julian real
P Kishor [EMAIL PROTECTED] wrote: is it possible to add usage such as the above, and many, many wonderful SQL suggestions routinely provided by Igor Tandetnik (thanks Igor!) to the syntax docs in the form of user-submitted comments? I was trying to move all of the documentation into wiki for exactly this reason. But then people said they didn't like that and wanted all the documentation in the source tree. Guess you can't please everybody :-) Right now we have some documentation in the source tree and some on the wiki, which I suppose is guaranteed to please nobody. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Precision of dates stores as Julian real
[EMAIL PROTECTED] wrote:
Sure you can. You just have to put the expression in parentheses
(to avoid a parsing conflict). Try this:
CREATE TABLE test1(
date TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now')),
dummy int
);
INSERT INTO test1(dummy) VALUES(1);
SELECT * FROM test1;
Oh. OK then. I just went by the documentation at
http://www.sqlite.org/lang_createtable.html. This isn't mentioned there
at all.
Would you like a bug report for the documentation, or will you just fix it?
I would suggest that adding a small amount of code bloat to accept
another magic default value that stores the high resolution julian day
number
This would break backwards compatibility. It would not be a major
break but it could possible cause problems for some users.
How would adding a new JULIAN_TIMESTAMP keyword which is a magic alias
of julianday('now') in the same fashion as CURRENT_TIMESTAMP is an alias
for datetime('now') break backwards compatibility? The new keyword would
only be used in new code. The old one would continue to work as it does
now in old code. The benefit would be smaller more flexible databases
for those who adopt the new functionality. There would be no change for
those who don't.
I need to start a wiki page on proposed minor incompatibilities
so that we I go from 3.3.x to 3.4.0 I can be sure to get them all.
What was that one we discussed on this mailing earlier this week?
I agree with this. I remember something else was discussed too, but I'm
not sure what it was either.
I looked back and found that it was the PRIMARY KEY and NOT NULL issue
raised by Eric Johansson.
Dennis Cote
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Precision of dates stores as Julian real
[EMAIL PROTECTED] wrote: Guess you can't please everybody :-) Right now we have some documentation in the source tree and some on the wiki, which I suppose is guaranteed to please nobody. So make the wiki available for download. ;) Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Precision of dates stores as Julian real
Hello, I know that SQLite uses a 64-bit floating point type to store Julian date information. Is this accurate to the second or a fraction of a second? I was doing some testing and tried to format a date using strftime() method with the %f option and I was unable to find a date that kept any millisecond information and after reviewing the date.c file it does not appear to capture anything more accurate than a second. Is this correct? If I need millisecond information I assume that I should be using the ISO 8601 format and store the dates as text. Is this correct? Tom -- View this message in context: http://www.nabble.com/Precision-of-dates-stores-as-Julian-%22real%22-tf3264210.html#a9073506 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Precision of dates stores as Julian real
Tom Olson [EMAIL PROTECTED] wrote:
Hello,
I know that SQLite uses a 64-bit floating point type to store Julian date
information. Is this accurate to the second or a fraction of a second?
I was doing some testing and tried to format a date using strftime() method
with the %f option and I was unable to find a date that kept any millisecond
information and after reviewing the date.c file it does not appear to
capture anything more accurate than a second. Is this correct?
A double is sufficient to store the current time to with about
25 microseconds. If you use 'now' to get the current time, the
date functions try to capture the current time to this precision.
That is implemented in the os_XXX.c layer. It's system dependent.
Check to see what your system is doing.
If you enter the date in ISO8601, it captures up to milliseconds.
Ex:
SELECT strftime('%Y-%m-%d %H:%M:%f', '2001-09-11 09:32:42.437');
This works for me. I see the full .437 at the end. Are you
saying that you do not?
--
D. Richard Hipp [EMAIL PROTECTED]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Precision of dates stores as Julian real
--- [EMAIL PROTECTED] wrote:
A double is sufficient to store the current time to with about
25 microseconds. If you use 'now' to get the current time, the
date functions try to capture the current time to this precision.
That is implemented in the os_XXX.c layer. It's system dependent.
Check to see what your system is doing.
Useless trick to find out the minimum resolution of your machine's clock
using only sqlite...
create view v1 as select 1 union all select 2 union all select 3;
select distinct julianday('now') from v1,v1,v1,v1,v1,v1,v1,v1,v1,v1,v1;
2454152.64735713
2454152.64735731
2454152.64735749
2454152.64735768
2454152.64735786
2454152.64735804
2454152.64735822
2454152.6473584
2454152.64735858
2454152.64735876
2454152.64735894
2454152.64735912
2454152.6473593
2454152.64735948
2454152.64735967
2454152.64735985
2454152.64736003
2454152.64736021
2454152.64736039
2454152.64736057
2454152.64736075
2454152.64736093
2454152.64736111
2454152.64736129
2454152.64736147
sqlite select (2454152.64736147 - 2454152.64736129) * 24 * 60 * 60;
0.015529990196228
So this machine's minimum timer resolution is 0.0155 seconds,
or 15.5 milliseconds.
Bored stiff? Loosen up...
Download and play hundreds of games for free on Yahoo! Games.
http://games.yahoo.com/games/front
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
