Re: [sqlite] Errors in "julianday" function in SqLite

[Richard Hipp]

On 12/7/17, David Simmons  wrote:
> There are many julian-day website sources, but this one is correct
> present-day all the way back to 4712 B.C. (not including what should be
> 46BC to AD8 leap year anomalies due to early priests LY every 3 year
> error corrections).
> SqLite:
>
> SELECT julianday('0001-01-01 12:00') AS jd
> 1721426correct answer is 1721424
> ---
> SELECT julianday('1582-10-04 12:00') AS jd
> 2299150 correct answer is 2299160

There are three separate systems in common use for naming days:

(1) The Julian Calendar named after the Caesar

(2) The Gregorian Calendar named after a Pope

(3) The julian day number, named after some guy named Julius (or maybe
it was his father's name that was Julius.  Or Julian.  I forget...)

One confusing aspect of this is even though (1) and (3) have similar
names, they are completely unrelated.

The ancient world use the Julian calendar.  But around 1582, folks
began switching over to the Gregorian calendar.  This happened slowly
over a span of more than 300 years.  Several countries (Russia,
Turkey) have only switched within living memory.  There was a time
when, as you traveled about the world, you would not only have to
adjust your watch, but you'd also have to adjust your calendar!  The
conversion from Julian to Gregorian in most English-speaking countries
occurred on 1752-09-02 (Julian) which was followed immediately by
1752-09-14 (Gregorian).  On your unix system, type "cal 1752" and see
that the month of September only had 19 days that year.

SQLite does all date computations using the Gregorian calendar.  It
does this even for dates that predate the invention of the Gregorian
calendar.  That way it does not have to worry with locale-specific
transitions from Julian to Gregorian.

The USNO uses the Gregorian calendar for modern dates and the Julian
calendar for earlier dates.  I do not know what they use as a
transition date.

The julianday() function of SQLite converts Gregorian calendar dates
(type 2) into a julian day number (type 3).

The USNO website converts "Calendar dates" (a mixture of Gregorian and
Julian dates) into the corresponding Julian day number.

These are different computations.  SQLite gets the same answer as the
USNO as long as the USNO is using the Gregorian calendar.  When the
USNO switches to the Julian calendar, then the answers diverge.

This is not a bug in SQLite's date function.  SQLite is computing
exactly the correct julian day number given an input gregorian date.
That is what the julianday() function in SQLite is defined to do.  The
date/time functions in SQLite do not know anything about Julian
calendar dates.  SQLite does not have the capability of converting
Julian calendar dates into other systems such as the Gregorian
calendar or the julian-day number.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Errors in "julianday" function in SqLite

[David Simmons]

There are many julian-day website sources, but this one is correct 
present-day all the way back to 4712 B.C. (not including what should be 
46BC to AD8 leap year anomalies due to early priests LY every 3 year 
error corrections).


Use this site: http://aa.usno.navy.mil/data/docs/JulianDate.php

1721424
A.D. 1 Jan 1st 12:00:00

SqLite:

SELECT julianday('0001-01-01 12:00') AS jd
1721426correct answer is 1721424
---
SELECT julianday('1582-10-04 12:00') AS jd
2299150 correct answer is 2299160

Etc.

Impact: Astronomical data, historical and religious data.

David Simmons (ts-lang, afm)
p.s.,

How Found: My code uses separate tsl time-fmt libraries I wrote long ago 
as part of (AOS/AFM) QKS Smalltalk which parse time and dates.
I was running unit-tests through SqLite databases and dates were not 
round tripping properly through SqLite conversion.


If needed, I can help with providing the correct 
equations/implementations for the entire JulianDate time span cycles and 
dealing with mean tropical year adjustments, etc for astronomy etc.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Errors in "julianday" function in SqLite

[David Simmons]

There are many julian-day website sources, but this one is correct 
present-day all the way back to 4712 B.C. (not including what should be 
46BC to AD8 leap year anomalies due to early priests LY every 3 year 
error corrections).


Use this site: http://aa.usno.navy.mil/data/docs/JulianDate.php

1721424
A.D. 1 Jan 1st 12:00:00

SqLite:

SELECT julianday('0001-01-01 12:00') AS jd
1721426correct answer is 1721424
---
SELECT julianday('1582-10-04 12:00') AS jd
2299150 correct answer is 2299160

Etc.

Impact: Astronomical data, historical and religious data.

David Simmons (ts-lang, afm)
p.s.,

How Found: My code uses separate tsl time-fmt libraries I wrote long ago 
as part of (AOS/AFM) QKS Smalltalk which parse time and dates.
I was running unit-tests through SqLite databases and dates were not 
round tripping properly through SqLite conversion.


If needed, I can help with providing the correct 
equations/implementations for the entire JulianDate time span cycles and 
dealing with mean tropical year adjustments, etc for astronomy etc.


--
Sent from Postbox 


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Errors in "julianday" function in SqLite

[David Simmons]

There are many julian-day website sources, but this one is correct 
present-day all the way back to 4712 B.C. (not including what should be 
46BC to AD8 leap year anomalies due to early priests LY every 3 year 
error corrections).


Use this site: http://aa.usno.navy.mil/data/docs/JulianDate.php

1721424
A.D. 1 Jan 1st 12:00:00

SqLite:

SELECT julianday('0001-01-01 12:00') AS jd
1721426correct answer is 1721424
---
SELECT julianday('1582-10-04 12:00') AS jd
2299150 correct answer is 2299160

Etc.

Impact: Astronomical data, historical and religious data.

David Simmons (ts-lang, afm)
p.s.,

How Found: My code uses separate tsl time-fmt libraries I wrote long ago 
as part of (AOS/AFM) QKS Smalltalk which parse time and dates.
I was running unit-tests through SqLite databases and dates were not 
round tripping properly through SqLite conversion.


If needed, I can help with providing the correct 
equations/implementations for the entire JulianDate time span cycles and 
dealing with mean tropical year adjustments, etc for astronomy etc.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users