Working with Dates
PostgreSQL has a real DATE type, and many tools for working with dates. The examples below use the order_date column of the orders table from the joins lessons.
Dates are written as text in the format 'YYYY-MM-DD', like '2024-03-15'.
Example 1: Compare Dates
Dates work with =, >, < and BETWEEN, just like numbers:
SELECT order_id, product_name, order_date
FROM orders
WHERE order_date >= '2024-03-01'
ORDER BY order_date;
Output:
order_id | product_name | order_date
----------+--------------+------------
104 | Watch | 2024-03-11
105 | Headphones | 2024-03-28
106 | Speakers | 2024-04-02
107 | Tablet | 2024-04-15
(4 rows)
A later date is "greater" than an earlier date.
Example 2: Add Days to a Date
Add a number to a date to move it forward by that many days:
SELECT order_id, order_date,
order_date + 30 AS return_by
FROM orders
WHERE order_id <= 102;
Output:
order_id | order_date | return_by
----------+------------+------------
101 | 2024-01-15 | 2024-02-14
102 | 2024-02-03 | 2024-03-04
(2 rows)
PostgreSQL knows how long each month is. 30 days after 15 January is 14 February.
Example 3: Days Between Two Dates
Subtract one date from another to get the number of days between them:
SELECT order_id,
DATE '2024-04-30' - order_date AS days_ago
FROM orders
WHERE order_id >= 105;
Output:
order_id | days_ago
----------+----------
105 | 33
106 | 28
107 | 15
(3 rows)
DATE '2024-04-30' tells PostgreSQL that this text is a date.
Example 4: Get Part of a Date with EXTRACT
EXTRACT takes one part out of a date, like the year, month or day:
SELECT EXTRACT(MONTH FROM order_date) AS month,
COUNT(*) AS orders
FROM orders
GROUP BY month
ORDER BY month;
Output:
month | orders
-------+--------
1 | 1
2 | 2
3 | 2
4 | 2
(4 rows)
This counts the orders in each month.
Tip
CURRENT_DATE gives today's date, and NOW() gives the current date and time. Their values change every day, so the exercises use fixed dates instead.
In the exercise, find the orders from one month and calculate a date for each.