Date Functions
Overview
Date functions allow you to perform chronological operations, extract date parts, calculate intervals, and handle timezone offsets.
Learning Objectives
- Retrieve active server date and time timestamps.
- Add or subtract date intervals with
DATEADDandDATEDIFF. - Truncate date granularity with
DATE_TRUNCand extract parts withEXTRACT.
Detailed Concept Explanation
Dates are stored as specialized structures. SQL provides rich utility functions:
CURRENT_DATE/CURRENT_TIMESTAMP: Gets active server system clock values.DATEDIFF(unit, start, end): Calculates chronological differences in years, months, or days.DATEADD(unit, interval, date): Adjusts date parameters by offsets.EXTRACT(part FROM date): Obtains single properties like year, month, or day.DATE_TRUNC(grain, date): Discards details below the specified grain, useful for month-over-month aggregations.
Code Examples
SQL
-- Calculate employee tenure in years
SELECT name,
DATEDIFF('year', hire_date, CURRENT_DATE) AS years_tenured
FROM employees;
-- Extract cohort year and month groupings
SELECT
EXTRACT(YEAR FROM signup_date) AS signup_year,
DATE_TRUNC('month', signup_date) AS cohort_month,
COUNT(*) AS user_count
FROM users
GROUP BY 1, 2;
Best Practices
- Avoid Filtering with Functions: Avoid writing
WHERE EXTRACT(YEAR FROM order_date) = 2026because it forces a full scan onorder_date. Instead, writeWHERE order_date BETWEEN '2026-01-01' AND '2026-12-31'.
Interview Perspective
Note
Interview Question: What is DATE_TRUNC and how does it help in analytics?
DATE_TRUNC rounds a timestamp down to a specific grain (e.g. month, day, hour). It keeps the value as a timestamp, allowing you to group daily records into neat monthly blocks while preserving standard date sorting capabilities.
Interactive Challenges
Summary
Date functions extract time attributes and calculate ranges, serving as critical tools in time-series and cohort analysis.
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.