DEV Community

Cover image for SQL Date & Time: From Today's Date to Birthday Reminders in 12 Questions
Navas Herbert
Navas Herbert

Posted on

SQL Date & Time: From Today's Date to Birthday Reminders in 12 Questions

#sql #postgres #beginners #ai

Every dataset has dates in it somewhere. Date of birth. Exam date. Order date. Registration date. And almost every useful question you can ask about that data involves doing something with those dates - comparing them, formatting them, extracting parts of them, calculating distances between them.

Today we worked through 12 questions on Greenwood Academy's student and exam records. Each one built on the last. By Question 12 we were building a birthday reminder query that could run in a real school system - using the same functions we introduced in Question 1.

Here's every question, every function, and every moment that landed.

Q1: What Time Is It Right Now?

Before touching any table data, we started with the simplest date question possible.

SELECT
    CURRENT_DATE       AS today,
    NOW()              AS current_datetime,
    CURRENT_TIMESTAMP  AS timestamp;
Enter fullscreen mode Exit fullscreen mode 
today       | current_datetime              | timestamp
------------|-------------------------------|-------------------------------
2026-07-05  | 2026-07-05 10:23:41.847+03   | 2026-07-05 10:23:41.847+03
Enter fullscreen mode Exit fullscreen mode

Three ways to ask PostgreSQL what time it is. The distinction matters:

  • CURRENT_DATE - date only, no time component
  • NOW() and CURRENT_TIMESTAMP - both return date + time + timezone; they're effectively the same

"Use CURRENT_DATE when you only care about the date - filtering by today, calculating days between two dates. Use NOW() when you need the exact moment something happened - audit logs, timestamps on records."

Q2: Pull the Year, Month, and Day Out Separately

A date of birth stored as 2008-03-15 is a single value. What if you want just the year? Just the month? EXTRACT does it.

SELECT
    first_name,
    date_of_birth,
    EXTRACT(YEAR  FROM date_of_birth) AS birth_year,
    EXTRACT(MONTH FROM date_of_birth) AS birth_month,
    EXTRACT(DAY   FROM date_of_birth) AS birth_day
FROM greenwood_academy.students;
Enter fullscreen mode Exit fullscreen mode 
first_name | date_of_birth | birth_year | birth_month | birth_day
-----------|---------------|------------|-------------|----------
Amina      | 2008-03-15    | 2008       | 3           | 15
Brian      | 2010-11-22    | 2010       | 11           | 22
Njeri      | 2007-07-04    | 2007       | 7            | 4
Enter fullscreen mode Exit fullscreen mode

Syntax: EXTRACT(field FROM column). The field can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW (day of week), and more. We'll use several of these before the end of the session.

"EXTRACT doesn't change the column - it reads it and returns a number. You're not modifying anything, you're asking a specific question about part of the date."

Q3: How Old Is Each Student?

AGE() calculates the interval between two dates. Pass it CURRENT_DATE and a date of birth and it returns the full age - years, months, and days.

SELECT
    first_name,
    last_name,
    date_of_birth,
    AGE(CURRENT_DATE, date_of_birth) AS full_age
FROM greenwood_academy.students;
Enter fullscreen mode Exit fullscreen mode 
first_name | date_of_birth | full_age
-----------|---------------|---------------------------
Amina      | 2008-03-15    | 18 years 3 mons 21 days
Brian      | 2010-11-22    | 15 years 7 mons 14 days
Njeri      | 2007-07-04    | 19 years 0 mons 1 day
Enter fullscreen mode Exit fullscreen mode

The output is a PostgreSQL interval - a readable duration. Perfect for displaying a person's age in a report. Not useful yet if you need to sort or compare ages numerically, which is where Q4 comes in.

Q4: Age in Whole Years - The Nesting Trick

For sorting, filtering, or displaying a clean number, you want age in years only. The solution: nest AGE() inside EXTRACT().

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    EXTRACT(YEAR FROM AGE(date_of_birth)) AS age_years
FROM greenwood_academy.students
ORDER BY age_years DESC;
Enter fullscreen mode Exit fullscreen mode 
full_name       | age_years
----------------|----------
Njeri Kamau     | 19
Amina Hassan    | 18
Brian Otieno    | 15
Enter fullscreen mode Exit fullscreen mode

I wrote the order of operations on the board: "The inner AGE() runs first - it calculates the interval between today and the date of birth. Then the outer EXTRACT(YEAR FROM ...) pulls just the year number out of that interval. Two functions, one clean integer."

This is the first nesting pattern of the session, and it's important - it shows that SQL functions can be composed, each one feeding its output into the next.

Q5: How Many Days Ago Was Each Exam?

Date arithmetic in PostgreSQL is simple: subtract two dates and you get the number of days between them as an integer.

SELECT
    result_id,
    exam_date,
    CURRENT_DATE - exam_date AS days_ago
FROM greenwood_academy.exam_results;
Enter fullscreen mode Exit fullscreen mode 
result_id | exam_date   | days_ago
----------|-------------|----------
1         | 2024-03-15  | 843
2         | 2024-04-02  | 825
3         | 2024-05-10  | 787
Enter fullscreen mode Exit fullscreen mode

date1 - date2 returns an integer. No function required. The simplicity is the point - I told the class: "When you need days between two dates, just subtract them. PostgreSQL handles the calendar for you - leap years, month lengths, all of it. You get the right number."

Q6: When Are Results Released? Adding Time with INTERVAL

You can add time to a date using INTERVAL. The value and the unit go together inside quotes.

SELECT
    result_id,
    exam_date,
    exam_date + INTERVAL '2 weeks' AS result_release_date
FROM greenwood_academy.exam_results;
Enter fullscreen mode Exit fullscreen mode 
result_id | exam_date   | result_release_date
----------|-------------|--------------------
1         | 2024-03-15  | 2024-03-29
2         | 2024-04-02  | 2024-04-16
Enter fullscreen mode Exit fullscreen mode

INTERVAL '2 weeks' and INTERVAL '14 days' give the same result - I ran both to prove it. You can use days, weeks, months, years, hours, minutes - whatever unit fits the problem.

Real-world use: payment due dates, subscription renewal dates, probation end dates, warranty expiry. Any situation where you need to add a fixed duration to a stored date.

Q7: Displaying Dates in a Human-Friendly Format

Dates stored as 2024-03-15 are machine-readable. Reports need Friday, 15th March 2024. TO_CHAR() handles the formatting.

SELECT
    result_id,
    exam_date,
    TO_CHAR(exam_date, 'Day, DDth Month YYYY') AS friendly_date
FROM greenwood_academy.exam_results;
Enter fullscreen mode Exit fullscreen mode 
result_id | exam_date   | friendly_date
----------|-------------|---------------------------
1         | 2024-03-15  | Friday   , 15th March  2024
2         | 2024-04-02  | Tuesday  , 02nd April  2024
Enter fullscreen mode Exit fullscreen mode

One thing worth noting: PostgreSQL pads Day and Month to a fixed width with trailing spaces - you can see "Friday " with extra spaces. Add FM (Fill Mode) at the start of the format to suppress padding: TO_CHAR(exam_date, 'FMDay, DDth FMMonth YYYY').

Q8: Two Formats, One Query

TO_CHAR() is flexible enough to produce any format you need. Here we run two different formats side by side:

SELECT
    result_id,
    exam_date,
    TO_CHAR(exam_date, 'DD/MM/YYYY')  AS first_format,
    TO_CHAR(exam_date, 'Month YYYY')  AS month_year
FROM greenwood_academy.exam_results;
Enter fullscreen mode Exit fullscreen mode 
result_id | exam_date   | first_format | month_year
----------|-------------|--------------|------------
1         | 2024-03-15  | 15/03/2024   | March 2024
2         | 2024-04-02  | 02/04/2024   | April 2024
Enter fullscreen mode Exit fullscreen mode

Common format codes worth memorising:

Code Meaning Example
DD Day number (with leading zero) 05
MM Month number 03
YYYY 4-digit year 2024
Month Full month name March
Mon Abbreviated month Mar
Day Full day name Friday
FMDay Day name, no padding Friday
DDth Day with suffix 15th

Q9: Filtering by Part of a Date

EXTRACT isn't only useful in SELECT - it works inside WHERE too. This is one of the most commonly used date patterns in real queries.

SELECT
    first_name, last_name, date_of_birth
FROM greenwood_academy.students
WHERE EXTRACT(YEAR FROM date_of_birth) = 2008;
Enter fullscreen mode Exit fullscreen mode 
first_name | last_name | date_of_birth
-----------|-----------|---------------
Amina      | Hassan    | 2008-03-15
Samuel     | Kiprop    | 2008-09-11
Enter fullscreen mode Exit fullscreen mode

"You don't need to know the full date. You just need the year. EXTRACT pulls it out, WHERE compares it. This pattern works for any part - filter by month to find March birthdays, filter by day to find everyone born on the 1st of any month."

Q10: Grouping Exams by Month with DATE_TRUNC

How many exams were held in each month? The challenge: exam dates are specific days (2024-03-15, 2024-03-21). You can't GROUP BY the exact date - you'd get one row per exam. You need to snap all March dates to a single value.

DATE_TRUNC() does this - it rounds a date down to the start of a specified unit.

SELECT
    DATE_TRUNC('month', exam_date) AS exam_month,
    COUNT(*)                        AS total_exams
FROM greenwood_academy.exam_results
GROUP BY DATE_TRUNC('month', exam_date)
ORDER BY exam_month;
Enter fullscreen mode Exit fullscreen mode 
exam_month           | total_exams
---------------------|------------
2024-03-01 00:00:00  | 4
2024-04-01 00:00:00  | 3
2024-05-01 00:00:00  | 5
Enter fullscreen mode Exit fullscreen mode

DATE_TRUNC('month', '2024-03-15') returns 2024-03-01. So does DATE_TRUNC('month', '2024-03-21'). Both March dates become the same value - GROUP BY can now treat them as a single group.

"DATE_TRUNC is how you go from daily data to weekly or monthly summaries without changing your raw data. You're not deleting the day - you're temporarily ignoring it for the purpose of grouping."

Q11: Was the Exam on a Weekday or Weekend?

EXTRACT(DOW FROM date) returns the day of the week as a number: 0 = Sunday, 1 = Monday, through 6 = Saturday.

SELECT
    result_id,
    exam_date,
    TO_CHAR(exam_date, 'FMDay')            AS day_name,
    EXTRACT(DOW FROM exam_date)            AS day_number,
    CASE
        WHEN EXTRACT(DOW FROM exam_date) IN (0, 6) THEN 'Weekend'
        ELSE 'Weekday'
    END AS day_type
FROM greenwood_academy.exam_results;
Enter fullscreen mode Exit fullscreen mode 
result_id | exam_date   | day_name  | day_number | day_type
----------|-------------|-----------|------------|----------
1         | 2024-03-15  | Friday    | 5          | Weekday
2         | 2024-04-02  | Tuesday   | 2          | Weekday
3         | 2024-03-17  | Sunday    | 0          | Weekend
Enter fullscreen mode Exit fullscreen mode

Two things working together here: TO_CHAR(exam_date, 'FMDay') gives the readable name. EXTRACT(DOW FROM exam_date) gives the number we can test in CASE WHEN. Sunday is 0 and Saturday is 6 - those two values are the Weekend, everything else is a Weekday.

This is also the first query where CASE WHEN connects directly to a date function - a pattern that shows up constantly in real reporting: label the data based on when something happened.

Q12: The Session Closer - Birthday Reminders

The most complex query of the session. "Find every student whose birthday falls in the next 30 days."

The challenge: dates of birth are from years ago. 2008-03-15 can't be compared directly to today's date range because the year is wrong. You need to rebuild "this year's version" of each birthday.

SELECT
    first_name,
    last_name,
    date_of_birth,
    TO_CHAR(date_of_birth, 'DDth FMMonth') AS birthday
FROM greenwood_academy.students
WHERE
    TO_DATE(
        TO_CHAR(CURRENT_DATE, 'YYYY') || '-' ||
        TO_CHAR(date_of_birth, 'MM-DD'),
        'YYYY-MM-DD'
    ) BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days';
Enter fullscreen mode Exit fullscreen mode

I broke this down step by step before running it:

Step 1 - Extract the current year as text: TO_CHAR(CURRENT_DATE, 'YYYY') → '2026'

Step 2 - Extract the birth month and day: TO_CHAR(date_of_birth, 'MM-DD') → '03-15'

Step 3 - Join them with || (PostgreSQL's string concatenation): '2026' || '-' || '03-15' → '2026-03-15'

Step 4 - Convert that text back to a real date: TO_DATE('2026-03-15', 'YYYY-MM-DD') → 2026-03-15

Step 5 - Check if that date falls in the next 30 days: BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days'

The result: every student with a birthday coming up in the next month, with their birthday formatted as 15th March.

"This is a real-world pattern. Schools use it for birthday lists. HR systems use it for contract renewals. Subscription services use it for renewal reminders. The logic is always the same - rebuild this year's version of a stored date, then test whether it falls in your target window."

The Full Function Reference

Function What it does Example
CURRENT_DATE Today's date 2026-07-05
NOW() Current date + time + timezone 2026-07-05 10:23:41+03
EXTRACT(field FROM date) Pull one part out of a date EXTRACT(YEAR FROM dob) → 2008
AGE(date1, date2) Interval between two dates 18 years 3 mons 21 days
date1 - date2 Days between two dates 843
date + INTERVAL '14 days' Add time to a date 2024-03-29
TO_CHAR(date, format) Format a date as text '15th March 2024'
DATE_TRUNC('month', date) Round date down to month start 2024-03-01
EXTRACT(DOW FROM date) Day of week (0=Sun, 6=Sat) 5 (Friday)
TO_DATE(text, format) Parse text into a date TO_DATE('2026-03-15', 'YYYY-MM-DD')

Practice Problems

Easy:

-- 1. Show each student's first name and the year they were born
-- 2. How many days old is each student? (CURRENT_DATE - date_of_birth)
-- 3. Format each exam date as 'April 2024' style
Enter fullscreen mode Exit fullscreen mode

Medium:

-- Show each student's full name and age in whole years
-- Order from oldest to youngest

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    EXTRACT(YEAR FROM AGE(date_of_birth)) AS age_years
FROM greenwood_academy.students
ORDER BY age_years DESC;
Enter fullscreen mode Exit fullscreen mode

Challenge:

-- Monthly exam summary:
-- Show which month each exam fell in, how many exams that month,
-- and whether the month was in Term 1 (Jan-Apr), Term 2 (May-Aug),
-- or Term 3 (Sep-Dec)
-- Use DATE_TRUNC for grouping and CASE WHEN + EXTRACT for the term label
-- Your query here...
Enter fullscreen mode Exit fullscreen mode

I'm a data trainer in Nairobi running a full data programme

Top comments (0)