SQL › Working with Dates and Times

Date and Time Functions

Dates are values you can compare, subtract, cut into parts and print in any format. PostgreSQL has DATE (a day), TIMESTAMP (a day and a time) and TIMESTAMPTZ (a moment, with its time zone).

Syntax

Order of execution: FROM → WHERE → SELECT → ORDER BY

Filter with plain date comparisons in WHERE, and format dates only in SELECT. TO_CHAR turns a date into text, which no longer sorts or compares like a date.

Code
SELECT EXTRACT(part FROM date_column), DATE_TRUNC('unit', date_column),
       TO_CHAR(date_column, 'format'), date_column + n
FROM table_name
WHERE date_column >= DATE 'start' AND date_column < DATE 'next start';

Examples

A range of dates

Get everything in one month, including its last day.

Code
-- From the first day up to, but not including, the next month's first day
SELECT order_id, order_date FROM orders
WHERE order_date >= DATE '2025-06-01' AND order_date < DATE '2025-07-01'
ORDER BY order_date;

Parts of a date

Pick out the year, month or weekday, or snap a date to the start of its month.

Code
SELECT order_date,
       EXTRACT(YEAR FROM order_date) AS year,
       EXTRACT(ISODOW FROM order_date) AS weekday_no,      -- 6 = Saturday, 7 = Sunday
       DATE_TRUNC('month', order_date)::date AS month_start
FROM orders;

Date arithmetic

Move a date forward or back, or count the days between two dates.

Code
SELECT order_date,
       order_date + 30 AS return_window_ends,
       DATE '2025-12-31' - order_date AS days_to_year_end,
       order_date + INTERVAL '1 month' AS one_month_later
FROM orders;

Ages and fixed dates

Measure time against a fixed day, so the answer doesn't change tomorrow.

Code
SELECT order_id, AGE(DATE '2025-12-31', order_date) AS order_age,
       EXTRACT(MONTH FROM AGE(DATE '2025-12-31', order_date)) AS whole_months_part
FROM orders;

Formatting

Print dates the way people read them.

Code
SELECT TO_CHAR(order_date, 'DD Mon YYYY'),      -- 02 Jun 2025
       TO_CHAR(order_date, 'Dy'),               -- Mon
       TO_CHAR(order_date, 'YYYY-MM'),          -- 2025-06
       TO_CHAR(order_date, 'Month')             -- 'June     ' (padded to 9 letters)
FROM orders;

In other databases

Dates differ more between databases than anything else in SQL. EXTRACT and DATE '…' are standard, but SQL Server has neither, and AGE, ISODOW and DATE_TRUNC are PostgreSQL's.

Weekday numbers differ too, so check which day is 1 before you use them.

Remember

• For a month, use >= first day AND < next first day. EXTRACT(MONTH …) = 10 matches October of every year.
• In PostgreSQL, EXTRACT(DOW …) numbers Sunday 0; ISODOW numbers Monday 1 to Sunday 7.
• AGE counts whole years only once the birthday has passed; subtracting years doesn't.
• Use a fixed date like DATE '2025-10-31', not CURRENT_DATE, when the answer must stay the same.

Practice

1. October 2025 Delivery Dates (Easy)
2. Weekend Orders in September 2025 (Easy)
3. Customer Ages on 31 October 2025 (Medium)