Short version of my opinion for this is: An application can store whatever it wants in a database. The job of the database software, or the ODBC software is to give you "what they actually stored", not to give you "what they meant by it." So putting this at the ODBC driver level would be bad. But making a (wrapper/shim/whatever it's called) on top of the driver that does this for you is just fine and is where this should get handled.
-----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Bill Hashman Sent: Tuesday, November 20, 2018 2:45 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] SQLite iOS timestamp type mapping settings must be set to float to get correct data Hello to all, I was delving into a SQLite db3 that is a backup from a iOS application when I came across a timestamp translation challenge. 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. But also, the way the timestamp is stored cannot be converted correctly into a timestamp with several open source SQLite ODBC drivers I have tried. It seems the solution I found is to change the timestamp type mapping to float type and then I can at least extract the iOS timestamp data from the db3 file. I used "SQLite Expert Personal version 5.0.0 alpha" from and set the timestamp type mapping to float to get the data. * Is this something that could be built into the ODBC driver source? * 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? Ref: SQLite Expert Personal version 5.0.0 alpha Coral Creek Software www.sqliteexpert.com<http://www.sqliteexpert.com> Source file was from: Apple iOS app that uses a SQLite db3 compliant database: http://priddysoftware.com/home/products/mytime-2 1. Launch app. Put in some time entries and then email the backup db to get the data in a db3 compliant db. 2. The table in the db3 that has a timestamp field that will not translate correctly with the few SQLite ODBC drivers I tried: ZTIMEENTRY 3. The field in the ZTIMEENTRY table that must have the timestamp type setting changed to float to get the data correctly: ZDATE Resepectfully, Bill Hashman 503-899-8780 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users