🌱BeginnerConstraints
Date and Time Functions
Work with dates and times: current time, extraction, arithmetic, and formatting.
Overview
Date and time handling is one of PostgreSQL's strong suits. It provides NOW(), CURRENT_DATE, CURRENT_TIMESTAMP for the current moment; EXTRACT and DATE_PART for pulling out components like year, month, day, hour; DATE_TRUNC for rounding to a time unit; and interval arithmetic for adding or subtracting time periods.
AGE() calculates the interval between two timestamps. to_char() formats dates for display. Always store timestamps in UTC (TIMESTAMPTZ) and convert for display mixing time zones in storage leads to subtle bugs.
Key Points
- •NOW() / CURRENT_TIMESTAMP current timestamp with time zone
- •CURRENT_DATE today's date; CURRENT_TIME current time
- •EXTRACT(part FROM ts) get year, month, day, hour, minute, etc.
- •DATE_TRUNC('month', ts) round down to start of month/week/day/etc.
- •ts + INTERVAL '1 day' date arithmetic
- •AGE(ts1, ts2) human-readable interval between two timestamps
Examples
Current time functions
SELECT NOW(); -- current timestamp with TZ
SELECT CURRENT_DATE; -- just the date
SELECT CURRENT_TIME; -- just the time
SELECT CURRENT_TIMESTAMP; -- same as NOW()
SELECT CLOCK_TIMESTAMP(); -- actual wall clock (changes within transaction)Extraction and arithmetic
-- Extract components
SELECT EXTRACT(YEAR FROM NOW()) AS year;
SELECT EXTRACT(MONTH FROM NOW()) AS month;
SELECT EXTRACT(DOW FROM NOW()) AS day_of_week; -- 0=Sunday
-- Date arithmetic with intervals
SELECT NOW() + INTERVAL '7 days';
SELECT NOW() - INTERVAL '1 month';
SELECT DATE '2024-06-15' + 30 AS thirty_days_later;
-- Truncate to start of period
SELECT DATE_TRUNC('month', NOW()) AS start_of_month;
SELECT DATE_TRUNC('week', NOW()) AS start_of_week;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4