Lesson 6 of 13 · 20 min
Date and time functions
By the end of this lesson you can group timestamps into periods with date_trunc, read parts of a date with extract, format dates with to_char, and build or shift dates with make_date and intervals.
PostgreSQL stores calendar days as date and moments as timestamp (date plus time of day). The shop's ordered_at is a timestamp in Riyadh local time; signup_date is a date. Four functions do most of the work:
| function | question it answers | example → result |
|---|---|---|
date_trunc('month', ts) | which period does it belong to? | 2025-03-17 21:40 → 2025-03-01 00:00 |
extract(part FROM ts) | what is the month / hour / weekday number? | extract(hour FROM ts) → 21 |
to_char(ts, 'format') | how should it be displayed? | to_char(ts, 'Mon YYYY') → Mar 2025 |
age(later, earlier) | how long between two dates, in calendar terms? | 3 years 9 mons 29 days |
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons