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.
- 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.
- 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.
- 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.