Exercise 2 solution

    from datetime import datetime, timezone
    from zoneinfo import ZoneInfo
    import sqlite3

    con = sqlite3.connect(":memory:")
    con.execute("CREATE TABLE meeting (title TEXT, at_utc TEXT NOT NULL, zone TEXT NOT NULL)")

    def add(title, local_text, zone):
        local = datetime.fromisoformat(local_text).replace(tzinfo=ZoneInfo(zone))
        utc = local.astimezone(timezone.utc)
        con.execute("INSERT INTO meeting VALUES (?, ?, ?)",
                    (title, utc.isoformat(timespec="seconds"), zone))

    add("Standup", "2026-11-30 23:30", "America/Los_Angeles")
    add("Review", "2026-12-01 08:00", "Europe/Paris")
    add("Planning", "2026-12-15 10:00", "Asia/Tokyo")
    add("Retro", "2026-12-31 22:00", "Australia/Sydney")

    print("Stored:")
    for row in con.execute("SELECT title, at_utc, zone FROM meeting ORDER BY at_utc"):
        print(" ", row)

    start = datetime(2026, 12, 1, tzinfo=timezone.utc).isoformat(timespec="seconds")
    end = datetime(2027, 1, 1, tzinfo=timezone.utc).isoformat(timespec="seconds")
    print("December (UTC):")
    for (title,) in con.execute(
            "SELECT title FROM meeting WHERE at_utc >= ? AND at_utc < ? ORDER BY at_utc", (start, end)):
        print(" ", title)

    print("Local display:")
    for title, at_utc, zone in con.execute("SELECT title, at_utc, zone FROM meeting ORDER BY at_utc"):
        shown = datetime.fromisoformat(at_utc).astimezone(ZoneInfo(zone))
        print(f"  {title:9} {shown:%Y-%m-%d %H:%M %Z}")

    print("Mixed offsets sort wrongly as text:")
    mixed = ["2026-12-01T09:00:00+09:00", "2026-12-01T01:00:00+00:00", "2026-11-30T20:00:00-05:00"]
    print(" text:", sorted(mixed))
    print(" real:", sorted(mixed, key=datetime.fromisoformat))

Output (run, and exactly as shown):

    Stored:
      ('Review', '2026-12-01T07:00:00+00:00', 'Europe/Paris')
      ('Standup', '2026-12-01T07:30:00+00:00', 'America/Los_Angeles')
      ('Planning', '2026-12-15T01:00:00+00:00', 'Asia/Tokyo')
      ('Retro', '2026-12-31T11:00:00+00:00', 'Australia/Sydney')
    December (UTC):
      Review
      Standup
      Planning
      Retro
    Local display:
      Review    2026-12-01 08:00 CET
      Standup   2026-11-30 23:30 PST
      Planning  2026-12-15 10:00 JST
      Retro     2026-12-31 22:00 AEDT
    Mixed offsets sort wrongly as text:
     text: ['2026-11-30T20:00:00-05:00', '2026-12-01T01:00:00+00:00', '2026-12-01T09:00:00+09:00']
     real: ['2026-12-01T09:00:00+09:00', '2026-12-01T01:00:00+00:00', '2026-11-30T20:00:00-05:00']

Explanation: all four meetings fall in December in UTC, including the
standup, which is still 30 November on the wall clock in Los Angeles; this
shows that "which month" depends on the zone you ask in. Because every stored
value uses the same UTC text format, ordinary ORDER BY and a half-open range
(>= start, < end) work correctly. In the mixed-offset list, 09:00+09:00 is
00:00 UTC, while 01:00+00:00 and 20:00-05:00 are both 01:00 UTC (a tie, so
the original order is kept). Text sorting follows the clock digits, not the
instants, so the order is wrong until you compare them as datetimes. Normalising to UTC before writing
removes the problem.