What are Date & Time Functions?
Date Function Examples
| Function | Input | Output | Use Case |
|---|---|---|---|
| CURRENT_DATE | - | 2024-11-20 | Get today |
| EXTRACT(YEAR FROM date) | 2024-11-20 | 2024 | Get year |
| EXTRACT(MONTH FROM date) | 2024-11-20 | 11 | Get month |
| AGE(birth_date) | 2000-05-15 | 24 years | Calculate age |
4 rows
Functions to work with dates and times - get today's date, extract year/month, calculate differences, etc.
Simple analogy: Like a calendar and clock built into SQL.
Common Date Functions
Date Function Examples
| Function | Input | Output | Use Case |
|---|---|---|---|
| CURRENT_DATE | - | 2024-11-20 | Get today |
| EXTRACT(YEAR FROM date) | 2024-11-20 | 2024 | Get year |
| EXTRACT(MONTH FROM date) | 2024-11-20 | 11 | Get month |
| AGE(birth_date) | 2000-05-15 | 24 years | Calculate age |
4 rows
CURRENT_DATE - Get Today
SELECT CURRENT_DATE;Output: 2024-11-20
CURRENT_TIMESTAMP - Get Now (Date + Time)
SELECT CURRENT_TIMESTAMP;Output: 2024-11-20 13:45:30
EXTRACT - Get Part of Date
SELECT EXTRACT(YEAR FROM order_date) FROM orders;Example:
- Input: 2024-11-20
- Output: 2024
DATE_PART - Another Way to Extract
SELECT DATE_PART('month', order_date) FROM orders;Example:
- Input: 2024-11-20
- Output: 11
AGE - Calculate Difference
SELECT AGE(CURRENT_DATE, birth_date) FROM students;Example:
- Birth: 2000-05-15
- Today: 2024-11-20
- Output: 24 years
Real Examples
Date Function Examples
| Function | Input | Output | Use Case |
|---|---|---|---|
| CURRENT_DATE | - | 2024-11-20 | Get today |
| EXTRACT(YEAR FROM date) | 2024-11-20 | 2024 | Get year |
| EXTRACT(MONTH FROM date) | 2024-11-20 | 11 | Get month |
| AGE(birth_date) | 2000-05-15 | 24 years | Calculate age |
4 rows
Example 1: Get Year from Date
SELECT
order_id,
order_date,
EXTRACT(YEAR FROM order_date) AS year
FROM orders;Example 2: Find Recent Orders
SELECT *
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days';Orders from last 7 days.
Example 3: Calculate Age
SELECT
name,
birth_date,
DATE_PART('year', AGE(birth_date)) AS age
FROM students;Example 4: Group by Month
SELECT
EXTRACT(MONTH FROM order_date) AS month,
COUNT(*) AS order_count
FROM orders
GROUP BY EXTRACT(MONTH FROM order_date);Summary
Date Function Examples
| Function | Input | Output | Use Case |
|---|---|---|---|
| CURRENT_DATE | - | 2024-11-20 | Get today |
| EXTRACT(YEAR FROM date) | 2024-11-20 | 2024 | Get year |
| EXTRACT(MONTH FROM date) | 2024-11-20 | 11 | Get month |
| AGE(birth_date) | 2000-05-15 | 24 years | Calculate age |
4 rows
Common functions:
- CURRENT_DATE: Today's date
- CURRENT_TIMESTAMP: Now
- EXTRACT: Get year/month/day
- DATE_PART: Get date parts
- AGE: Calculate difference
- INTERVAL: Add/subtract time