SQL from zero · Chapter 4 of 37

Time on our side

By the end of this chapter you can work with dates and times: pick out weekdays, measure how long something took, show another time zone and select a whole month. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: First queries
  • 4 tasks
  • SQL
  • In the all labs plan

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

Why you need this

Rohan has a theory that people post more at the weekend, and he wants to know how long the members who left stayed. The photo team's dashboard runs on UTC, so it needs times in UTC. And Vikram wants every post from January.

created_at looks like text (2026-01-02 09:15:00+05:30), but nothing in it says "Saturday", and a stay is the gap between two of these values, which no comparison you know can measure. Compare it like text and you quietly lose rows: the range most people write for January misses posts from the 31st, and nothing warns you.

What you build: 4 tasks

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

  1. Task 1

    weekend-posts.sql: posts written at the weekend

    Find the active posts (no date in deleted_at) that were written on a Saturday or a Sunday, India time (the lab's clock).

    Return two columns: content and created_at. Any order.

  2. Task 2

    stayed.sql: how many days each member who left stayed

    For each member who left (deleted_at has a date), count the whole days from created_at to deleted_at, rounded down: 70 days and 14 hours counts as 70. Count the time that passed, not the difference between the two dates: a stay of 70 days and 16 hours is 70, even when its dates are 71 days apart.

    Return two columns: username and days_stayed. Any order.

  3. Task 3

    utc-times.sql: image posts in UTC

    For every active post (no date in deleted_at) of type image, show the moment it was written as a UTC clock would show it: a timestamp without a time zone, like 2026-01-28 17:10:00.

    Return two columns: content and utc_time. Any order.

  4. Task 4 · interview question

    Interview: january-posts.sql, every post from January 2026

    Find every active post (no date in deleted_at) written in January 2026, India time: from the first moment of 1 January to the last moment of 31 January, both included. A post at 23:59 on 31 January counts; one at midnight on 1 February, or on 31 December 2025, does not.

    Return two columns: content and created_at. Any order.

What you will learn

  • Moments and the lab's clock: what a timestamptz is, and what a date like '2026-01-31' means next to one.
  • Pulling dates apart: extract, date_trunc and to_char.
  • Intervals and time zones: the time between two moments, and the same moment on a UTC clock.
  • Ranges of days: selecting whole days without losing the last one.

More in First queries

Build "Time on our side" yourself

MingleSphereQL, a social app, needs answers from its data every day. 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 from zero are free. This one comes with the all labs plan.