SQLite iOS timestamp type mapping settings must be set to float to get correct data

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite iOS timestamp type mapping settings must be set to float to get correct data

Bill Hashman
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite iOS timestamp type mapping settings must be set to float to get correct data

David Raymond
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:[hidden email]] On Behalf Of Bill Hashman
Sent: Tuesday, November 20, 2018 2:45 PM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite iOS timestamp type mapping settings must be set to float to get correct data

Jens Alfke-2
In reply to this post by Bill Hashman


> On Nov 20, 2018, at 11:44 AM, Bill Hashman <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite iOS timestamp type mapping settings must be set to float to get correct data

Thomas Kurz
> (Does SQL itself have a numeric timestamp type, or explicitly endorse the POSIX epoch for numeric timestamps?)

SQL has an explicit TIMESTAMP type since SQL-92, one thing that I'm heavily missing in SQlite ;-)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite iOS timestamp type mapping settings must be set to float to get correct data

Petite Abeille-2


> On Nov 20, 2018, at 21:49, Thomas Kurz <[hidden email]> wrote:
>
>> (Does SQL itself have a numeric timestamp type, or explicitly endorse the POSIX epoch for numeric timestamps?)
>
> SQL has an explicit TIMESTAMP type since SQL-92, one thing that I'm heavily missing in SQlite ;-)

DATE '1998-12-25’ & TIMESTAMP '1997-01-31 09:26:50.124’  literals :))

But do *not* try this:

sqlite> select DATE '1998-12-25’;
   ...>
   ...>
   …>


sqlite3 -version
3.25.3 2018-11-05 20:37:38 89e099fbe5e13c33e683bef07361231ca525b88f7907be7092058007b75036f2
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite iOS timestamp type mapping settings must be set to float to get correct data

Guy Harris
In reply to this post by Jens Alfke-2
On Nov 20, 2018, at 12:41 PM, Jens Alfke <[hidden email]> wrote:

> On Nov 20, 2018, at 11:44 AM, Bill Hashman <[hidden email]> 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.

And this applies to all Apple Darwin-based OSes, including macOS, watchOS, and tvOS, not just iOS.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite iOS timestamp type mapping settings must be set to float to get correct data

Scott Perry
On Nov 20, 2018, at 12:41 PM, Jens Alfke <[hidden email]> wrote:

> On Nov 20, 2018, at 11:44 AM, Bill Hashman <[hidden email]> 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.

That database looks like it was produced by Core Data, which does not use SQLite's timestamp-specific features since NSDate and friends are backed by doubles.

For Bill's purposes—investigating a copied, non-corrupt database—it would probably be easiest to just convert from the Cocoa epoch to the Unix epoch by updating all the columns that currently store Cocoa timestamps. Something like:

    UPDATE ZTIMEENTRY SET ZDATE = ZDATE + 978307200;

Scott
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite iOS timestamp type mapping settings must be set to float to get correct data

Simon Slavin-3
On 26 Nov 2018, at 9:09pm, Scott Perry <[hidden email]> wrote:

> For Bill's purposes—investigating a copied, non-corrupt database—it would probably be easiest to just convert from the Cocoa epoch to the Unix epoch by updating all the columns that currently store Cocoa timestamps. Something like:
>
>    UPDATE ZTIMEENTRY SET ZDATE = ZDATE + 978307200;

If you want to access your date in that form but leave the database usable by Apple's libraries you can create a view which has a new column which modifies the date in the above way.  As long as your VIEW's name does not clash with one Apple wants to use, Apple's utilities should not stop working just because you created a new view in a Core Data database.  I have previously done this without problems but I may have just been lucky.

Come to think of that, Scott, you're in a better position to confirm that than I am.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SQLite iOS timestamp type mapping settings must be set to float to get correct data

Scott Perry
On Nov 26, 2018, at 14:16, Simon Slavin <[hidden email]> wrote:

>
> On 26 Nov 2018, at 9:09pm, Scott Perry <[hidden email]> wrote:
>
>> For Bill's purposes—investigating a copied, non-corrupt database—it would probably be easiest to just convert from the Cocoa epoch to the Unix epoch by updating all the columns that currently store Cocoa timestamps. Something like:
>>
>>   UPDATE ZTIMEENTRY SET ZDATE = ZDATE + 978307200;
>
> If you want to access your date in that form but leave the database usable by Apple's libraries you can create a view which has a new column which modifies the date in the above way.  As long as your VIEW's name does not clash with one Apple wants to use, Apple's utilities should not stop working just because you created a new view in a Core Data database.  I have previously done this without problems but I may have just been lucky.
>
> Come to think of that, Scott, you're in a better position to confirm that than I am.


Local experimentation is one of the greatest learning tools, but I don't recommend making any customizations to Core Data stores that will ever be used on someone else's device as the framework is not especially sympathetic to meddling.

With that disclaimer out of the way, as a general rule Core Data namespaces all of its resources by prefixing them with Z. Migrations (which occur when updating the store to a new model version, or on first use after an operating system update) may destroy schema customizations.

Scott
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users