a bug, date() cannot handle dates in range -1000-0000 (1000BC-0BC)

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

a bug, date() cannot handle dates in range -1000-0000 (1000BC-0BC)

Petr Slansky
I discovered julianday() function and because it is linked to the distant
past, I tried to find some old dates and I found a bug in date() function for
date in range -1000-0000 (1000BC-0BC):

sqlite> select date('-1000-01-01'); -- OK
-1000-01-01
sqlite> select date('-0999-01-01'); -- BUG, should return date '-0999-01-01'
-999-01-01
sqlite> select date('-999-01-01'); -- cannot be converted to '-0999-01-01'

sqlite> select date('-0001-01-01');  -- BUG, should report date '-0001-01-01'
-001-01-01
sqlite> select date('-001-01-01');

sqlite> .version
SQLite 3.22.0 2018-01-22 18:45:57
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
zlib version 1.2.11
gcc-7.4.0
 
With regards,

---------------------------------
  Petr Slansky, [hidden email]


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: a bug, date() cannot handle dates in range -1000-0000 (1000BC-0BC)

J. King-3
On August 24, 2019 1:12:21 p.m. EDT, Petr Slansky <[hidden email]> wrote:

>I discovered julianday() function and because it is linked to the
>distant
>past, I tried to find some old dates and I found a bug in date()
>function for
>date in range -1000-0000 (1000BC-0BC):
>
>sqlite> select date('-1000-01-01'); -- OK
>-1000-01-01
>sqlite> select date('-0999-01-01'); -- BUG, should return date
>'-0999-01-01'
>-999-01-01
>sqlite> select date('-999-01-01'); -- cannot be converted to
>'-0999-01-01'
>
>sqlite> select date('-0001-01-01');  -- BUG, should report date
>'-0001-01-01'
>-001-01-01
>sqlite> select date('-001-01-01');
>
>sqlite> .version
>SQLite 3.22.0 2018-01-22 18:45:57
>0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1
>zlib version 1.2.11
>gcc-7.4.0
>
>With regards,
>
>---------------------------------
>  Petr Slansky, [hidden email]
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

The documentation is explicit that behaviour for negative dates is undefined:

> These functions only work for dates between 0000-01-01 00:00:00 and 9999-12-31 23:59:59 (julian day numbers 1721059.5 through 5373484.5). For dates outside that range, the results of these functions are undefined.

<https://sqlite.org/lang_datefunc.html>
--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: a bug, date() cannot handle dates in range -1000-0000 (1000BC-0BC)

Keith Medcalf
In reply to this post by Petr Slansky

On Saturday, 24 August, 2019 11:12, Petr Slansky <[hidden email]> wrote:

>past, I tried to find some old dates and I found a bug in date()
>function for date in range -1000-0000 (1000BC-0BC)

There is no 0 BC.  The day immediately before 0001-01-01 AD/CE was 0001-12-31 BC(E), and what you refer to as -1000 is really 1001 BC(E).

The SQLite3 datetime functions operate in astronomical years and the behaviour with use of negative astronomical years is undefined.

The input/output routines could be changed such that the sign indicated AD/BC but presently this is not the case.

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




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users