PostgreSQL Date Functions & Formatting: Complete Guide
Working with dates requires specific functions - extracting year/month/day, calculating differences, formatting for display, converting between timezones. This guide covers the most practical date functions and shows real-world patterns.
What You’ll Learn
This guide covers:
- EXTRACT for getting date parts (year, month, day)
- DATE_TRUNC for rounding dates
- Date arithmetic (adding/subtracting intervals)
- Calculating age and duration
- Date formatting with TO_CHAR
- String to date conversion with TO_DATE
- Timezone handling
- Common date patterns
Getting Date Parts with EXTRACT
Basic EXTRACT
Extract individual components from dates.
SELECT
hire_date,
EXTRACT(YEAR FROM hire_date) as year,
EXTRACT(MONTH FROM hire_date) as month,
EXTRACT(DAY FROM hire_date) as day,
EXTRACT(QUARTER FROM hire_date) as quarter,
EXTRACT(WEEK FROM hire_date) as week_of_year,
EXTRACT(DOW FROM hire_date) as day_of_week,
EXTRACT(DOY FROM hire_date) as day_of_year
FROM employees
LIMIT 3;
Result:
hire_date | year | month | day | quarter | week_of_year | day_of_week | day_of_year
----------+------+-------+-----+---------+--------------+-------------+-------------
2020-01-15| 2020| 1| 15| 1| 3| 3| 15
2019-03-20| 2019| 3| 20| 1| 12| 3| 80
2018-06-11| 2018| 6| 11| 2| 24| 1| 162
EXTRACT returns NUMERIC type, so cast to INTEGER if needed:
SELECT EXTRACT(YEAR FROM hire_date)::INTEGER as hire_year FROM employees LIMIT 1;
Extracting Time Parts
SELECT
NOW() as current_time,
EXTRACT(HOUR FROM NOW()) as hour,
EXTRACT(MINUTE FROM NOW()) as minute,
EXTRACT(SECOND FROM NOW()) as second,
EXTRACT(MILLISECOND FROM NOW()) as millisecond,
EXTRACT(EPOCH FROM NOW()) as seconds_since_epoch
FROM employees
LIMIT 1;
Result:
current_time | hour | minute | second | millisecond | seconds_since_epoch
-----------------------+------+--------+--------+-------------+---------------------
2026-02-21 10:35:45.123| 10 | 35 | 45 | 123 | 1740134145.123
EPOCH gives seconds since January 1, 1970.
DATE_TRUNC: Round Dates
Round dates to a specific precision.
SELECT
NOW() as now,
DATE_TRUNC('year', NOW()) as start_of_year,
DATE_TRUNC('month', NOW()) as start_of_month,
DATE_TRUNC('week', NOW()) as start_of_week,
DATE_TRUNC('day', NOW()) as start_of_day,
DATE_TRUNC('hour', NOW()) as start_of_hour,
DATE_TRUNC('minute', NOW()) as start_of_minute;
Result:
now | start_of_year | start_of_month | start_of_week
-----------------------+---------------------+---------------------+---------------------
2026-02-21 10:35:45.123| 2026-01-01 00:00:00 | 2026-02-01 00:00:00 | 2026-02-16 00:00:00
Use DATE_TRUNC to:
- Find start of year:
DATE_TRUNC('year', date) - Group by month:
DATE_TRUNC('month', date) - Find start of hour:
DATE_TRUNC('hour', timestamp)
Date Arithmetic
Adding/Subtracting Intervals
SELECT
hire_date,
hire_date + INTERVAL '1 year' as one_year_later,
hire_date + INTERVAL '6 months' as six_months_later,
hire_date + INTERVAL '30 days' as thirty_days_later,
hire_date - INTERVAL '1 year' as one_year_earlier
FROM employees
LIMIT 3;
Result:
hire_date | one_year_later | six_months_later | thirty_days_later | one_year_earlier
----------+----------------+-----------------+-------------------+------------------
2020-01-15| 2021-01-15 | 2020-07-15 | 2020-02-14 | 2019-01-15
2019-03-20| 2020-03-20 | 2019-09-20 | 2019-04-19 | 2018-03-20
2018-06-11| 2019-06-11 | 2018-12-11 | 2018-07-11 | 2017-06-11
Interval syntax: INTERVAL 'quantity unit' or INTERVAL 'amount'
Valid units: year, month, week, day, hour, minute, second
Complex Interval Expressions
SELECT
hire_date,
hire_date + INTERVAL '1 year 3 months 15 days' as future_date,
hire_date + '1 year'::INTERVAL as another_year_later
FROM employees
LIMIT 1;
Subtracting Dates
Get the difference between two dates.
SELECT
emp_id,
first_name,
hire_date,
CURRENT_DATE - hire_date as days_employed,
(CURRENT_DATE - hire_date) / 365.25 as years_employed,
EXTRACT(DAY FROM CURRENT_DATE - hire_date) as remaining_days_in_year
FROM employees
LIMIT 3;
Result:
emp_id | first_name | hire_date | days_employed | years_employed | remaining_days_in_year
-------+------------+----------+---------------+----------------+---------------------
1 | James | 2020-01-15| 2167| 5.9| 67
2 | Mary | 2019-03-20| 2528| 6.9| 37
7 | David | 2018-06-11| 2752| 7.5| 152
Calculating Age
AGE Function
Calculate age in years, months, and days.
SELECT
emp_id,
first_name,
hire_date,
AGE(NOW(), hire_date) as employment_duration,
AGE(CURRENT_DATE, hire_date)::TEXT as duration_text
FROM employees
LIMIT 3;
Result:
emp_id | first_name | hire_date | employment_duration | duration_text
-------+------------+----------+---------------------+--------------------
1 | James | 2020-01-15| 6 years 1 mon 6 day | 6 years 1 mon 6 day
2 | Mary | 2019-03-20| 6 years 11 mons 1 d | 6 years 11 mons 1 d
7 | David | 2018-06-11| 7 years 8 mons 10 d | 7 years 8 mons 10 d
AGE returns an INTERVAL type, human-readable format.
Years of Service Calculation
CREATE FUNCTION get_tenure_band(emp_id INTEGER)
RETURNS VARCHAR AS $$
DECLARE
v_years DECIMAL;
BEGIN
SELECT EXTRACT(YEAR FROM AGE(NOW(), hire_date))::DECIMAL INTO v_years
FROM employees
WHERE emp_id = get_tenure_band.emp_id;
CASE
WHEN v_years < 1 THEN RETURN 'New (< 1 year)';
WHEN v_years < 3 THEN RETURN 'Junior (1-3 years)';
WHEN v_years < 5 THEN RETURN 'Mid-level (3-5 years)';
WHEN v_years < 10 THEN RETURN 'Senior (5-10 years)';
ELSE RETURN 'Veteran (10+ years)';
END CASE;
END;
$$ LANGUAGE plpgsql;
SELECT emp_id, first_name, get_tenure_band(emp_id) FROM employees LIMIT 5;
Date Formatting with TO_CHAR
Common Formats
SELECT
CURRENT_DATE as raw_date,
TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD') as iso_format,
TO_CHAR(CURRENT_DATE, 'Month DD, YYYY') as long_format,
TO_CHAR(CURRENT_DATE, 'MM/DD/YY') as us_format,
TO_CHAR(CURRENT_DATE, 'DD.MM.YYYY') as european_format,
TO_CHAR(CURRENT_DATE, 'Dy, Mon DD') as short_format;
Result:
raw_date | iso_format | long_format | us_format | european_format | short_format
----------+------------+--------------------+----------+-----------------+---------------
2026-02-21| 2026-02-21 | February 21, 2026 | 02/21/26 | 21.02.2026 | Sat, Feb 21
Format codes:
YYYY- 4-digit yearMM- 2-digit monthDD- 2-digit dayMonth- Full month nameMon- Abbreviated month nameDy- Day nameHH24- 24-hour formatHH- 12-hour formatMI- MinutesSS- Seconds
Timestamp Formatting
SELECT
NOW() as raw_timestamp,
TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS') as datetime_iso,
TO_CHAR(NOW(), 'Month DD, YYYY at HH12:MI AM') as readable,
TO_CHAR(NOW(), 'Day, Month DD, YYYY HH24:MI:SS') as full_format;
Result:
raw_timestamp | datetime_iso | readable | full_format
-----------------------+------------------------+-----------------------------------+------------------------------
2026-02-21 10:35:45.123| 2026-02-21 10:35:45 | February 21, 2026 at 10:35 AM | Saturday, February 21, 2026 10:35:45
String to Date Conversion
TO_DATE
Convert strings to DATE type.
SELECT
'2026-02-21'::DATE as simple_cast,
TO_DATE('02/21/2026', 'MM/DD/YYYY') as us_format_conversion,
TO_DATE('21-Feb-2026', 'DD-Mon-YYYY') as abbreviated_month,
TO_DATE('2026-02-21', 'YYYY-MM-DD') as iso_format;
Result:
simple_cast | us_format_conversion | abbreviated_month | iso_format
----------+---------------------+-------------------+-----------
2026-02-21 | 2026-02-21 | 2026-02-21 | 2026-02-21
TO_TIMESTAMP
Convert strings to TIMESTAMP.
SELECT
TO_TIMESTAMP('2026-02-21 10:35:45', 'YYYY-MM-DD HH24:MI:SS') as timestamp_iso,
TO_TIMESTAMP('02/21/2026 10:35 AM', 'MM/DD/YYYY HH12:MI AM') as us_format_timestamp;
Result:
timestamp_iso | us_format_timestamp
-----------------------+---------------------
2026-02-21 10:35:45+00 | 2026-02-21 10:35:00+00
Real-World Date Patterns
Find Employees Hired in Last Year
SELECT
emp_id,
first_name,
hire_date,
CURRENT_DATE - hire_date as days_employed
FROM employees
WHERE hire_date >= CURRENT_DATE - INTERVAL '1 year'
ORDER BY hire_date DESC;
Quarterly Reports
SELECT
DATE_TRUNC('quarter', hire_date)::DATE as quarter_start,
EXTRACT(QUARTER FROM hire_date)::INTEGER as quarter,
COUNT(*) as employees_hired
FROM employees
GROUP BY DATE_TRUNC('quarter', hire_date)
ORDER BY quarter_start DESC;
Birthdays This Month
SELECT
emp_id,
first_name,
birth_date,
AGE(CURRENT_DATE, birth_date) as age,
DATE_TRUNC('year', CURRENT_DATE) +
(birth_date - DATE_TRUNC('year', birth_date))::INTERVAL as birthday_this_year
FROM employees
WHERE EXTRACT(MONTH FROM birth_date) = EXTRACT(MONTH FROM CURRENT_DATE)
ORDER BY EXTRACT(DAY FROM birth_date);
Last Day of Month
SELECT
DATE_TRUNC('month', CURRENT_DATE)::DATE + INTERVAL '1 month' - INTERVAL '1 day' as last_day_of_month;
Result:
last_day_of_month
------------------
2026-02-28
Work Days Calculation (Excluding Weekends)
CREATE FUNCTION count_work_days(start_date DATE, end_date DATE)
RETURNS INTEGER AS $$
DECLARE
v_count INTEGER := 0;
v_current_date DATE := start_date;
BEGIN
WHILE v_current_date <= end_date LOOP
-- EXTRACT(DOW FROM date) returns 0 (Sunday) to 6 (Saturday)
IF EXTRACT(DOW FROM v_current_date) BETWEEN 1 AND 5 THEN
v_count := v_count + 1;
END IF;
v_current_date := v_current_date + INTERVAL '1 day';
END LOOP;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;
SELECT count_work_days('2026-02-16', '2026-02-20') as work_days; -- Monday-Friday
Result:
work_days
-----------
5
Timezone Handling
AT TIME ZONE
Convert between timezones.
SELECT
NOW() as server_time,
NOW() AT TIME ZONE 'US/Eastern' as eastern_time,
NOW() AT TIME ZONE 'Europe/London' as london_time,
NOW() AT TIME ZONE 'Asia/Tokyo' as tokyo_time;
Result:
server_time | eastern_time | london_time | tokyo_time
-----------------------+------------------------+------------------------+-----------------------
2026-02-21 15:35:45+00 | 2026-02-21 10:35:45 | 2026-02-21 15:35:45 | 2026-02-22 00:35:45
Store Dates in UTC, Display in User Timezone
CREATE TABLE user_events (
event_id INTEGER,
user_id INTEGER,
event_time TIMESTAMP WITH TIME ZONE, -- Always store in UTC
user_timezone VARCHAR(50)
);
INSERT INTO user_events VALUES
(1, 1, '2026-02-21 15:35:45+00'::TIMESTAMP WITH TIME ZONE, 'US/Eastern'),
(2, 2, '2026-02-21 15:35:45+00'::TIMESTAMP WITH TIME ZONE, 'Asia/Tokyo');
-- Display in user's timezone
SELECT
event_id,
event_time AT TIME ZONE 'UTC' as utc_time,
event_time AT TIME ZONE user_timezone as user_local_time
FROM user_events;
Result:
event_id | utc_time | user_local_time
---------+---------------------+---------------------
1 | 2026-02-21 15:35:45 | 2026-02-21 10:35:45
2 | 2026-02-21 15:35:45 | 2026-02-22 00:35:45
Common Errors & Solutions
Error: “invalid input syntax for type date”
Problem:
SELECT '2026/02/21'::DATE; -- Wrong format
Solution:
Use correct format or TO_DATE:
SELECT '2026-02-21'::DATE; -- ISO format works
SELECT TO_DATE('2026/02/21', 'YYYY/MM/DD'); -- Specify format
Error: “EXTRACT day of week from date cannot contain time zone”
Problem:
SELECT EXTRACT(DOW FROM NOW() AT TIME ZONE 'US/Eastern');
Solution:
Cast to DATE first:
SELECT EXTRACT(DOW FROM (NOW() AT TIME ZONE 'US/Eastern')::DATE);
Unexpected Results from Age Calculation
Problem:
SELECT AGE(NOW(), hire_date) FROM employees; -- Gives INTERVAL, hard to use in calculations
Solution:
Extract the year component:
SELECT EXTRACT(YEAR FROM AGE(NOW(), hire_date))::INTEGER FROM employees;
Performance Tips
1. Use DATE Not TIMESTAMP When Possible
-- Faster: 4 bytes
CREATE TABLE events (event_id INTEGER, event_date DATE);
-- Slower: 8 bytes
CREATE TABLE events (event_id INTEGER, event_timestamp TIMESTAMP);
2. Index Date Columns Used in Queries
CREATE INDEX idx_hire_date ON employees(hire_date);
-- Now this query uses the index
SELECT * FROM employees WHERE hire_date > '2020-01-01';
3. DATE_TRUNC for Range Queries
-- Uses index efficiently
SELECT * FROM events
WHERE event_date >= DATE_TRUNC('month', CURRENT_DATE)::DATE
AND event_date < DATE_TRUNC('month', CURRENT_DATE)::DATE + INTERVAL '1 month';
4. Avoid Functions on Column in WHERE
-- Slow: Can't use index
SELECT * FROM employees WHERE EXTRACT(YEAR FROM hire_date) = 2020;
-- Better: Use date range
SELECT * FROM employees
WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';
FAQ
Q: What’s the difference between DATE and TIMESTAMP? A: DATE is just the date (4 bytes). TIMESTAMP includes time (8 bytes). Use DATE when time doesn’t matter.
Q: How do I get the last day of a month?
A: DATE_TRUNC('month', date)::DATE + INTERVAL '1 month' - INTERVAL '1 day'
Q: How do I calculate business days (excluding weekends)?
A: Check EXTRACT(DOW FROM date) - 1 to 5 are Mon-Fri, 0 and 6 are weekend.
Q: Should I store dates as TEXT or DATE type? A: Always DATE type. Enables date arithmetic, indexing, and validation.
Q: How do I handle timezones correctly? A: Store all times in UTC (TIMESTAMP WITH TIME ZONE), display in user’s timezone with AT TIME ZONE.
Q: What’s the difference between NOW() and CURRENT_DATE? A: NOW() returns TIMESTAMP. CURRENT_DATE returns DATE (today only, no time).
Related Topics
Continue mastering data types:
- PostgreSQL Data Types Reference - Complete type guide
- JSON Functions & Operators - Working with JSON data
- UUID vs Serial for Primary Keys - Choosing identifiers
Date functions are essential for business applications. Master these patterns and you can handle any temporal requirement.