First day and Last day of previous year

Question

I'm trying to find the date of the end of previous year in Postgres.

This will give me the first day of previous year

SELECT date_trunc('year', now()- interval '1 year')

returns the correct result #=> 2018-01-01 00:00:00

But trying to remove 1day from the first day of the current year doesn't give me the last day of previous year:

SELECT date_trunc('year', now() - interval '1 day')

returns #=> 2018-01-01 00:00:00

When I'm expecting it to be 2018-12-31

Still gives me the first day of the current year. Even if i remove 100 day it still returns the same result.

Same behavior for previous years:

SELECT date_trunc('year', now()- interval '2 year');

returns #=> 2017-01-01 00:00:00 which is what I expect.

but :

SELECT date_trunc('year', now()- interval '1 year' - interval '1 day');

returns #=> 2018-01-01 00:00:00 when I'm expecting 2017-12-31

-- For future reference I'm posting this in 2019.

Answer 1

This is an order of operations issue. Truncate the date before subtracting:

SELECT date_trunc('year', now()) - interval '1 day';
SELECT date_trunc('year', now()- interval '1 year') - interval '1 day';

Disclosure: I work for EnterpriseDB (EDB)