> On Nov 20, 2018, at 11:44 AM, Bill Hashman <bill.hash...@protocad-inc.com> > wrote: > > The timestamp from iOS systems is not compliant with ISO 8601/Unix or other > common timestamps. It appears apple has their start date offset 31 years.
Yes, the ‘epoch’ in Apple’s own APIs (CoreFoundation, Foundation) is 1/1/2001, expressed as a double. But of course the POSIX APIs on Apple platforms use the regular Unix epoch of 1/1/1970 as integer. So apparently the app that generated this database explicitly decided to use Apple’s epoch. > * Is this something that could be built into the ODBC driver source? SQLite doesn’t have a date or timestamp type. It just has some functions that parse and generate ISO date-time strings. So this doesn’t seem like an issue at the SQLite level; it’s just a schema mismatch. (Does SQL itself have a numeric timestamp type, or explicitly endorse the POSIX epoch for numeric timestamps?) > * Could there be an option or built in filter that recognizes that the > timestamp data is not ISO 8601/Unix compliant but it's in iOS timestamp > format? And then it reads the data and then correctly maps the iOS timestamp > data to be ISO 8601/Unix compliant? I have no idea how you could detect that! How would a piece of code know whether a number in a column is supposed to represent a date in 1980 or a date in 2010? The fact that the value is a float vs an integer doesn’t help; I’m sure there are databases that store POSIX-epoch timestamps with sub-second precision, and databases that store Apple timestamps as integers. —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
