Read dates and elapsed time
Part 1 of 3
Separate calendar dates from clock times
The scans timestamp is ISO-style text: YYYY-MM-DD HH:MM:SS. DATE(timestamp) extracts a calendar date; TIME(timestamp) extracts the clock. A date-only string is not equal to a timestamp containing a time, even on the same day.
strftime('%H', timestamp) returns a two-character hour as text, and '%w' returns a weekday code from '0' (Sunday) to '6' (Saturday). Compare consistent formats. For this fixed teaching snapshot, use a fixed date from its data rather than 'now', which changes the question whenever you run it.
Date functions are not general-purpose format guessers. If a value returns NULL, inspect the original string. These timestamps have no zone suffix: the exercise does not establish their timezone or support a real-world timezone conversion.
Worked example
SELECT id, DATE(timestamp) AS scan_day, TIME(timestamp) AS scan_clock, strftime('%H', timestamp) AS hour_text FROM scans ORDER BY timestamp, id LIMIT 5;The date and clock are two views of the same event timestamp. The result still has one row per scan.
The final id key makes equal timestamps deterministic. It does not establish which of two simultaneous physical events happened first.
Part 2 of 3
Measure elapsed time with explicit units
julianday converts a supported timestamp into a day number. Subtract the earlier value from the later value to get elapsed days. Multiply by 24 for hours or 1440 for minutes. Subtracting the original text strings does not calculate elapsed time.
ROUND is a presentation choice applied after the calculation. ABS removes the sign: it is useful when you want a magnitude, but can conceal that start and end were reversed. Preserve direction until you have checked the event sequence.
MIN(timestamp) and MAX(timestamp) identify the observed bounds in this consistently formatted snapshot. Their difference describes the scan window. It is not automatically total shipping time: events before the first scan or after the last scan are not observed.
Worked example
SELECT ROUND((julianday('2024-10-02 10:30:00') - julianday('2024-10-01 09:00:00')) * 24, 1) AS elapsed_hours;The literal example crosses midnight, so subtracting clock times alone would give the wrong interval. The date is part of the calculation.
In the practice, show both endpoints next to the duration. This makes the units and the source window reviewable.
Part 3 of 3
Trace IDs and defend the last observed state
For this lab, investigate across tables using a sequence of single-table queries. First identify a package in packages. Record its id and intended to_address_id. Next filter scans by that package_id and sort by timestamp, then id. Finally look up the relevant address id in addresses.
The handout’s model says a final Drop places the package at the recorded address, while a final Pick puts it on a driver’s truck. Describe this as the last observed state in the snapshot. The intended destination and the last scanned location are different facts.
Keep each query and its output together. A count of scans cannot identify a location; an address id alone does not establish delivery. If latest timestamps tie with conflicting actions, investigate the ambiguity instead of claiming the id sort proves physical order.
Worked example
SELECT id, contents, to_address_id FROM packages WHERE id = 8;
The package record gives the intended destination, not evidence of arrival. Its id becomes the filter in the scan query.
Look up an observed address with SELECT id, address, type FROM addresses WHERE id = the recorded number. Carry the value from your output; do not guess it.