On Sunday, 11 August, 2019 07:45, Gabor Grothendieck <ggrothendi...@gmail.com> wrote:
>R supports Date and POSIXct (date/time) classes which are represented >internally as days and seconds since the UNIX Epoch respectively; >however, due to the class it knows to display and manipulate them as >dates and datetimes rather than numbers. SQLite3 can store integers and doubles. Seems to me this is quite sufficient for storing a "number of seconds" since the Unix Epoch or "number of days" since the Unix epoch. What exactly is the problem? An IEEE-754 double precision floating point number is certainly capable of storing all offsets from the Unix Epoch (either as a days or seconds offset) with far more precision that a human is likely able to discern (or with more precision than the accuracy of most atomic clocks, for that matter). >If sqldf sends a Date or POSIXct to SQLite then it is sent as a >number (days or seconds since the UNIX Epoch) but when it is sent >back it cannot know that that number is supposed to represent a >date or datetime. Why not? The column type declaration in the table is merely an "arbitrary string", and the returned column names from a select are merely "arbitrary strings". It seems like an "application deficiency" that it cannot set and retrieve "arbitrary metadata" for its own internal use (such as providing column type declarations in the CREATE TABLE or additional metadata in the column name (in select statements). Many other "applications" do so without difficulty (for example the default sqlite3 wrapper in Python). Are the R programmers somehow particularly deficient in this regard? I quite often store "timestamps" using application dependent epochs and offsets without difficulty (for example, the number of one-minute intervals since the Unix epoch). Given that just about every programming system and language ever invented seems to store datetime data as some type of offset from some epoch, and each of them different, does not seem to bother interoperability in the least. Once you know the Epoch and Offset interval, conversion is rather simple arithmetic that most children learned in primary school. -- 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
