Interview Question Bank · Timestamp intervals · EXTRACT(EPOCH) · half-open date ranges
4. Viewing hours by device, midnight sessions included
EasyStreamingQuestion #265
Concept: Timestamp intervals · EXTRACT(EPOCH) · half-open date rangesshow
Subtracting two timestamps gives an interval, and EXTRACT(EPOCH FROM interval) turns it into seconds, so dividing by 3600 gives hours with the minutes kept. To bucket by day, cast the timestamp that defines the day to ::date. Filter timestamps with a half-open range (>= first day AND < the day after the last) because BETWEEN '...' AND 'last day' stops at midnight of the last day.
Example (different data):
SELECT clock_in::date AS shift_date, ROUND(SUM(EXTRACT(EPOCH FROM clock_out - clock_in)) / 3600, 2) AS hours FROM shifts WHERE clock_in >= '2025-05-01' AND clock_in < '2025-05-08' GROUP BY 1;
Your task
Pinewood Play, a video streaming service, reports viewing time by device every day. A session counts toward the day it started, even when it runs past midnight.
For sessions that started on 2025-03-01 or 2025-03-02, return view_date (the date the session started), device_type, sessions and hours_watched (total ended_at - started_at in hours, rounded to 2 decimals). Sort by view_date, then device_type.
Starting Postgres in your browser…
Checking your access…