Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.sys.mac.system > #117774 > unrolled thread

SQLite tools on OS-X

Back to article view | Back to comp.sys.mac.system

Contents

  SQLite tools on OS-X JF Mezei <jfmezei.spamnot@vaxination.ca> - 2018-08-20 13:15 -0400
    Re: SQLite tools on OS-X nospam <nospam@nospam.invalid> - 2018-08-20 13:33 -0400
      Re: SQLite tools on OS-X Tim Streater <timstreater@greenbee.net> - 2018-08-20 18:42 +0100
        Re: SQLite tools on OS-X nospam <nospam@nospam.invalid> - 2018-08-20 13:46 -0400
          Re: SQLite tools on OS-X Tim Streater <timstreater@greenbee.net> - 2018-08-20 22:15 +0100
        Re: SQLite tools on OS-X Jolly Roger <jollyroger@pobox.com> - 2018-08-20 22:44 +0000
        Re: SQLite tools on OS-X Lewis <g.kreme@gmail.com.dontsendmecopies> - 2018-08-22 23:56 +0000
    Re: SQLite tools on OS-X Jolly Roger <jollyroger@pobox.com> - 2018-08-20 19:12 +0000
    Re: SQLite tools on OS-X dempson@actrix.gen.nz (David Empson) - 2018-08-21 13:57 +1200
      Re: SQLite tools on OS-X JF Mezei <jfmezei.spamnot@vaxination.ca> - 2018-08-20 23:26 -0400
        Re: SQLite tools on OS-X Tim Streater <timstreater@greenbee.net> - 2018-08-21 09:02 +0100
        Re: SQLite tools on OS-X dempson@actrix.gen.nz (David Empson) - 2018-08-21 22:10 +1200
    Re: SQLite tools on OS-X Lewis <g.kreme@gmail.com.dontsendmecopies> - 2018-08-22 23:54 +0000
      Re: SQLite tools on OS-X JF Mezei <jfmezei.spamnot@vaxination.ca> - 2018-08-22 21:46 -0400
        Re: SQLite tools on OS-X Lewis <g.kreme@gmail.com.dontsendmecopies> - 2018-08-24 05:15 +0000

#117774 — SQLite tools on OS-X

I found the iPhone call log, it is an SQLite database.

I downloaded the SQLlite tools from an sqlite web site, which includes
the sqlite3 program. It lets one create , select, and export data.

The problem is that Apple uses a "julian date" real number for the
date/time of a phone call and the documented

select datefile(ZDATE) from ZCALLRECORD ;  yields a bunch of blank lines.

The documentation states that julian date time (a real number) is the
12th acceptable format for the date/time routines.

Interestingly, most of the Google result point to people calculating
this manually. (It is based on some random Microsoft data in the 5th
century at noon, not midnight).


Has anyone dealt with this before?

Are there better SQLite tools that are able to deal with these real
number timestamps ?


> CREATE TABLE ZCALLRECORD ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZANSWERED INTEGER, ZCALL_CATEGORY INTEGER, ZCALLTYPE INTEGER, ZDISCONNECTED_CAUSE INTEGER, ZFACE_TIME_DATA INTEGER, ZHANDLE_TYPE INTEGER, ZNUMBER_AVAILABILITY INTEGER, ZORIGINATED INTEGER, ZREAD INTEGER, ZDATE TIMESTAMP, ZDURATION FLOAT, ZDEVICE_ID VARCHAR, ZISO_COUNTRY_CODE VARCHAR, ZLOCATION VARCHAR, ZNAME VARCHAR, ZSERVICE_PROVIDER VARCHAR, ZUNIQUE_ID VARCHAR UNIQUE, ZADDRESS BLOB );


The field was created as :

ZDATE TIMESTAMP,


I guess I can bring this up into numbers and perform some fancy
calculation on that column to yield a usage time stamp. Any other
sugghestions?

(this is a one time thing).

[toc] | [next] | [standalone]

#117775

In article <WGCeD.61639$1E6.58979@fx07.iad>, JF Mezei
<jfmezei.spamnot@vaxination.ca> wrote:

> I found the iPhone call log, it is an SQLite database.
> 
> I downloaded the SQLlite tools from an sqlite web site, which includes
> the sqlite3 program. It lets one create , select, and export data.

sqlite is built into mac os. no need to download anything, unless you
want something fancy.

[toc] | [prev] | [next] | [standalone]

#117776

In article <200820181333240079%nospam@nospam.invalid>, nospam
<nospam@nospam.invalid> wrote:

>In article <WGCeD.61639$1E6.58979@fx07.iad>, JF Mezei
><jfmezei.spamnot@vaxination.ca> wrote:
>
>> I found the iPhone call log, it is an SQLite database.
>> 
>> I downloaded the SQLlite tools from an sqlite web site, which includes
>> the sqlite3 program. It lets one create , select, and export data.
>
>sqlite is built into mac os. no need to download anything, unless you
>want something fancy.

Well it gets you a more recent version of the sqlite3 program, which
may be useful under some circs, depending what your are doing.

-- 
New Socialism consists essentially in being seen to have your heart in 
the right place whilst your head is in the clouds and your hand is in 
someone else's pocket.

[toc] | [prev] | [next] | [standalone]

#117777

In article <200820181842054418%timstreater@greenbee.net>, Tim Streater
<timstreater@greenbee.net> wrote:

> >> I found the iPhone call log, it is an SQLite database.
> >> 
> >> I downloaded the SQLlite tools from an sqlite web site, which includes
> >> the sqlite3 program. It lets one create , select, and export data.
> >
> >sqlite is built into mac os. no need to download anything, unless you
> >want something fancy.
> 
> Well it gets you a more recent version of the sqlite3 program, which
> may be useful under some circs, depending what your are doing.

true, but apple is using what's built in.

[toc] | [prev] | [next] | [standalone]

#117780

In article <200820181346226740%nospam@nospam.invalid>, nospam
<nospam@nospam.invalid> wrote:

>In article <200820181842054418%timstreater@greenbee.net>, Tim Streater
><timstreater@greenbee.net> wrote:
>
>> >> I found the iPhone call log, it is an SQLite database.
>> >> 
>> >> I downloaded the SQLlite tools from an sqlite web site, which includes
>> >> the sqlite3 program. It lets one create , select, and export data.
>> >
>> >sqlite is built into mac os. no need to download anything, unless you
>> >want something fancy.
>> 
>> Well it gets you a more recent version of the sqlite3 program, which
>> may be useful under some circs, depending what your are doing.
>
>true, but apple is using what's built in.

I'm not sure you know that. They install a dylib, and the sqlite3 app
uses it, but nothing stops them linking the sqlite library statically
into any of their apps.

-- 
"The problem with defending the purity of the English language is that English
is about as pure as a cribhouse whore. We don't just borrow words; on occasion,
English has pursued other languages down alleyways to beat them unconscious 
and rifle their pockets for new vocabulary."            -- James Nicoll, rasfw

[toc] | [prev] | [next] | [standalone]

#117782

On 2018-08-20, Tim Streater <timstreater@greenbee.net> wrote:
> In article <200820181333240079%nospam@nospam.invalid>, nospam
><nospam@nospam.invalid> wrote:
>
>>In article <WGCeD.61639$1E6.58979@fx07.iad>, JF Mezei
>><jfmezei.spamnot@vaxination.ca> wrote:
>>
>>> I found the iPhone call log, it is an SQLite database.
>>> 
>>> I downloaded the SQLlite tools from an sqlite web site, which includes
>>> the sqlite3 program. It lets one create , select, and export data.
>>
>>sqlite is built into mac os. no need to download anything, unless you
>>want something fancy.
>
> Well it gets you a more recent version of the sqlite3 program, which
> may be useful under some circs, depending what your are doing.

In this case it's not needed at all. The version that comes with macOS
is all that is needed to read the iPhone call log in iTunes backups.

-- 
E-mail sent to this address may be devoured by my ravenous SPAM filter.
I often ignore posts from Google. Use a real news client instead.

JR

[toc] | [prev] | [next] | [standalone]

#117797

In message <200820181842054418%timstreater@greenbee.net> Tim Streater <timstreater@greenbee.net> wrote:
> In article <200820181333240079%nospam@nospam.invalid>, nospam
> <nospam@nospam.invalid> wrote:

>>In article <WGCeD.61639$1E6.58979@fx07.iad>, JF Mezei
>><jfmezei.spamnot@vaxination.ca> wrote:
>>
>>> I found the iPhone call log, it is an SQLite database.
>>> 
>>> I downloaded the SQLlite tools from an sqlite web site, which includes
>>> the sqlite3 program. It lets one create , select, and export data.
>>
>>sqlite is built into mac os. no need to download anything, unless you
>>want something fancy.

> Well it gets you a more recent version of the sqlite3 program, which
> may be useful under some circs, depending what your are doing.

Using a newer version that Apple uses for making the database is
unlikely to be helpful and somewhat likely to fuck things up entirely.


-- 
Everybody hates a tourist, especially one who thinks it's all such
laugh. Yeah, and the chip stains and grease will come out in the bath.
You will never understand how it feels to live your life with no meaning
or control, and with nowhere left to go. You are amazed that the exist,
and they burn so bright whilst you can only wonder why.

[toc] | [prev] | [next] | [standalone]

#117778

On 2018-08-20, JF Mezei <jfmezei.spamnot@vaxination.ca> wrote:
> I found the iPhone call log, it is an SQLite database.

Yes it's at:
	HomeDomain: Library/CallHistoryDB/CallHistory.storedata
	Filename: 5a4935c78a5255723f707230a451d79c540d2741

> I downloaded the SQLlite tools from an sqlite web site, which includes
> the sqlite3 program. It lets one create , select, and export data.

No need for that since macOS comes with sqlite3.

> The problem is that Apple uses a "julian date" real number for the
> date/time of a phone call and the documented
>
> select datefile(ZDATE) from ZCALLRECORD ;  yields a bunch of blank lines.

Shouldn't that be: select datetime(ZDATE)?

I get blanks too. I also tried it with datetime(ZDATE, 'unixepoch') and
got incorrect timestamps.

> The documentation states that julian date time (a real number) is the
> 12th acceptable format for the date/time routines.

I'm not sure these are actually Juian dates.

-- 
E-mail sent to this address may be devoured by my ravenous SPAM filter.
I often ignore posts from Google. Use a real news client instead.

JR

[toc] | [prev] | [next] | [standalone]

#117785

JF Mezei <jfmezei.spamnot@vaxination.ca> wrote:

> I found the iPhone call log, it is an SQLite database.
> 
> I downloaded the SQLlite tools from an sqlite web site, which includes
> the sqlite3 program. It lets one create , select, and export data.

As others have noted, you don't need to download the sqlite3 command
line tools because a new enough version comes with macOS.

There are third party utilities which can read the call log, no need to
muck around with the database directly. I don't know of a free one
offhand, but iMazing can directly read the call log out of the iPhone
backup it makes, and export it as a CSV.

> The problem is that Apple uses a "julian date" real number for the
> date/time of a phone call and the documented
> 
> select datefile(ZDATE) from ZCALLRECORD ;  yields a bunch of blank lines.

I assume that was a typo and you meant

        select datetime(ZDATE) from ZCALLRECORD;

By default, SQLite's date printing functions like datetime and strftime
assume a julian day number as input.

> The documentation states that julian date time (a real number) is the
> 12th acceptable format for the date/time routines.
> 
> Interestingly, most of the Google result point to people calculating
> this manually. (It is based on some random Microsoft data in the 5th
> century at noon, not midnight).

SQLite's Julian day numbers follow the standard: zero is noon on Monday,
January 1, 4713 BC. Time of day is the fractional part.

SQLite also supports UNIX epoch time which counts seconds since
1970-01-01 00:00:00 UTC, ignoring leap seconds.

> Has anyone dealt with this before?

Not that specific database but I'm regularly using SQLite to do
date/time calculations (I haven't had a need for Julian days - I use
UNIX epoch).

> Are there better SQLite tools that are able to deal with these real
> number timestamps ?

The problem isn't better tools, it is working out what epoch and scale
Apple used for its timestamps. Once that is known, the sqlite3 command
line tool can do a calculation to convert the number to either Julian
day or UNIX epoch then use the datetime function to display it.

It is probably easier to use other tools that support SQLite to extract
the data, but it doesn't take much to learn how to output CSV from the
command line tool.


Analysis in progress:

From a quick glance it is obvious that Apple's ZDATE value is not a
Julian day because the numbers are the wrong order of magnitude.

They aren't using UNIX epoch either but that at least appears to be the
right number of digits for 1.0 being 1 second.

A little hunting through my iPhone call log and backup found some
identifiable entries and with those I was able to confirm that the
integer part of ZDATE does indeed increment by 1 for each second.

The numbers are too small to be UNIX epoch, but some logical guesswork
and a test confirmed that Apple is using an epoch of 2001-01-01 00:00:00
UTC for that particular timestamp (other timestamps in iOS databases may
be using a different epoch).

Therefore the following statement in SQLite should print the correct
date:

select
datetime(ZDATE+strftime('%s','2001-01-01'),'unixepoch','localtime') FROM
ZCALLRECORD;

(with any other modifiers you need, e.g. order by, limit, where clauses,
etc., and any other columns which allow you to identify the particular
entries)

That assumes you haven't been travelling to different time zones. If you
have, then you also need to know where you were for each call so you can
use the correct UTC to local time conversion. Remove the 'localtime'
clause and add the offset in seconds from UTC to local time after the
strftime(...) in the above expression.

e.g. NZ is UTC+12:00 which is +43200 seconds for NZ standard time; North
America would be negative offsets.

For reference: strftime('%s','2001-01-01') returns the UNIX epoch time
for midnight 2001-01-01, i.e. number of seconds between the start of
1970 and the start of 2001.

> > CREATE TABLE ZCALLRECORD ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER,
> > Z_OPT INTEGER, ZANSWERED INTEGER, ZCALL_CATEGORY INTEGER, ZCALLTYPE
> > INTEGER, ZDISCONNECTED_CAUSE INTEGER, ZFACE_TIME_DATA INTEGER,
> > ZHANDLE_TYPE INTEGER, ZNUMBER_AVAILABILITY INTEGER, ZORIGINATED
> > INTEGER, ZREAD INTEGER, ZDATE TIMESTAMP, ZDURATION FLOAT,
> > ZDEVICE_ID VARCHAR, ZISO_COUNTRY_CODE VARCHAR, ZLOCATION VARCHAR,
> > ZNAME VARCHAR, ZSERVICE_PROVIDER VARCHAR, ZUNIQUE_ID VARCHAR
> > UNIQUE, ZADDRESS BLOB );
> 
> 
> The field was created as :
> 
> ZDATE TIMESTAMP,
> 
> 
> I guess I can bring this up into numbers and perform some fancy
> calculation on that column to yield a usage time stamp. Any other
> sugghestions?
> 
> (this is a one time thing).


-- 
David Empson
dempson@actrix.gen.nz

[toc] | [prev] | [next] | [standalone]

#117786

On 2018-08-20 21:57, David Empson wrote:

> offhand, but iMazing can directly read the call log out of the iPhone
> backup it makes, and export it as a CSV.


I found 4, one of which read directly from the iPhone, all of which
found the call log and showed it, but refused to export to to any format
unless I bought the full version. Again, this is for a one time use.

BTW, when they asked for my password to decrypt my iTunes backup, I
proceeded to disconnedt the ethernet from my Mac to ensure the app
wouldn't send it back to their developpers.


> From a quick glance it is obvious that Apple's ZDATE value is not a
> Julian day because the numbers are the wrong order of magnitude.

The table creates the field as "timestamp".

Yet, I see no documentation on "timestamp" in SQLlite.


> The numbers are too small to be UNIX epoch, but some logical guesswork
> and a test confirmed that Apple is using an epoch of 2001-01-01 00:00:00
> UTC for that particular timestamp (other timestamps in iOS databases may
> be using a different epoch).


> select
> datetime(ZDATE+strftime('%s','2001-01-01'),'unixepoch','localtime') FROM
> ZCALLRECORD;

MAN MANY thanks for your research.


In my call log, some rows has an integer and some have a floating point
number. If this is using a Unix expoch (seconds since a date), what
would the fractional part of the real number mean ?


And MANY MANY thanks for your research.  I guess Apple was worried about
32bit limitation of Unix time and developped its own based on a more
recent base time instead of Jan 1 1970 for early 32 bit iPhones.


As this is a one time thing, I put the call log in numbers and created a
column with this formula:

DATE(1970,1,1)+ (978307200+ M2− 14400)÷86400


The 978307200 is a fixed offset from Jan 1 1970, and the 13400 is the 4
hour time difference between GMT and Montréal in Summer.

gain, many thanks for putting me on the right track.

Looging at the call log, only *some* calls made prior to 2015-08-20 had
ZDATE values that didn't have fractions.  After that date, they all have
fractions.  (and of those early "integer" dates, Facetime had both
integer and number with fractions, so it wasn't a "phone vs Facetime thing.

[toc] | [prev] | [next] | [standalone]

#117787

In article <5ELeD.230198$LE1.179115@fx35.iad>, JF Mezei
<jfmezei.spamnot@vaxination.ca> wrote:

>On 2018-08-20 21:57, David Empson wrote:
>
>> offhand, but iMazing can directly read the call log out of the iPhone
>> backup it makes, and export it as a CSV.
>
>
>I found 4, one of which read directly from the iPhone, all of which
>found the call log and showed it, but refused to export to to any format
>unless I bought the full version. Again, this is for a one time use.
>
>BTW, when they asked for my password to decrypt my iTunes backup, I
>proceeded to disconnedt the ethernet from my Mac to ensure the app
>wouldn't send it back to their developpers.
>
>
>> From a quick glance it is obvious that Apple's ZDATE value is not a
>> Julian day because the numbers are the wrong order of magnitude.
>
>The table creates the field as "timestamp".
>
>Yet, I see no documentation on "timestamp" in SQLlite.

See https://www.sqlite.org/datatype3.html

SQLite's data types are NULL, INTEGER, REAL, TEXT, and BLOB. You can
put any type of data value into any field, whatever its type. A field
declared as timestamp will probably have numeric affinity.

-- 
The whole aim of practical politics is to keep the populace alarmed
(and hence clamorous to be led to safety) by menacing it with an
endless series of hobgoblins, all of them imaginary.

HL Mencken

[toc] | [prev] | [next] | [standalone]

#117788

JF Mezei <jfmezei.spamnot@vaxination.ca> wrote:

> On 2018-08-20 21:57, David Empson wrote:
> 
> > From a quick glance it is obvious that Apple's ZDATE value is not a
> > Julian day because the numbers are the wrong order of magnitude.
> 
> The table creates the field as "timestamp".
> 
> Yet, I see no documentation on "timestamp" in SQLlite.

I see Tim already answered that one. TIMESTAMP means nothing to SQLite
so it was treated as NUMERIC.

It is worth pointing out (which is mentioned in the documentation) that
in SQLite the column data type is only a hint - it specifies a type
affinity, but you can store any data type in that column. The affinity
gives a hint as to the preferred data type.

For example, given this table:

CREATE TABLE test(x INTEGER);

I can do:

INSERT INTO test VALUES('fred');
INSERT INTO test VALUES('1');
INSERT INTO test VALUES(5);
INSERT INTO test VALUES(NULL);
INSERT INTO test VALUES(99.3);

then get them back again:

.mode columns
.headers on

SELECT x,typeof(x) FROM test;

x           typeof(x) 
----------  ----------
fred        text      
1           integer   
5           integer   
            null      
99.3        real      

The '1' was inserted as text but it parses as an integer, so the integer
type affinity for the column made that row store the value as integer.

The 'fred' was left as text because it doesn't parse as a number.


Therefore it didn't matter what type was specified in the column header
- I looked at the data in the ZDATE column, saw that it was numeric, and
went from there to work out how to interpret the values.

> > The numbers are too small to be UNIX epoch, but some logical guesswork
> > and a test confirmed that Apple is using an epoch of 2001-01-01 00:00:00
> > UTC for that particular timestamp (other timestamps in iOS databases may
> > be using a different epoch).
> 
> 
> > select
> > datetime(ZDATE+strftime('%s','2001-01-01'),'unixepoch','localtime') FROM
> > ZCALLRECORD;
> 
> MAN MANY thanks for your research.
> 
> 
> In my call log, some rows has an integer and some have a floating point
> number. If this is using a Unix expoch (seconds since a date), what
> would the fractional part of the real number mean ?

Presumably a fractional second, which will get truncated and ignored by
the datetime function.

> And MANY MANY thanks for your research.  I guess Apple was worried about
> 32bit limitation of Unix time and developped its own based on a more
> recent base time instead of Jan 1 1970 for early 32 bit iPhones.

That isn't a concern for the SQLite side of things: SQLite uses double
precision floating point and 64-bit integers internally and can handle a
very large range of date/time values.

> As this is a one time thing, I put the call log in numbers and created a
> column with this formula:
> 
> DATE(1970,1,1)+ (978307200+ M2? 14400)÷86400
> 
> 
> The 978307200 is a fixed offset from Jan 1 1970, and the 13400 is the 4
> hour time difference between GMT and Montréal in Summer.
> 
> gain, many thanks for putting me on the right track.
> 
> Looging at the call log, only *some* calls made prior to 2015-08-20 had
> ZDATE values that didn't have fractions.  After that date, they all have
> fractions.  (and of those early "integer" dates, Facetime had both
> integer and number with fractions, so it wasn't a "phone vs Facetime thing.

I also saw a similar pattern with some older entries not having
fractions. It won't matter because datetime() wants an integer so would
do a floating point to integer conversion, truncating the fractional
part. Fractional seconds don't matter for call start times, and probably
not for call durations either.

-- 
David Empson
dempson@actrix.gen.nz

[toc] | [prev] | [next] | [standalone]

#117796

In message <WGCeD.61639$1E6.58979@fx07.iad> JF Mezei <jfmezei.spamnot@vaxination.ca> wrote:
> I found the iPhone call log, it is an SQLite database.

> I downloaded the SQLlite tools from an sqlite web site, which includes
> the sqlite3 program. It lets one create , select, and export data.

Downloaded them to a Mac? Why? SQLite is part of macOS.

/usr/bin/sqlite3

> The problem is that Apple uses a "julian date" real number for the
> date/time of a phone call and the documented

Julian dates are great. Nothing wrong with using them.

> select datefile(ZDATE) from ZCALLRECORD ;  yields a bunch of blank lines.

That entirely depends on the database format and if your command is
correct, but that is well outside the purview of this group.

> The documentation states that julian date time (a real number) is the
> 12th acceptable format for the date/time routines.

> Interestingly, most of the Google result point to people calculating
> this manually. (It is based on some random Microsoft data in the 5th
> century at noon, not midnight).

This makes no sense. The first day in JDN is nearly 5000 BCE (I forget
the exact number, but it is more the 4500 BCE and less than 5000 BCE)
and Microsoft has nothing to do with it.

> Has anyone dealt with this before?

Yes, I used the GNU date function, IIRC.

Basically, you calculate the current epoch time and then add the JDN for
JDN 1 to 1970-01-01 and then convert to the format you need.

<https://en.wikipedia.org/wiki/Julian_day>

echo $(($(TZ=GMT+12 gdate +%s -d "now") / 86400 + 2440587))
2458352

You can calculate the "real" part by figuring what percentage of the
current day has passed.

In most cases, the first 2400000 days are ignored, so 58352 would be
used. There's also a Linian date, which is the days in the Gregorian
calendar, (days since around 1600AD). I have never seen this one used
outside of a textbook talking about JDN.


>> CREATE TABLE ZCALLRECORD ( Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, Z_OPT INTEGER, ZANSWERED INTEGER, ZCALL_CATEGORY INTEGER, ZCALLTYPE INTEGER, ZDISCONNECTED_CAUSE INTEGER, ZFACE_TIME_DATA INTEGER, ZHANDLE_TYPE INTEGER, ZNUMBER_AVAILABILITY INTEGER, ZORIGINATED INTEGER, ZREAD INTEGER, ZDATE TIMESTAMP, ZDURATION FLOAT, ZDEVICE_ID VARCHAR, ZISO_COUNTRY_CODE VARCHAR, ZLOCATION VARCHAR, ZNAME VARCHAR, ZSERVICE_PROVIDER VARCHAR, ZUNIQUE_ID VARCHAR UNIQUE, ZADDRESS BLOB );


> The field was created as :

You should not be creating a table in an existing database.

> ZDATE TIMESTAMP,

You need to ask on a sqlite3 group about that.

> I guess I can bring this up into numbers and perform some fancy
> calculation on that column to yield a usage time stamp. Any other
> sugghestions?

It's much easier to do this stuff in the CLI if you are able to deal
with the commandline.

-- 
I mistook thee for thy better Hamlet Act III scene 4

[toc] | [prev] | [next] | [standalone]

#117798

On 2018-08-22 19:54, Lewis wrote:

>> The field was created as :
> 
> You should not be creating a table in an existing database.
> 
>> ZDATE TIMESTAMP,

.schema lists how a table was created. I didn't create the database or
try to modify it. Apple created it for the iPhone defining a field as
"TIMESTAMP" which the SQLITE3 utility didn't process correctly. (and
which the sqlite documentation doesn't know about).


Mr Empson did the research and provided the information on the Apple
timestamp format (number of seconds since Jan 1 2000)

At the time I asked the question, i was unaware that Apple provided the
sqlite3 utility on OS-X, thought it used sqlite on OS-X and IOS as
run-time only libraries.

[toc] | [prev] | [next] | [standalone]

#117799

In message <HlofD.7802$Ac3.5580@fx45.iad> JF Mezei <jfmezei.spamnot@vaxination.ca> wrote:
> On 2018-08-22 19:54, Lewis wrote:

>>> The field was created as :
>> 
>> You should not be creating a table in an existing database.
>> 
>>> ZDATE TIMESTAMP,

> .schema lists how a table was created. I didn't create the database or
> try to modify it. Apple created it for the iPhone defining a field as
> "TIMESTAMP" which the SQLITE3 utility didn't process correctly. (and
> which the sqlite documentation doesn't know about).

Your knowledge of SQL, and SQLite in specific, is very shallow. You can
set pretty much anything you want as the "Type" of a table.

> At the time I asked the question, i was unaware that Apple provided the
> sqlite3 utility on OS-X, thought it used sqlite on OS-X and IOS as
> run-time only libraries.

It's been in macOS (nee OS X) for a long time. Possible since the Public
Beta. Most of the databases Apple uses are SQLite. A mere instant of
research (like, for example, 

which sqlite3

would have revealed this.

And in fact, since you have posted in the past about vacuuming the mail
database, you DID know about SQLite being in macOS. I couldn't swear to
it in court, but I am at least 95% sure.


-- 
'I don't like to ask them questions.' 'Why not?' 'They might give me
answers. And then what would I do?'

[toc] | [prev] | [standalone]

Back to top | Article view | comp.sys.mac.system

csiph-web