Date and Time Functions

Objectives

By the end of this lesson, you should be able to:

• Get the current date with CURRENT_DATE
• Calculate a duration between two dates with AGE()
• Pull out a single part of a date with EXTRACT()
• Add to or subtract from a date

💡 Why this matters: “How long has this employee worked here” or “what year was this hired in” are questions about a stored date, but not the date itself, they need calculation. PostgreSQL’s date functions handle this without exporting data to do the math elsewhere.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the same employees table as the rest of this module. CURRENT_DATE and anything calculated from it will show a different result depending on the day the query actually runs, the exact values below reflect the day this lesson was verified.

CURRENT_DATE

SELECT CURRENT_DATE AS today;

    today
------------
 2026-07-28

CURRENT_DATE returns today’s date according to the database server, no parentheses needed (it’s a reserved keyword, not a function call). Running this same query tomorrow returns tomorrow’s date.

AGE(): Duration Between Two Dates

SELECT first_name, hire_date, AGE(CURRENT_DATE, hire_date) AS tenure
FROM employees
WHERE first_name = 'Priya';

 first_name | hire_date  |       tenure
------------+------------+---------------------
 Priya      | 2019-01-10 | 7 years 6 mons 18 days

AGE(later_date, earlier_date) returns a human-readable interval, years, months, and days, between two dates. Called with a single argument, AGE(hire_date), it’s shorthand for AGE(CURRENT_DATE, hire_date). This result will grow larger every day this query is run again.

EXTRACT(): Pulling Out One Part

SELECT first_name, hire_date, EXTRACT(YEAR FROM hire_date) AS hire_year
FROM employees
WHERE first_name = 'Taylor';

 first_name | hire_date  | hire_year
------------+------------+-----------
 Taylor     | 2018-05-05 |      2018

EXTRACT(field FROM date) pulls out a single component. YEAR, MONTH, and DAY are the most common fields, DOW (day of week, 0 for Sunday) and QUARTER are also available. Unlike hire_date itself (a DATE), the result of EXTRACT() is a plain number, usable directly in arithmetic or a WHERE condition like EXTRACT(YEAR FROM hire_date) = 2018.

Date Arithmetic

SELECT hire_date, hire_date + 90 AS ninety_days_later
FROM employees
WHERE first_name = 'Sam';

 hire_date  | ninety_days_later
------------+-------------------
 2023-02-14 | 2023-05-15

Adding a plain integer to a DATE adds that many days, PostgreSQL understands DATE + integer without needing a special function. Subtracting two dates returns the number of days between them, as a plain integer:

SELECT CURRENT_DATE - hire_date AS days_employed
FROM employees
WHERE first_name = 'Devon';

 days_employed
---------------
          1063

Note the difference from AGE(): date - date gives a single integer (total days), AGE() gives a broken-down interval (years, months, days). Which one to use depends on whether “1063 days” or “2 years 10 months 29 days” is more useful for the report at hand.

Try It

1. Write a query showing CURRENT_DATE on its own.
2. Write a query showing every employee’s first_name and their tenure using AGE().
3. Write a query showing every employee’s first_name and the year they were hired, using EXTRACT().
4. Write a query showing every employee’s hire_date plus 30 days (their one-month mark).

Recap

• CURRENT_DATE returns today’s date, no arguments needed.
• AGE(later, earlier) returns a broken-down interval (years/months/days), AGE(date) alone compares against CURRENT_DATE.
• EXTRACT(field FROM date) pulls out a single component (YEAR, MONTH, DAY, and others) as a plain number.
• date + integer adds days, date - date returns the number of days between two dates as an integer.

Next lesson: a first look at aggregate functions, COUNT, SUM, AVG, MIN, and MAX.