Re: [sqlite] 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  wrote:
> 
> On 26 Nov 2018, at 9:09pm, Scott Perry  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 26 Nov 2018, at 9:09pm, Scott Perry  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  wrote:

> On Nov 20, 2018, at 11:44 AM, Bill Hashman  
> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Guy Harris]

On Nov 20, 2018, at 12:41 PM, Jens Alfke  wrote:

> On Nov 20, 2018, at 11:44 AM, Bill Hashman  
> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Petite Abeille]

> On Nov 20, 2018, at 21:49, Thomas Kurz  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jens Alfke]

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

Re: [sqlite] 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: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

[sqlite] 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

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