Intermediate · Lesson 9 of 14

Dates and strings

Buckets, windows, and cleanup.

Date arithmetic

Postgres has real DATE and TIMESTAMP types that sort and compare correctly out of the box. Adding an integer shifts a date by days (signup_date + 30), TO_CHAR(x, 'YYYY-MM') extracts a month bucket, and subtracting two dates gives the days between them.

String tools

UPPER, LOWER, LENGTH, SUBSTRING, and REPLACE handle text. STRING_AGG joins values into one cell. String questions mostly test whether you reach for these instead of writing loops.

Buckets over raw timestamps

Every reporting question is a bucket question: daily, monthly, cohort. Learning TO_CHAR and simple date casts well pays off across most of your practice.

Examples

Monthly bucket

Signups grouped by calendar month.

SELECT TO_CHAR(signup_date, 'YYYY-MM') AS month, COUNT(*) AS signups FROM accounts GROUP BY TO_CHAR(signup_date, 'YYYY-MM') ORDER BY month;

Date offset

A simple additive date window.

SELECT company_name, signup_date, signup_date + 30 AS trial_end FROM accounts LIMIT 5;

String cleanup

String functions are row by row.

SELECT UPPER(plan) AS plan_upper FROM accounts LIMIT 5;