SQL traps · Chapter 10 of 16

timestamp without a time zone

Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Time
  • 3 tasks
  • SQL
  • In the all labs plan

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.

What you build: 3 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    repro.sql: 9 am out at 2:30 pm, on purpose

    Write repro.sql. It runs in a fresh, empty database where schedule.sql has just made the table scheduled_posts and the function due_posts(moment), which counts the posts due at that moment. Set the session's time zone to India's (Asia/Kolkata), as the app does for a member, and schedule one post for 2026-10-26 09:00, the wall clock the member typed, with no offset. Then set the time zone to UTC, as on the scheduler's new server. End with a SELECT that gives one value: how many posts due_posts says are due at 2026-10-26 09:00+05:30, 9 am in India.

    The check runs your file twice, each time in a fresh database: beside the original schedule.sql it must give 0 (the post is not due at 9 am), and beside a fixed schedule.sql it must give 1. So it has to really change the time zone between saving and asking: a file that only says SELECT 0 fails the second run. Run it with node run.mjs in the terminal.

  2. Task 2

    schedule.sql: posts go out on time again

    Fix schedule.sql so a post is due at the moment it was scheduled for, whatever the time zone of the session that saved it and of the session that asks. A post saved as 2026-10-26 09:00 from a session in India is due at 9 am India time when asked from India, from UTC or from New York, and not due a minute before. A post saved with an explicit offset (2026-10-27 03:30+00, from a UTC session) is due at exactly that moment. A post saved as 09:00 from a session in Dubai is due at 9 am Dubai time, which is 10:30 am in India.

    The bug is in the table's design, so the fix may change the table. Keep the table's name and its columns (id, author, body, publish_at), and keep due_posts(moment timestamptz) returning how many posts are due. The check uses its own posts and time zones, and shifting the hours by hand is right in one time zone only.

  3. Task 3

    schedule.test.sql: a test that would have caught it

    Write a SQL test file, schedule.test.sql, that would have caught this bug before Saturday's server move. Use the lab's ok(condition, name) and is(got, want, name): each call is one test. The check runs your file three times, each in a fresh database where a version of schedule.sql has just made the table: beside the original schedule.sql at least one test must fail; beside the fixed schedule.sql every test must pass and nothing may raise an error; beside a hotfix that keeps the old column and takes five and a half hours off publish_at at least one test must fail.

    So schedule a post from a session in India, then ask due_posts from a UTC session and again from an India session, and test both sides each time: the post is due at the moment it was scheduled for, and not due a minute before. Run it with node run.mjs schedule.test.sql.

What you will learn

  • repro.sql: 9 am out at 2:30 pm, on purpose
  • schedule.sql: posts go out on time again
  • schedule.test.sql: a test that would have caught it

More in Time

Build "timestamp without a time zone" yourself

A year after Chai aur SQL, MingleSphere sends a Sunday digest, pays its creators and shows investors a dashboard, and things break the way they break in real teams. Anya, the lead backend engineer, hands you each ticket. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of SQL traps are free. This one comes with the all labs plan.