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

Reply via email to