Date and Time Functions

Explanation

Dates are everywhere in real data — hires, orders, signups — and SQL gives you a rich toolkit to slice and shift them:

• EXTRACT(part FROM date) — pull out year, month, day, etc. as a number
• DATE_TRUNC(unit, date) — round a timestamp down to the start of a unit
• INTERVAL — add/subtract time (date + INTERVAL '3 months')
• NOW() / CURRENT_DATE — the current moment

Date arithmetic with intervals avoids the bugs of manual math and respects month lengths and leap years.

Syntax

SELECT
  EXTRACT(YEAR FROM hire_date) AS hire_year,
  hire_date + INTERVAL '1 year' AS anniversary,
  DATE_TRUNC('month', hire_date) AS hire_month
FROM employees;

Interactive Example

Count hires per year. Then compute each employee's tenure in years from their hire date.

Common Mistakes

• Subtracting dates as if they were numbers. Use interval arithmetic or a dedicated AGE/DATEDIFF function; raw subtraction rules vary by database.
• Stringly-typed dates. Store dates as DATE/TIMESTAMP, not text, so functions and comparisons work correctly.
• Mixing time zones blindly. Be explicit about time zones for global apps; NOW() is usually server-local.

Best Practices

• Prefer EXTRACT and DATE_TRUNC for grouping by date parts.
• Use INTERVAL for adding/subtracting time — it handles calendar quirks.
• Store dates in proper date types, not strings.

Practice Question

Write a query that returns each employee's first_name, hire_date, and the hire_month truncated to the month, ordered by hire_date.

Summary

Date functions (EXTRACT, DATE_TRUNC, INTERVAL, NOW) let you read and shift dates safely. Group by truncated dates and use interval arithmetic to avoid calendar bugs.

FAQ