Slava Tutushkin wrote:
I think, I found a bug in the strftime function.
When I executed the next query: select strftime('%Y-%m-%d %H:%M:%f',
julianday('2006-09-24T10:50:26.047')); it returned 2006-09-24
10:50:26.046
At the same time, I can clearly see, that julianday for that time for
.046 and .047 points is returning different numbers. It seems, there
is a bug in the strftime function.
I think this is down to floating point rounding errors. I tried a couple
of juliandate queries in the sqlite shell - only 8 decimal places are
returned (on my 32 bit XP box) and when you get down to the millisecond
range you run out of decimal places. For the (dozen or so) dates I tried
there was a repeatable error for the following number of milliseconds:
5 7 9 11 13 15 17 22 24 26 28 30 32 34 41 43 45 47 49 51 53 58 60 62 64
66 68 70 77 79 81 83 85 87 89 94 96 98
After looking at the source, the juliandayFunc wrapper returns a double
and as one millisecond = 1.15740740325e-008 days I think your problem is
down to rounding errors.
Also, I note two other problems:
1) The "Battle of Hastings" example query on the website
SELECT julianday('now') - julianday('1066-10-14','gregorian');
doesn't work with sqlite shell 3.3.4 or 3.3.6 - the 1066 date returns
nothing with the gregorian modifier.
Looking at the source, the text "gregorian" only appears in comments in
the 3.3.4, 3.3.5 and 3.3.7 trees. juliandate() calls isDate() and then
parseModifier() - "localtime" and "utc" are both listed in the comments
for parseModifier() but "gregorian" is not. The comments and docs state
that Julian dates are used internally which suggests the example given
is a bug in the documentation rather than in the library.
2) The Julian day docs state that a Julian date is "the number of days
since noon in Greenwich on November 24, 4714 B.C" but when I probed
around this date to check the resolution, I got a JD of -365 for that
date and 0 for "select julianday('-4713-11-24T12:00:00.%03d')"
I can't claim to understand the algorithm used in computeJD() and don't
have the reference, but the relevant area in date.test is:
> # Negative years work. Example: '-4713-11-26' is JD 1.5.
> #
> datetest 9.1 {julianday('-4713-11-24 12:00:00')} {0.0}
> datetest 9.2 {julianday(datetime(5))} {5.0}
To me, it looks like this code is testing 24-11-4713BC against JD 0 and
this is confirmed by the following session:
SQLite version 3.3.7
Enter ".help" for instructions
sqlite> select datetime(0);
datetime(0)
--------------------
-4713-11-24 12:00:00
sqlite>.quit
So, it looks like the documentation for juliandate() is wrong for the
base year.
I did do a bit of research and there seems to be some confusion (or at
least, lack of precision when talking about) about the start date.
*Wikipedia:* http://en.wikipedia.org/wiki/Julian_date
The Julian day or Julian day number (JDN) is the (integer) number of
days that have elapsed since Monday, January 1, 4713 BC in the proleptic
Julian calendar 1. That day is counted as Julian day zero. [snip]
The Julian Date (JD) is the number of days (with decimal fraction of the
day) that have elapsed since 12 noon Greenwich Mean Time (UT or TT) of
that day. Rounding to the nearest integer gives the Julian day number.
*US Navy:*
http://aa.usno.navy.mil/data/docs/JulianDate.html has a calculator
which gives Jan 1st 4713 BCE 12:00:00 for JD 0
*Doug Welch:*
http://wwwmacho.mcmaster.ca/JAVA/JD.html has a calculator which gives
-4712-01-13 12:00:00 for JD 0
*Wolfram Research:*
http://scienceworld.wolfram.com/astronomy/JulianDate.html has a page
which defines the Julian day as "The number of days since noon on
January 1, -4712, i.e., January 1, 4713 BC".
The last page links to http://scienceworld.wolfram.com/astronomy/BC.html
which explains why Doug Welch has "-4712" and the others have "4713 BC"
(news to me!) and based on that it appears that there might be a year
off bug in juliandate() :(
Martin
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
