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

Groups > comp.sys.mac.system > #117796

Re: SQLite tools on OS-X

Show all headers | View raw

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

Back to comp.sys.mac.system | Previous | Next — Previous in thread | 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