Timestamps need an offset · SQL
Task: the rules
Input
events(id, raw_ts) — text. id is unique.
Rules
Accept raw_ts when it is YYYY-MM-DD, "T" or one space, HH:MM[:SS[.fraction up to 6 digits]], then an explicit
offset (Z or ±HH:MM, at most ±14:00); hour 00–23, minute/second 00–59; the date must exist. Convert to UTC.
2026-11-01T01:30:00-04:00 and 2026-11-01T01:30:00-05:00 are different instants.
Quarantine with reason:
missing_offset a well-formed date and time with no offset (never assume a zone)
bad_timestamp anything else, including missing values
Outputs (row order does not matter)
clean(id text, ts timestamptz [UTC])
exceptions(id text, reason text)Tab inserts spaces. Press Escape, then Tab, to move focus out of the editor.
Tab inserts spaces. Press Escape, then Tab, to move focus out of the editor.
| # | idstring | raw_tsstring |
|---|---|---|
| 1 | evsk9z | 2026-01-15T09:00:00+15:00 |
| 2 | evp3vk | 2026-11-01T05:30Z |
| 3 | evs5xu | 2026-06-30T23:59:59.123456 |
| 4 | eve3p8 | yesterday |
| 5 | evl74h | 2026-06-30T23:59:59.5+05:30 |
| 6 | evff5w | 2026-11-01T01:30:00 |
| 7 | evdtw2 | 2026-11-01T01:30:00 |
| 8 | evfs8g | 2026-01-01T00:00:00+14:00 |
| 9 | evwupf | 2026-11-01 01:30 |
| 10 | evbd2o | 2026-11-01T01:30:00 |
| 11 | ev6p6c | 2026-11-01 01:30 |
Run preview · in your browser, not a grade
Loading the preview runtime…
Check answer · graded on an isolated server, including hidden datasets
Your code runs in a locked-down machine with no network, on the visible data and on hidden datasets that follow the same rules.
Learn from this exercise
A worked solution becomes available after your first check or once you've used every hint.