Re: [sqlite] How can I convert from Julian time to a tm structure?
We just lifted the routines out of Sqlite to do that. They are in date.c. By making an Sqlite-style date type and a small library of date manipulation routines we move date conversion to the application. It is handy when handling ISO8601 and HTTP date formats plus integrating with file agees. Rob Richardson wrote: Greetings! I am trying to speed up an application that queries a table with three columns and around a million records, and one of the fields is a timestamp. One thing I want to do is to move the conversion of the timestamp from a Julian time to a human-readable time from the query to my application. (By the way, this is a C++ app written in MS Visual Studio 6.) I could build a query and have SQLite execute it, something like "SELECT datetime(123456.789) AS timestring", but that has the overhead of preparing the query, executing it and finalizing it, plus the overhead of converting from a string representation into the tm structure once I get the result of the query. I didn't see any little utility function in the SQLite library that just exposes whatever routine SQLite uses to do the conversion. Does one exist? There must be plenty of algorithms out there to do this conversion. A quick search revealed a few, but they were obviously faulty (assuming every year has 365.25 days, for instance) or not precise enough (returning only the day). I need an algorithm that is accurate to the nearest second. What should I use? Or is the SQLite query the best I'm going to do? Thank you very much. Rob Richardson - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How can I convert from Julian time to a tm structure?
Dr. Hipp, Thank you very much for the link to the source for the Julian date calculations. I did pull the code out of date.c, and I'm using it. But the code seems to rely on implicit conversions between floating-point numbers and integers that I assume must be intentional, but it looks error-prone to me. While I am quite confident that you and your collaborators checked this code carefully, I would like to see an explanation of this algorithm to understand it more fully. Maybe I'll see if I can get the book through an inter-library loan someplace. Rob Richardson - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How can I convert from Julian time to a tm structure?
Hi Rob, Regarding: One thing I want to do is to move the conversion of the timestamp from a Julian time to a human-readable time from the query to my application. I may not understand your request fully, but are you asking for more information than is provided in the source files for the conversion routines? (You probably know the full source is online.) BTW, would it be trivially easy to run your query both with and without using sqlite to do the conversion and compare the times? (You may need to run the tests again, in reverse order, to guard against speedups due to caching.) If, with the conversion time reduced to zero, you don't gain much benefit, then working on the outboard conversion would be time wasted. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How can I convert from Julian time to a tm structure?
All right. Smack me upside the head again. I deserve it. SQLite is open source, so I just had to look in the source code. RobR - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How can I convert from Julian time to a tm structure?
Rob Richardson wrote: ... One thing I want to do is to move the conversion of the timestamp from a Julian time to a human-readable time from the query to my application I didn't see any little utility function in the SQLite library that just exposes whatever routine SQLite uses to do the conversion. Does one exist? Check out: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions HTH, Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How can I convert from Julian time to a tm structure?
Greetings! I am trying to speed up an application that queries a table with three columns and around a million records, and one of the fields is a timestamp. One thing I want to do is to move the conversion of the timestamp from a Julian time to a human-readable time from the query to my application. (By the way, this is a C++ app written in MS Visual Studio 6.) I could build a query and have SQLite execute it, something like "SELECT datetime(123456.789) AS timestring", but that has the overhead of preparing the query, executing it and finalizing it, plus the overhead of converting from a string representation into the tm structure once I get the result of the query. I didn't see any little utility function in the SQLite library that just exposes whatever routine SQLite uses to do the conversion. Does one exist? There must be plenty of algorithms out there to do this conversion. A quick search revealed a few, but they were obviously faulty (assuming every year has 365.25 days, for instance) or not precise enough (returning only the day). I need an algorithm that is accurate to the nearest second. What should I use? Or is the SQLite query the best I'm going to do? Thank you very much. Rob Richardson - To unsubscribe, send email to [EMAIL PROTECTED] -
