Today's date and date maths

CURRENT_DATE is today's date. You can subtract one date from another to get the days between them, and add or subtract an INTERVAL like '30 days'. Reports usually work as of a date. The tasks here name the date, so the answers don't change from day to day.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Days since, and the last 30 days

SELECT order_id, DATE '2026-03-31' - order_date AS days_ago
FROM orders

Result:

order_id	days_ago
O2001	2
O2002	26
O2003	48
O2004	76
O2005	11
O2006	32

Step by step

A date minus a date gives the days between. For today, use CURRENT_DATE; here the report date is 31 March 2026.
```
SELECT order_id, DATE '2026-03-31' - order_date AS days_ago FROM orders
```

A date minus INTERVAL 'n days' gives an earlier date.

SELECT * FROM orders WHERE order_date >= DATE '2026-03-31' - INTERVAL '30 days'

Practice

Try each one in your head first, then open the answer.

1. Using orders, show order_id and how many days before 31 March 2026 each order was placed. Name it days_ago.

SELECT order_id, DATE '2026-03-31' - order_date AS days_ago FROM orders

2. As of 31 March 2026, show each customer_id and how many days it's been since their last order. Name it days_since.

SELECT customer_id, DATE '2026-03-31' - MAX(order_date) AS days_since FROM orders GROUP BY customer_id

3. Using shipments, show shipment_id and how many days each order took to ship. Name it days_to_ship.

SELECT shipment_id, ship_date - order_date AS days_to_ship FROM shipments

4. As of 31 March 2026, show each customer_id and how many days ago their first order was. Name it days_since_first.

SELECT customer_id, DATE '2026-03-31' - MIN(order_date) AS days_since_first FROM orders GROUP BY customer_id

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed