On 22 May 2013, at 11:48pm, Stephan Buchert <stephanb...@gmail.com> wrote:
> However, Sqlite's julianday seems to have limitations because it returns > NULL for times that fall in leap seconds (in addition, it is off by > presently about 1 min from the Julian Day that is used in astronomy). > Perhaps the reason is that leap second times cannot be represented with a > syntactically valid time string, per > http://www.sqlite.org/lang_datefunc.html, but it really doesn't matter much > what the reason is. I'm suggesting, that Sqlite should have a function that > for the convenience of users returns a monotonically increasing numeric > timestamp for all practical times in the real world, including the leap > seconds. We understand what you want. However, I think the demand for the time conversion function you want is so low it's not worth adding it to SQLite which is designed to be a tiny system, just big enough for most users' requirements. Although your requirements are technically part of the definition of Julian Days, there are no facilities in the common operating systems (Unix timestamps, Windows, etc.) that would let SQLite know which days have leap seconds in. SQLite would have to include its own little table of leap seconds and that kind of detail is beyond even the big SQL engines. The table would have to be modified from time to time as new leap seconds are declared. There might be issues relating to time zones I don't know about. "As represented in seconds since the Epoch, each and every day shall be accounted for by exactly 86400 seconds." -- The Open Group, Single Unix Specification As far as I remember the only software I've ever written that had to worry about leap seconds was in GPS position calculations, in which an extra second leads to significant errors. Even scientific logging software usually ignores the issue. If the function is important to you, presumably for astronomy or some other scientific software, you may choose to implement your desired function yourself as a SQLite extension: <http://www.sqlite.org/loadext.html> Perhaps you might want to take the source code for the existing julian date function and modify it. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
