PostgreSQL Date based Query Examples

Today
select current_date; --2024-01-17
Yesterday
select current_date - 1; --2024-01-16
Tomorrow
select current_date + 1; --2024-01-18

Format Date
select to_char(current_date,'DD/MM/YYYY'); --17/01/2024

This year
select extract(year from current_date); --2024
Next year
select extract(year from current_date + interval '1 year'); --2025
Last year
select extract(year from current_date - interval '1 year'); --2023

First day of current month
select cast(date_trunc('month', current_date) as date); --2024-01-01
Last day of current month
select cast(date_trunc('month',current_date + interval '1 month') as date) - 1; --2024-01-31

First day of last month
select cast(date_trunc('month', current_date - interval '1 month') as date); --2023-12-01
Last day of last month
select cast(date_trunc('month', current_date) as date) - 1; --2023-12-31

First day of next month
select cast(date_trunc('month', current_date + interval '1 month') as date); --2024-02-01
Last day of next month
select cast(date_trunc('month', current_date + interval '2 month') as date) - 1; --2024-02-29

Last day of this year
select cast(date_trunc('year', current_date) as date) + interval '1 year' - interval '1 day'; --2024-12-31 00:00:00.000
Last day of last year
select cast(date_trunc('year', current_date) as date) - interval '1 day'; --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'; --2025-12-31 00:00:00.000