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
