scenario-based design - improve temporal's ux-handling with wasm-sqlite3

[kaizhu256]

i'm optimistic of a future, where wasm-sqlite will be univerally available in javascript-systems -- and much of the computational/aggregation related-scopes of this proposal can be offloaded to wasm-sqlite.

lets revisit the google-flights scenario (#139 (comment)), and designing a departure/arrival database using temporals and wasm-sqlite:

flight hx68 departs 2020-03-08T11:55:00 from hong-kong to los-angeles and is known to take 775 minutes of flight time. is this how you would insert the record in sqlite3?

let arrival;
let departure;
let flight;
let sqliteWorker;



// create flight arrival/departure database
await sqliteWorker.postMessage({
    action: "exec",
    sql: `
DROP TABLE IF EXISTS flights1;
CREATE TABLE flights1 (
    rowid TEXT PRIMARY KEY,
    flight TEXT,               -- flight number
    duration INT,              -- flight duration (seconds)

    departure_utc TEXT,        -- departure datetime (utc)
    departure_local TEXT,      -- departure datetime (local)
    departure_zone_offset INT, -- departure zone_offset (seconds)
    departure_zone TEXT,       -- departure zone

    arrival_utc TEXT,          -- arrival datetime (utc)
    arrival_local TEXT,        -- arrival datetime (local)
    arrival_zone_offset INT,   -- arrival zone_offset (seconds)
    arrival_zone TEXT          -- arrival zone
);
`
});



// add flight arrival/departure record to sqlite3
flight = "hx68";
departure = CivilDateTime.fromString("2020-03-08T11:55:00").withZone(
    "Asia/Hong_Kong"
);
arrival = departure.plus({minutes: 775}).withZone("Americal/Los_Angeles");

await sqliteWorker.postMessage({
    action: "exec",
    sql: `
INSERT INTO flights1 (
    rowid,
    flight,
    duration,

    departure_utc,
    departure_local,
    departure_zone_offset,
    departure_zone,

    arrival_utc,
    arrival_local,
    arrival_zone_offset,
    arrival_zone
) VALUES (
    ${Math.random().toString(16)},
    ${flight},                                    -- "hx68"
    ${arrival.difference(departure, "seconds")},  -- 46500

    ${departure.instant.toString().slice(0, 19)}, -- "2020-03-08T19:55:00"
    ${departure.toString().slice(0, 19)},         -- "2020-03-08T11:55:00"
    ${departure.offset},                          -- 28800
    ${departure.ianaZone},                        -- "Asia/Hong_Kong"

    ${arrival.instant.toString().slice(0, 19)},   -- "2020-03-08T02:50:00"
    ${arrival.toString().slice(0, 19)},           -- "2020-03-08T09:50:00"
    ${arrival.offset},                            -- -25200
    ${arrival.ianaZone}                           -- "America/Los_Angeles"
);
`
});



// get reminder on how many days until [most-recent] flight hx68 from today
await sqliteWorker.postMessage({
    action: "exec",
    sql: `
SELECT
        flight,
        CAST(JULIANDAY(departure_utc) - JULIANDAY('now') AS INT) AS days
    FROM flights1
    WHERE flight = 'hx68' AND days >= 0
    ORDER BY days
    LIMIT 1;
`
});
// result
// flight    days
// hx68	     170



// get earliest flight from hk to la departing in month 2020-03 (la localtime)
await sqliteWorker.postMessage({
    action: "exec",
    sql: `
SELECT flight, departure_local FROM flights1
    WHERE
    departure_zone = 'Asia/Hong_Kong'
    AND arrival_zone = 'America/Los_Angeles'
    AND SUBSTR(departure_local, 0, 8) = '2020-03'
    ORDER BY departure_local
    LIMIT 1;
`
});
// result
// flight    departure_local
// hx68	     2020-03-08T11:55:00



// get all flights departing in month 2020-03 (sorted by utc-datetime)
await sqliteWorker.postMessage({
    action: "exec",
    sql: `
SELECT flight, departure_utc FROM flights1
    WHERE SUBSTR(departure_utc, 0, 8) = '2020-03'
    ORDER BY departure_utc;
`
});
// result
// flight    departure_utc
// hx68      2020-03-08T19:55:00

[pipobscure]

Thanks, but that just answers the question of where the calculations take place and presumes that Temporal means that they take place in JS-code.

So from the point of view of making Time and Date be things more easily dealt with by JS developers, this is just a side-note that even now it’s both possible and with sqlite-wasm can be done performantly.

Seems a bit off topic for issues on defining a human usable API.