How to Use Date Functions in PostgreSQL
PostgreSQL provides four fundamental date and time types that serve distinct purposes. DATE stores calendar dates without time information, occupying 4 bytes. TIME stores time of day without date or...
Key Insights
- PostgreSQL offers four primary date/time types (DATE, TIME, TIMESTAMP, TIMESTAMPTZ) each suited for different use cases—use TIMESTAMPTZ for most applications to handle timezones correctly
- Date arithmetic with INTERVAL types is more reliable than manual calculations and automatically handles edge cases like month-end boundaries and leap years
- Applying functions to date columns in WHERE clauses destroys index performance—always structure queries to use bare column comparisons with computed date ranges instead
Introduction to PostgreSQL Date/Time Types
PostgreSQL provides four fundamental date and time types that serve distinct purposes. DATE stores calendar dates without time information, occupying 4 bytes. TIME stores time of day without date or timezone data. TIMESTAMP combines date and time without timezone awareness. TIMESTAMPTZ (timestamp with time zone) stores both date/time and timezone information, making it the most versatile option.
Choose DATE when you only care about calendar days—birthdays, deadlines, or publication dates. Use TIMESTAMP for logging events when all your systems operate in the same timezone. Use TIMESTAMPTZ for any application serving users across timezones or when you need accurate time-based calculations. The INTERVAL type represents a duration and is essential for date arithmetic.
CREATE TABLE events (
event_id SERIAL PRIMARY KEY,
event_name VARCHAR(200),
event_date DATE, -- Just the day
start_time TIME, -- Time without date
created_at TIMESTAMP, -- No timezone awareness
scheduled_for TIMESTAMPTZ, -- Best for most uses
duration INTERVAL -- How long it lasts
);
INSERT INTO events (event_name, event_date, start_time, created_at, scheduled_for, duration)
VALUES (
'Product Launch',
'2024-06-15',
'14:30:00',
'2024-01-10 09:00:00',
'2024-06-15 14:30:00-05',
'2 hours 30 minutes'
);
Getting Current Date and Time
PostgreSQL offers several functions for retrieving current date and time values, each with subtle but important differences. CURRENT_DATE returns today’s date, CURRENT_TIME returns the current time with timezone, and CURRENT_TIMESTAMP returns both as a TIMESTAMPTZ value.
NOW() and CURRENT_TIMESTAMP appear identical but have a critical distinction in transaction context—both return the transaction start time, not the actual current moment. For the actual real-time value within a transaction, use CLOCK_TIMESTAMP().
-- All of these are evaluated once at transaction start
SELECT
CURRENT_DATE as today,
CURRENT_TIME as time_now,
CURRENT_TIMESTAMP as timestamp_now,
NOW() as now_func;
-- Demonstrate transaction-time vs real-time
BEGIN;
SELECT NOW() as transaction_start;
SELECT pg_sleep(2); -- Wait 2 seconds
SELECT NOW() as still_same_time;
SELECT CLOCK_TIMESTAMP() as actual_current_time;
COMMIT;
-- Practical usage: default timestamp
CREATE TABLE user_actions (
action_id SERIAL PRIMARY KEY,
user_id INTEGER,
action_type VARCHAR(50),
created_at TIMESTAMPTZ DEFAULT NOW()
);
Date Arithmetic and Intervals
PostgreSQL’s INTERVAL type makes date arithmetic intuitive and handles edge cases automatically. You can add or subtract intervals using standard operators, and PostgreSQL correctly handles month boundaries, leap years, and daylight saving transitions.
The AGE() function calculates the interval between two dates, returning a human-readable duration. EXTRACT() and DATE_PART() pull specific components from dates—year, month, day, hour, or even day of week.
-- Basic interval arithmetic
SELECT
CURRENT_DATE as today,
CURRENT_DATE + INTERVAL '30 days' as thirty_days_later,
CURRENT_DATE - INTERVAL '1 year' as one_year_ago,
CURRENT_DATE + INTERVAL '3 months 5 days' as complex_interval;
-- Calculate age from birthdate
SELECT
AGE('1990-05-15'::DATE) as age_from_birth,
AGE(CURRENT_DATE, '1990-05-15'::DATE) as explicit_age,
EXTRACT(YEAR FROM AGE('1990-05-15'::DATE)) as years_old;
-- Find records from the last 7 days
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER,
order_date TIMESTAMPTZ,
total_amount DECIMAL(10,2)
);
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
AND order_date < CURRENT_DATE + INTERVAL '1 day';
-- Extract specific date components
SELECT
order_date,
EXTRACT(YEAR FROM order_date) as year,
EXTRACT(MONTH FROM order_date) as month,
EXTRACT(DOW FROM order_date) as day_of_week, -- 0=Sunday
DATE_PART('quarter', order_date) as quarter
FROM orders;
Formatting and Parsing Dates
Converting between dates and strings requires TO_CHAR() for formatting output and TO_DATE() or TO_TIMESTAMP() for parsing input. PostgreSQL uses pattern strings where YYYY represents four-digit years, MM for months, DD for days, HH24 for 24-hour time, and many other options.
-- Format dates for display
SELECT
TO_CHAR(NOW(), 'YYYY-MM-DD') as iso_date,
TO_CHAR(NOW(), 'Mon DD, YYYY') as readable_date,
TO_CHAR(NOW(), 'Day, Month DDth, YYYY at HH24:MI') as full_format,
TO_CHAR(NOW(), 'FMDay, FMMonth DDth, YYYY') as no_padding; -- FM removes padding
-- Parse strings into dates
SELECT
TO_DATE('2024-06-15', 'YYYY-MM-DD') as parsed_date,
TO_TIMESTAMP('15/06/2024 14:30', 'DD/MM/YYYY HH24:MI') as parsed_timestamp,
TO_TIMESTAMP('Jun 15 2024 2:30PM', 'Mon DD YYYY HH:MIPM') as text_timestamp;
-- Practical example: format invoice dates
SELECT
order_id,
TO_CHAR(order_date, 'Month DD, YYYY') as formatted_date,
TO_CHAR(total_amount, '$999,999.99') as formatted_amount
FROM orders
WHERE order_date >= '2024-01-01';
Date Truncation and Rounding
DATE_TRUNC() rounds timestamps down to a specified precision—second, minute, hour, day, week, month, quarter, or year. This function is invaluable for grouping and aggregating time-series data.
-- Truncate to different units
SELECT
DATE_TRUNC('hour', NOW()) as current_hour,
DATE_TRUNC('day', NOW()) as today_midnight,
DATE_TRUNC('week', NOW()) as week_start, -- Monday
DATE_TRUNC('month', NOW()) as month_start,
DATE_TRUNC('quarter', NOW()) as quarter_start;
-- Aggregate sales by month
SELECT
DATE_TRUNC('month', order_date) as month,
COUNT(*) as order_count,
SUM(total_amount) as monthly_revenue
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
-- Weekly sales report
SELECT
DATE_TRUNC('week', order_date) as week_starting,
TO_CHAR(DATE_TRUNC('week', order_date), 'YYYY-MM-DD') as week_label,
COUNT(*) as orders,
AVG(total_amount) as avg_order_value
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '12 weeks'
GROUP BY DATE_TRUNC('week', order_date)
ORDER BY week_starting DESC;
Practical Date Queries
Real-world applications require complex date logic—finding overlapping ranges, excluding weekends, and handling timezones. These patterns appear repeatedly in scheduling systems, booking platforms, and reporting tools.
-- Find all bookings in the next 30 days
CREATE TABLE bookings (
booking_id SERIAL PRIMARY KEY,
resource_id INTEGER,
start_time TIMESTAMPTZ,
end_time TIMESTAMPTZ,
customer_name VARCHAR(200)
);
SELECT * FROM bookings
WHERE start_time >= NOW()
AND start_time < NOW() + INTERVAL '30 days'
ORDER BY start_time;
-- Exclude weekends (0=Sunday, 6=Saturday)
SELECT * FROM bookings
WHERE start_time >= NOW()
AND start_time < NOW() + INTERVAL '30 days'
AND EXTRACT(DOW FROM start_time) NOT IN (0, 6)
ORDER BY start_time;
-- Find overlapping date ranges
SELECT b1.booking_id, b2.booking_id
FROM bookings b1
JOIN bookings b2 ON b1.resource_id = b2.resource_id
AND b1.booking_id < b2.booking_id -- Avoid duplicate pairs
WHERE b1.start_time < b2.end_time
AND b1.end_time > b2.start_time;
-- Timezone conversions
SELECT
start_time as utc_time,
start_time AT TIME ZONE 'America/New_York' as ny_time,
start_time AT TIME ZONE 'Europe/London' as london_time,
start_time AT TIME ZONE 'Asia/Tokyo' as tokyo_time
FROM bookings
WHERE booking_id = 1;
-- Calculate business days between dates
CREATE OR REPLACE FUNCTION business_days(start_date DATE, end_date DATE)
RETURNS INTEGER AS $$
DECLARE
total_days INTEGER;
weekend_days INTEGER;
BEGIN
total_days := end_date - start_date;
weekend_days := (
SELECT COUNT(*)
FROM generate_series(start_date, end_date, '1 day'::INTERVAL) d
WHERE EXTRACT(DOW FROM d) IN (0, 6)
);
RETURN total_days - weekend_days;
END;
$$ LANGUAGE plpgsql;
SELECT business_days('2024-01-01', '2024-01-31');
Performance Tips and Best Practices
Date column performance depends heavily on proper indexing and query structure. Creating indexes on frequently queried date columns is obvious, but the real performance gains come from writing queries that can actually use those indexes.
Applying functions to date columns in WHERE clauses prevents index usage. Instead of WHERE DATE(created_at) = '2024-01-15', use a range comparison that allows index scans.
-- Create appropriate indexes
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_bookings_start ON bookings(start_time);
CREATE INDEX idx_bookings_range ON bookings USING GIST (tstzrange(start_time, end_time));
-- BAD: Function on column prevents index usage
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE DATE(order_date) = '2024-01-15';
-- GOOD: Range comparison uses index
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date >= '2024-01-15'::DATE
AND order_date < '2024-01-16'::DATE;
-- BAD: EXTRACT prevents index usage
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2024
AND EXTRACT(MONTH FROM order_date) = 1;
-- GOOD: Date range uses index
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2024-02-01';
-- Use BETWEEN carefully (it's inclusive on both ends)
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31 23:59:59.999999';
-- Better: Use half-open intervals
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2024-02-01';
Store dates in UTC using TIMESTAMPTZ and convert to local timezones only for display. This eliminates ambiguity during daylight saving transitions and simplifies multi-timezone applications. Always use parameterized queries when accepting date input from users to prevent SQL injection and ensure proper type conversion.
Master these date functions and patterns, and you’ll handle everything from simple date filtering to complex scheduling logic with confidence. PostgreSQL’s date handling is powerful—use it correctly and your queries will be both fast and maintainable.