SQLite tools on OS-X

Newsgroups	comp.sys.mac.system
From	JF Mezei <jfmezei.spamnot@vaxination.ca>
Subject	SQLite tools on OS-X
Message-ID	<WGCeD.61639$1E6.58979@fx07.iad> (permalink)
Date	2018-08-20 13:15 -0400

Show all headers | View raw

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).

Back to comp.sys.mac.system | Previous | Next — Next in thread | Find similar | Unroll thread

Thread

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

csiph-web