> select ('2021-01-01'::timestamptz - '2020-12-31'::timestamptz) as diff;
1 daySo this is correct for years, right?
> select floor(extract(epoch from date_trunc('year', '2021-01-01'::timestamptz) - date_trunc('year', '2020-12-31'::timestamptz)) / 3600 / 24 / 365) as diff;
1I don't think there's a good, mathematical expression for determining something as arbitrary as dates. If there was, I don't think MS would implement a special function like DateDiff for it.
> # SELECT date_part('year', '2021-01-01'::timestamptz) - date_part('year', '2020-12-31'::timestamptz) as years;
Or what am I missing?
[edit: formatting]
If you e.g. want to check if two timestamps are more than a year apart you just compare the difference to an interval of 1 year.
It gets even strange for e.g. select DATEDIFF(year, '2021-12-01', '2022-01-01') which still returns 1 even though that's a whole month. I don't see a use case for this kind of result