INTERACTIVE LECTURES
SQL functions & delivery evidence
Guided class activity · apply the ideas from the full SQL lessons.
Preparing for Lab 4 · After practice, try the team challenge.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
Separate calendar dates from clock times
Step 3 of 7 · Database: North Shore Deliveries
READ & UNDERSTAND
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.
Your question
Return id, package_id, and TIME(timestamp) AS scan_clock for scans on 2024-10-01. Order by timestamp, then id.
Explain your evidence
Why could WHERE timestamp = '2024-10-01' return no rows even when scans occurred that day?
Try a query to unlock the worked-answer discussion.
Query results
Predict the result, then run your query.Predict the result, then run your query.
Read-only activity · One statement per run · First 200 rows displayed · Checks compare the complete bounded result, not just this preview.
Explore this query freely in the playground ↗Check the save status above. Account-saved work is available in My Progress. Explanations are for discussion, not automatically graded. No prerequisite locks.