Querying a LogTen Pilot Logbook With SQL

LogTen Pro is a fantastic piece of software that I love to use for logging my flights.

I also hate vendor lock-in.

LogTen does provide the option to export flights as a CSV file, but in order to power this website’s realtime flight statistics, I wanted to gain access to the SQLite database that powers LogTen.

As of version 2024.2, the SQLite data file is located in the /Users/[yourusername]/Library/Group Containers/group.com.coradine.LogTenPro/LogTenProData_[some_long_string] folder in a file called LogTenCoreDataStore.sql.

Below is the SQL query that will create a sql view with the data that I consider important in my logbook as an airline pilot.

CREATE VIEW IF NOT EXISTS logbook AS
SELECT 
    date(ZFLIGHT_FLIGHTDATE + 978307200, 'unixepoch')                AS flt_date,
    datetime(ZFLIGHT_ACTUALDEPARTURETIME + 978307200, 'unixepoch')   AS atd,
    datetime(ZFLIGHT_ACTUALARRIVALTIME + 978307200, 'unixepoch')     AS ata,
    AC.ZAIRCRAFT_AIRCRAFTID                                          AS ac_reg,
    ACT.ZAIRCRAFTTYPE_TYPE                                           AS ac_type,
    DEP.ZPLACE_ICAOID                                                AS frm,
    DEP.ZPLACE_LAT                                                   AS frm_lat,
    DEP.ZPLACE_LON                                                   AS frm_lon,
    DEP.ZPLACE_NAME                                                  AS frm_name,
    DEP.ZPLACE_COUNTRYNAME                                           AS frm_country,
    DEP.ZPLACE_COUNTRYCODE                                           AS frm_countrycode,
    ARR.ZPLACE_ICAOID                                                AS to_,
    ARR.ZPLACE_LAT                                                   AS to_lat,
    ARR.ZPLACE_LON                                                   AS to_lon,
    ARR.ZPLACE_NAME                                                  AS to_name,
    ARR.ZPLACE_COUNTRYNAME                                           AS to_country,
    ARR.ZPLACE_COUNTRYCODE                                           AS to_countrycode,
    P1.ZPERSON_NAME                                                  AS p1,
    P2.ZPERSON_NAME                                                  AS p2,
    PH1.ZPERSON_NAME                                                 AS ph1,
    PH2.ZPERSON_NAME                                                 AS ph2,
    ZFLIGHT_TOTALTIME                                                AS total_time_minutes,
    ifnull(round(ZFLIGHT_TOTALTIME * 10 / 60.0, 0) / 10, 0)          AS total_time,
    ifnull(round(ZFLIGHT_PIC * 10 / 60.0, 0) / 10, 0)                AS pic_time,
    ifnull(round(ZFLIGHT_P1US * 10 / 60.0, 0) / 10, 0)               AS p1us_time,
    ifnull(round(ZFLIGHT_SIC * 10 / 60.0, 0) / 10, 0)                AS sic_time,
    ifnull(round(ZFLIGHT_DUALRECEIVED * 10 / 60.0, 0) / 10, 0)       AS dual_time,
    ifnull(round(ZFLIGHT_RELIEF * 10 / 60.0, 0) / 10, 0)             AS heavy_time,
    ifnull(round(ZFLIGHT_NIGHT * 10 / 60.0, 0) / 10, 0)              AS night_time,
    ifnull(ZFLIGHT_TOTALLANDINGS, 0)                                 AS landings,
    ifnull(ZFLIGHT_NIGHTLANDINGS, 0)                                 AS night_landings,
    ZFLIGHT_REMARKS                                                  AS remarks,
    ifnull(ZFLIGHT_DISTANCE, 0)                                      AS distance
FROM ZFLIGHT AS F
    LEFT JOIN ZPLACE AS DEP ON F.ZFLIGHT_FROMPLACE = DEP.Z_PK
    LEFT JOIN ZPLACE AS ARR ON F.ZFLIGHT_TOPLACE = ARR.Z_PK
    LEFT JOIN ZAIRCRAFT AS AC ON F.ZFLIGHT_AIRCRAFT = AC.Z_PK
    LEFT JOIN ZAIRCRAFTTYPE AS ACT ON AC.ZAIRCRAFT_AIRCRAFTTYPE = ACT.Z_PK
    LEFT JOIN ZFLIGHTCREW AS FC ON F.ZFLIGHT_FLIGHTCREW = FC.Z_PK
    LEFT JOIN ZPERSON AS P1 ON FC.ZFLIGHTCREW_PIC = P1.Z_PK
    LEFT JOIN ZPERSON AS P2 ON FC.ZFLIGHTCREW_SIC = P2.Z_PK
    LEFT JOIN ZPERSON AS PH1 ON FC.ZFLIGHTCREW_RELIEF1 = PH1.Z_PK
    LEFT JOIN ZPERSON AS PH2 ON FC.ZFLIGHTCREW_RELIEF2 = PH2.Z_PK
WHERE flt_date <= date('now')
ORDER BY flt_date;

Let me explain a few of the quirky aspects:

• The magic number of 978307200 in the date(ZFLIGHT_FLIGHTDATE + 978307200, 'unixepoch') line is due to the fact that LogTen stores datetime values in reference to January 1st 2001 (I am guessing due to the use of NSDate internally.). This is the number of seconds to convert back to the Unix epoch time.
• Rounding in SQLite uses floating point numbers that are susceptible to rounding errors. This took me a good amount of time to figure out when my SQL query totals differed from the LogTen app. For example, an 87 minute and a 90 minute long flight should both be considered to be 1.5 hours. Try running round(87 / 60.0, 1) and round(87 / 60.0, 1) and see what happens.
• Since we only need 1 decimal place precision, multiplying by 10, rounding to 0 decimal places, and dividing by 10 is a quick fix. See the table below.

Want to see my live flying statistics that use this table? They are over on my Pilot page