PostgreSQL Date-Based Query Examples: Today, Month, and Year

Working with dates is essential in PostgreSQL for reporting, analytics, and application logic. This guide provides practical examples of common date-based queries in PostgreSQL, from today’s date to month and year calculations.

1. Day-Based Queries

PostgreSQL makes it easy to retrieve today’s, yesterday’s, or tomorrow’s date:

-- Today
SELECT current_date; 
-- Output: 2024-01-17

-- Yesterday
SELECT current_date - 1; 
-- Output: 2024-01-16

-- Tomorrow
SELECT current_date + 1; 
-- Output: 2024-01-18

2. Formatting Dates

You can format dates using the to_char function:

SELECT to_char(current_date, 'DD/MM/YYYY'); 
-- Output: 17/01/2024

3. Year-Based Queries

Use extract and interval for current, previous, or next year calculations:

-- Current year
SELECT extract(year FROM current_date); 
-- Output: 2024

-- Next year
SELECT extract(year FROM current_date + interval '1 year'); 
-- Output: 2025

-- Last year
SELECT extract(year FROM current_date - interval '1 year'); 
-- Output: 2023

4. First and Last Day of the Month

Finding the first or last day of a month is common in monthly reporting:

-- First day of the current month
SELECT CAST(date_trunc('month', current_date) AS date); 
-- Output: 2024-01-01

-- Last day of the current month
SELECT CAST(date_trunc('month', current_date + interval '1 month') AS date) - 1; 
-- Output: 2024-01-31

Previous and Next Month
-- First day of last month
SELECT CAST(date_trunc('month', current_date - interval '1 month') AS date); 
-- Output: 2023-12-01

-- Last day of last month
SELECT CAST(date_trunc('month', current_date) AS date) - 1; 
-- Output: 2023-12-31

-- First day of next month
SELECT CAST(date_trunc('month', current_date + interval '1 month') AS date); 
-- Output: 2024-02-01

-- Last day of next month
SELECT CAST(date_trunc('month', current_date + interval '2 month') AS date) - 1; 
-- Output: 2024-02-29

5. Last Day of the Year

Year-based queries are helpful for annual reports or analytics:

-- Last day of this year
SELECT CAST(date_trunc('year', current_date) AS date) + interval '1 year' - interval '1 day'; 
-- Output: 2024-12-31 00:00:00.000

-- Last day of last year
SELECT CAST(date_trunc('year', current_date) AS date) - interval '1 day'; 
-- Output: 2023-12-31 00:00:00.000

-- Last day of next year
SELECT CAST(date_trunc('year', current_date) AS date) + interval '2 year' - interval '1 day'; 
-- Output: 2025-12-31 00:00:00.000

🚀 Final Notes

• current_date returns only the date, without the time. Use current_timestamp for both date and time.
• date_trunc is very useful for truncating dates to the start of the month or year.
• interval allows addition or subtraction of time periods, enabling dynamic date calculations.

These queries provide a solid foundation for working with dates in PostgreSQL for reporting, analytics, and application logic.