Postgres with Drizzle · Chapter 33 of 40

The time machine

By the end of this chapter you can answer questions about time: what happened between two dates, how much per month or per day (empty days included), how long between one event and the next, and what happened in the last few hours. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Performance & Superpowers
  • 5 tasks
  • PostgreSQL
  • In the all labs plan

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

Why you need this

Rohan is building the activity dashboard. He wants posts per month for the trend line, posts per day for a chart with no gaps, a member's timeline showing how long they wait between posts, and a live tile with the last hour's activity.

Every row already has a created_at. Without Postgres's time functions you would fetch every post, bucket the dates in JavaScript, invent the missing days yourself and subtract Date objects by hand. That is a lot of code that time zones and month lengths love to break, and it moves every row over the network.

What you build: 5 tasks

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

  1. Task 1

    getPostsByDateRange: posts from one date to another

    Write getPostsByDateRange(startDate, endDate). Both are date strings, like 2024-01-01. It returns the posts whose created_at is at or after startDate and before endDate: the start is included and the end is not, so a post at exactly endDate does not count.

    Return result.rows: objects with id, content and created_at. Order: by created_at, oldest first.

  2. Task 2

    getPostCountByMonth: posts per month

    Write getPostCountByMonth(). It counts all posts per calendar month: one row for each month that has posts.

    Return result.rows: objects with month (the first moment of that month, as a timestamp) and post_count (a number, not a string). Order: by month, oldest first.

  3. Task 3

    generateDateSeries: posts per day, empty days included

    Write generateDateSeries(startDate, endDate, interval). All three are strings, like 2024-01-14, 2024-01-21 and 1 day. It makes one row for every step from startDate to endDate, both included, and counts the posts created on that day. A day with no posts is still there, with 0.

    Return result.rows: objects with date (the step, as a timestamp) and post_count (a number). Order: by date.

  4. Task 4

    getUserActivityTimeline: time between a member's posts

    Write getUserActivityTimeline(userId). userId is a number, the id of a member. It returns that member's posts, oldest first, each with how long it came after the member's previous post and how long ago it was written.

    Return result.rows: objects with id, content, created_at, time_since_last (the interval since the member's previous post; null for their first post) and seconds_ago (whole seconds from created_at until now, a number, not a string).

  5. Task 5

    getRecentActivityStats: the last N hours

    Write getRecentActivityStats(hours). hours is a number. It counts what happened in the last hours hours, up to now: posts created, comments created, and active users (the different authors of those posts).

    Return one object (the first row), not an array, with posts_created, comments_created and active_users, all numbers, not strings. The check adds a post and a comment dated now and asks for the last hour.

What you will learn

  • Half-open ranges: start included, end excluded, so no moment is lost or counted twice.
  • date_trunc and generate_series: buckets of time, and a row for every day even when nothing happened.
  • LAG on timestamps and EXTRACT(EPOCH FROM ...): the gap between events, as an interval or in seconds.
  • Intervals from a parameter: "the last N hours".

More in Performance & Superpowers

Build The time machine yourself

MingleSphereQL grows up: its backend talks to Postgres through Drizzle. 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 with Drizzle are free. This one comes with the all labs plan.