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

Re: [sqlite] sqlite3_analyzer for Debian

[Simon Slavin]

On 7 Dec 2017, at 11:22pm,   wrote:

> be careful with "under any Linux" as the name of the zip says the
> binarys are for X86 only: sqlite-tools-linux-x86-321.zip

Point taken.  Does "Lintel" mean anything apart from a horizontal bracing strut 
?

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

Re: [sqlite] sqlite3_analyzer for Debian

[nitpilot]

Am Thu, 7 Dec 2017 19:29:46 +
schrieb Simon Slavin :

> It’s not part of the standard Debian installation, but you can
> download it as part of the precompiled binaries for your platform:
> 
> 
> 
> Note that sqlite3_analyzer is a compiled Tcl program.  It should work
> fine under any Linux variant but if you have any trouble with it
> please post here.
> 
> Simon.

Hi Simon,

be careful with "under any Linux" as the name of the zip says the
binarys are for X86 only: sqlite-tools-linux-x86-321.zip

On Raspberry it will not work.

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

Re: [sqlite] finding the number of records until a value is different

[Kees Nuyt]

On Thu, 07 Dec 2017 19:46:21 +0100, nitpi...@arcor.de wrote:

> Hi all,
>
> I have a DB i.e. like this:
>
> table values
> bctemp
> 35123
> 35124
> 35123
> 20123
> 12123
> 12123
> 16123
> 35123
> 35123
> 35123
> 35123
> 35123
>
> The value in temp (or all the other columns) is not of interest.
>
> Now I a looking for the basic concept how to count the number of rows where 
> bc=bc_of_last_row after the last different bc.
>
> SELECT COUNT(bc) FROM values WHERE bc=35;
>
> gives me the number of all rows with bc=35, so here 8.
> But I am looking for 5, the number of rows after the last change of bc. 

I recently had a similar but different requirement:
Store a series of CPU temperatures over time, but if the
temperature stays the same, just increment a count.
Again, not the same as your problem, but my solution might
inspire you with yours.

CREATE TABLE cputemp (
epoch  INTEGER PRIMARY KEY NOT NULL -- "epochfr"
,   epochto  INTEGER DEFAULT NULL
,   ctempINTEGER NOT NULL
);

CREATE TRIGGER before_ins_cputemp BEFORE INSERT ON cputemp
FOR EACH ROW
WHEN NEW.ctemp == (SELECT ctemp FROM cputemp WHERE epoch ==
(SELECT max(epoch) FROM cputemp))
BEGIN
UPDATE cputemp set epochto = NEW.epoch
WHERE epoch == (SELECT max(epoch) FROM cputemp);
SELECT RAISE(IGNORE); -- do not insert a new row
END;

-- Optional trigger: set epochto in new rows,
-- could be done in other ways.
CREATE TRIGGER after_ins_cputemp AFTER INSERT ON cputemp
FOR EACH ROW
WHEN NEW.epochto IS NULL
BEGIN
UPDATE cputemp SET epochto = NEW.epoch
WHERE epoch == NEW.epoch;
END;

The INSERT looks like (awk code):

printf "INSERT INTO cputemp (epoch,ctemp) " \
"VALUES ( 0 + strftime('%%s','now'),%s);\n",cputemp

This can also be done in other ways of course.

Hope this helps.

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

Re: [sqlite] finding the number of records until a value is different

[Keith Medcalf]

Have your application read the table in reverse order.  Then when the value of 
BC changes, stop incrementing a counter and close the select.  You can use 
whatever ordering you like just so long as it is "descending" (that is reading 
backwards).  ((Code example in Python -- you can use wordier and/or more 
obfuscated languages if you so choose -- but anyone should be able to 
understand this code fragment)).


bc = None
count = 0
for row in db.cursor().execute('select bc from db where bc is not null order by 
rowid desc;'):
 if bc is None:
  bc = row.bc
 if bc != row.bc:
  break
 count += 1


When the loop is done bc will contain the value of bc, and count will contain 
the count of that value since it last changed (null values completely ignored 
since you did not specify as part of your problem statement whether or not 
there are null values, and what to do with them if there are some, so I assumed 
that you would want to ignore them -- if this is a school assignment then it is 
faulty and incompletely specified and the correct answer is that no answer is 
possible).

It will also be fast like diarrhea from goose on an excessively hot and humid 
summer day in Hoostun.  Unless of course you will have hugely long runs of the 
same value at the end of the query, in which case (if say there are a million 
of two duplicates values at the end of the table) it might be faster to count 
them in SQL.  Of course, YMMV and applying premature optimization to the 
problem is almost always counter-productive.

This is because it took about 35 nanoseconds to compute the algorithm to solve 
the problem.  Computing the correct SQL query (and testing it) will take at 
least a million orders of magnitude longer (meaning it will cost a million 
times more to implement).  Time is money and all that.

You can probably do it in SQL but it will be far far far far far far far slower 
and more inefficient.  Just because you have an SQL database does not mean that 
*everything* must be written as a single SQL query.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of nitpi...@arcor.de
>Sent: Thursday, 7 December, 2017 11:46
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] finding the number of records until a value is
>different
>
>Hi all,
>
>I have a DB i.e. like this:
>
>table values
>bc temp
>35 123
>35 124
>35 123
>20 123
>12 123
>12 123
>16 123
>35 123
>35 123
>35 123
>35 123
>35 123
>
>The value in temp (or all the other columns) is not of interest.
>
>Now I a looking for the basic concept how to count the number of rows
>where
>bc=bc_of_last_row after the last different bc.
>
>SELECT COUNT(bc) FROM values WHERE bc=35;
>
>gives me the number of all rows with bc=35, so here 8.
>But I am looking for 5, the number of rows after the last change of
>bc.
>
>Regards Matth
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] finding the number of records until a value is different

[Marc L. Allen]

Ok I'm on this list because I love reading all the comments and discussion 
about sqlite and DBs in general.  I haven't used sqlite in quite awhile, so I 
don't know how well this will work, but...

Assuming you have a row number as well, such as

CREATE TABLE x
(
row_number int,
bc int
)

Then you can use something like:

SELECT a.last_rn - (SELECT MAX(row_number) FROM x WHERE bc != a.last_bc)
FROM (SELECT TOP 1 bc AS last_bc, row_number AS last_rn FROM x ORDER BY 
row_number DESC) a

I know this works on MS SQL SERVER.  There may be a more efficient way, and 
you'll need to modify it based on size of table and available indexes.  I think 
the above should work fairly well if you have indexes on both the row_number 
and bc.

Marc

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of nitpi...@arcor.de
Sent: Thursday, December 7, 2017 1:46 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] finding the number of records until a value is different

Hi all,

I have a DB i.e. like this:

table values
bc  temp
35  123
35  124
35  123
20  123
12  123
12  123
16  123
35  123
35  123
35  123
35  123
35  123

The value in temp (or all the other columns) is not of interest.

Now I a looking for the basic concept how to count the number of rows where 
bc=bc_of_last_row after the last different bc.

SELECT COUNT(bc) FROM values WHERE bc=35;

gives me the number of all rows with bc=35, so here 8.
But I am looking for 5, the number of rows after the last change of bc. 

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


Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_analyzer for Debian

[Simon Slavin]

On 7 Dec 2017, at 6:37pm, Cecil Westerhof  wrote:

> I was looking at:
>Tcl'2017 - SQLite's use of Tcl (Richard Hipp)
> 
> There is talk about the sqlite3_analyzer. But I do not have this on my
> Debian system. Is that only for Windows?

It’s not part of the standard Debian installation, but you can download it as 
part of the precompiled binaries for your platform:



Note that sqlite3_analyzer is a compiled Tcl program.  It should work fine 
under any Linux variant but if you have any trouble with it please post here.

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

Re: [sqlite] finding the number of records until a value is different

[David Raymond]

The question you're gonna get asked of course is "what are you sorting by?"

If it's for example rowid, then this convoluted thing will work, though 
probably inefficiently.

create table tbl (bc int, temp int);
insert into tbl values (35, 123), (35, 124), (35, 123), (20, 123), (12, 123), 
(12, 123), (16, 123), (35, 123), (35, 123), (35, 123), (35, 123), (35, 123);

select count(*) from tbl where rowid > (select max(rowid) from tbl where bc != 
(select bc from tbl where rowid = (select max(rowid) from tbl)));

If you're sorting by multiple columns then it'll get more complex. And I didn't 
test it with a million record table, so don't know how it'll scale.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of nitpi...@arcor.de
Sent: Thursday, December 07, 2017 1:46 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] finding the number of records until a value is different

Hi all,

I have a DB i.e. like this:

table values
bc  temp
35  123
35  124
35  123
20  123
12  123
12  123
16  123
35  123
35  123
35  123
35  123
35  123

The value in temp (or all the other columns) is not of interest.

Now I a looking for the basic concept how to count the number of rows where 
bc=bc_of_last_row after the last different bc.

SELECT COUNT(bc) FROM values WHERE bc=35;

gives me the number of all rows with bc=35, so here 8.
But I am looking for 5, the number of rows after the last change of bc. 

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

[sqlite] finding the number of records until a value is different

[nitpilot]

Hi all,

I have a DB i.e. like this:

table values
bc  temp
35  123
35  124
35  123
20  123
12  123
12  123
16  123
35  123
35  123
35  123
35  123
35  123

The value in temp (or all the other columns) is not of interest.

Now I a looking for the basic concept how to count the number of rows where 
bc=bc_of_last_row after the last different bc.

SELECT COUNT(bc) FROM values WHERE bc=35;

gives me the number of all rows with bc=35, so here 8.
But I am looking for 5, the number of rows after the last change of bc. 

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

[sqlite] sqlite3_analyzer for Debian

[Cecil Westerhof]

I was looking at:
Tcl'2017 - SQLite's use of Tcl (Richard Hipp)

There is talk about the sqlite3_analyzer. But I do not have this on my
Debian system. Is that only for Windows?

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