Lesson 11 · Read dates and elapsed time

Separate calendar dates from clock times

Task 1 of 3 · deliveries · 0 results checked · 0 explanations saved

Opening your saved work…

Read the idea

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 task

Return id, package_id, and TIME(timestamp) AS scan_clock for scans on 2024-10-01. Order by timestamp, then id.

Predict or plan before trying

Give me a hint

Extract DATE in WHERE, but TIME in the output. Keep timestamp as the first sort key.

Compare with a worked answer

SELECT id, package_id, TIME(timestamp) AS scan_clock FROM scans WHERE DATE(timestamp) = '2024-10-01' ORDER BY timestamp, id;

Using an answer is useful practice. Record what you learned; it does not demonstrate independent mastery.

Table & column names only · ↑ ↓ choose · Tab insert · Esc dismiss

Query results

Run your query to see the result here.

Explain your evidence

Why could WHERE timestamp = '2024-10-01' return no rows even when scans occurred that day?

My explanation or revision I would like help with this task

Your instructor can review account-saved work and help requests. Saving does not submit to Canvas.