Date/Time Functions
beginneradvanced
2 min read
What are Date/Time Functions?
PostgreSQL has rich built-in support for date and time operations. You can get the current date/time, extract parts of dates, perform arithmetic with intervals, and truncate timestamps to specific precision.
Common Functions
NOW() -- Current timestamp with timezone
CURRENT_DATE -- Current date
CURRENT_TIMESTAMP -- Current timestamp
EXTRACT(part FROM date) -- Get year, month, day, etc.
DATE_TRUNC('month', date) -- Truncate to month
AGE(timestamp1, timestamp2) -- Interval between dates
date + INTERVAL '7 days' -- Date arithmeticWhen to Use
- Filtering records by date ranges
- Grouping data by month, quarter, or year
- Calculating age or duration between events
- Generating date-based reports
Key Points
- EXTRACT — Returns a numeric part: EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date).
- DATE_TRUNC — Truncates to a precision: 'year', 'quarter', 'month', 'week', 'day', 'hour'.
- Intervals — PostgreSQL supports INTERVAL literals: INTERVAL '1 year 2 months 3 days'.
- AGE — Returns an interval: AGE(NOW(), birth_date) gives years, months, days.
- Timezone — Use AT TIME ZONE to convert between timezones.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Date/Time Functions Challenge
Write a query that retrieve the current timestamp and date.
Expected result
The current timestamp and today's date.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are Date/Time Functions?
Initializing database...Each run starts from fresh sample data.
More Examples
Extract year and month from enrollments
Break down enrollment dates into components.
Initializing database...Each run starts from fresh sample data.
Group orders by month
Count orders per month using DATE_TRUNC.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What is the difference between NOW() and CURRENT_TIMESTAMP?
They return the same value. CURRENT_TIMESTAMP is SQL standard; NOW() is a PostgreSQL function. Both return the transaction start time.
How do I add days to a date?
Use interval arithmetic: date_column + INTERVAL '7 days' or simply date_column + 7 (adds days to a date type).