undefined | Better HN

0 pointstomrod4y ago0 comments

That doesn't make sense to me. Why wouldn't datediff be 0 in this case, as it is less than 365 days? Else one should simply:

    extract(year from d1) - extract(year from d2)

and call it a day?

0 comments

3 comments · 2 top-level

btilly4y ago· 1 in thread

Try it again, except looking for months. You'll get -11 instead of 1.

castorp4y ago

    select extract(month from justify_interval(timestamp '2022-01-01' - timestamp '2021-11-01'))

returns 2

saltcured4y ago

I'm pretty sure this will provide the desired semantics, though I am away from a usable postgres and not bothering to test it.

CREATE FUNCTION date_diff(granularity text, t1 timestamptz, t2 timestamptz) RETURNS int8 AS $$ SELECT date_part(granularity, date_trunc(granularity, t2) - date_trunc(granularity, t1))::int8 $$ LANGUAGE SQL;

j / k navigate · click thread line to collapse