Postgres in depth · Chapter 18 of 65

When did they last learn?

By the end of this chapter you can calculate with dates and times in Postgres: durations, days between dates, a month or a week as a range, times printed the way people read them, and the same moment in another country's time zone. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Answering LCO's questions
  • 4 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.

Why you need this

Kabir wants to email students who stopped learning months ago, before they forget LCO. Arjun reports signups month by month. Sana gets messages from students in London asking what time a live class starts for them.

All of that is arithmetic on time, and time is harder than numbers. Months have 28 to 31 days, a day ends at a different moment in every country, and some countries move their clocks twice a year. Postgres knows all of that, as long as you let it do the work instead of adding and subtracting hours by hand.

What you build: 4 tasks

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

  1. Task 1

    january-signups.sql: everyone who joined in January

    Arjun's monthly report starts with January 2026. List every student who signed up in January 2026, India time: from the first moment of 1 January to the last moment of 31 January.

    Return two columns: name, signed_up_at. Order: earliest signup first, then name A to Z.

    Careful: students who signed up during the day on 31 January count too, and the edge data has a signup half an hour before New Year.

  2. Task 2

    live-schedule.sql: the live-class schedule

    The schedule page lists every live class. For each one return its title, day (the start's short weekday, two-digit day and short month, like Wed 06 May), starts (the start time as 24-hour hours and minutes, like 19:30) and minutes (how long the class lasts, in minutes, as a number).

    Return four columns: title, day, starts, minutes. Order: earliest start first, then title A to Z.

  3. Task 3

    idle-learners.sql: lessons left idle for 90 days

    Kabir wants to email students who stopped in the middle of a lesson. As of 2026-09-30, list the lesson progress rows that are not completed (completed_at is empty) and were last touched (updated_at) 90 or more days before that date.

    Return four columns: student_id, lesson_id, last_seen (the calendar date of updated_at) and idle_days (the number of days from last_seen to 2026-09-30, as a whole number). Order: most idle first, then student_id, then lesson_id.

    Use the date 2026-09-30 itself, never today's date: the data stops in September 2026.

  4. Task 4 · interview question

    Interview: london-times.sql, the class times in London

    Students in London ask when the live classes start for them. For every live class return its title, starts_at and london (what London's clocks show at that moment, as a timestamp without time zone, like 2026-01-24 13:30:00).

    Return three columns: title, starts_at, london. Order: earliest start first, then title A to Z.

    Careful: London moves its clocks forward on 29 March 2026. Taking a fixed number of hours off is right in winter and wrong in summer.

What you will learn

  • timestamptz, date and interval: a moment, a calendar day, a length of time.
  • Date arithmetic: subtracting moments and dates, adding intervals.
  • Ranges of time: the half-open range, and why BETWEEN loses a day.
  • date_trunc, extract and to_char: rounding, taking apart and printing.
  • Time zones: AT TIME ZONE and summer time; generate_series for a list of dates.

More in Answering LCO's questions

Build "When did they last learn?" yourself

LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.